Idea about implementing a time internal type in SQL Serve 2008


    In SQL Server 2008, we implement several new date and time types which follow the ISO SQL standard.  The one ISO SQL type which is missing in SQL Server 2008 is the time internal type. Today, I will present some idea about implementing a time internal types in SQL Server 2008. 


   A time interval is the intervening time between two time points. The amount of intervening time is expressed by a duration. Time Interval is a very useful type, such as  tracking the age of a person, etc. In SQL Server, the smaller unit (we call it as a tick) of time is 100ns, which is aligned with CLR DateTime class and Windows FILETIME structure. The range of time point is from 0001-01-01T00:00:00 to 9999-12-31T23:59.59.59.9999999, which can be represented by using a BIGINT type in SQL Server and indicate the number of ticks since 0001-01-01T00:00:00 to the date and time we want to represented. As a result,  I can convert two time points into ticks, and the different between the two ticks (which is represented as a BIGINT) will serve as my Time Interval types.  I will use following code to illustrate how I will implement the types;




DROP FUNCTION dbo.Get_TimeInterval,dbo.From_Ticks,dbo.Get_Ticks,dbo.Add_TimeInterval


drop type BigInt_TimeInterval


go


 


CREATE TYPE BigInt_TimeInterval FROM BIGINT;


go


-- get the ticks (100ns) from a datetimeoffset instance


create function Get_Ticks (@value datetimeoffset(7))


RETURNS BigInt_TimeInterval  WITH RETURNS NULL ON NULL INPUT


as


begin


 --convert to UTC value first


 declare @tempvalue datetime2(7) = convert(datetime2(7),@value,1)


 declare @temptime  time(7) = convert(time(7),@tempvalue)


 declare @ticks_day  BigInt = (cast(DateDiff(day,'0001-01-01', @tempvalue) as bigint) ) * 24 * 60 * 60 *10000000


 declare @ticks_time BigInt = DatePart(nanosecond , @temptime) /100 + cast(DateDiff(second,'00:00:00', @temptime) as bigint)* 10000000


 return @ticks_day + @ticks_time


end


go


 


 


--given a ticks, return the correspond datetimeoffset value


create function From_Ticks (@interval BigInt_TimeInterval)


RETURNS datetimeoffset(7)  WITH RETURNS NULL ON NULL INPUT


as


begin


 declare @start datetimeoffset(7)='0001-01-01';


 declare @day bigint = @interval /10000000 /60/60/24;


 declare @ticksperday bigint= cast(10000000 as bigint)*60*60 *24;


 declare @second bigint = @interval % @ticksperday /10000000;


 declare @nanosecond bigint = @interval % 10000000 * 100;


 return  dateadd(nanosecond,@nanosecond,dateadd(second,@second,dateadd(day, @day,@start)))


end


go


 


--given two datetimeoffset values, return the difference in term of ticks between them


create function Get_TimeInterval (@start datetimeoffset(7), @end datetimeoffset(7))


RETURNS BigInt_TimeInterval  WITH RETURNS NULL ON NULL INPUT


as


begin


 


 declare @ticks_start BigInt = dbo.Get_Ticks(@start)


 declare @ticks_end BigInt = dbo.Get_Ticks(@end)


 return @ticks_end - @ticks_start


end


go


 


 


create function Add_TimeInterval (@start datetimeoffset(7), @interval BigInt_TimeInterval)


RETURNS datetimeoffset(7)  WITH RETURNS NULL ON NULL INPUT


as


begin


 


 declare @ticks_start BigInt = dbo.Get_Ticks(@start)


 declare @ticks_end BigInt = @ticks_start + @interval


 return dbo.From_Ticks(@ticks_end)


end


go


 


-- testing round-tripping scripts for from_ticks and get_ticks


select convert(nvarchar(200),dbo.From_Ticks(dbo.Get_Ticks('1437-08-02T22:56:35.7316992')),126),'1437-08-02T22:56:35.7316992'


select convert(nvarchar(200),dbo.From_Ticks(dbo.Get_Ticks('1748-08-29T07:13:06.0253248')),126),'1748-08-29T07:13:06.0253248'


select convert(nvarchar(200),dbo.From_Ticks(dbo.Get_Ticks('6452-07-05T09:50:10.3666944')),126),'6452-07-05T09:50:10.3666944'


 


-- testing timeinterval add and diff round-tripping


declare @base datetime2= SysDateTime();


declare @a datetime2(7) ='1452-02-22T09:50:28.8184192'; select @a,dbo.Add_TimeInterval(@base,dbo.Get_TimeInterval(@base,@a))


set @a ='6361-02-17T06:45:13.8613760'; select @a,dbo.Add_TimeInterval(@base,dbo.Get_TimeInterval(@base,@a))


set @a= '9608-01-08T22:31:37.4123520'; select @a,dbo.Add_TimeInterval(@base,dbo.Get_TimeInterval(@base,@a))


go


-- return type need be special cas


declare @a time ='01:00:00', @b time='12:00:00'


select @a as a, @b as b, dbo.Get_Ticks(@a), dbo.Get_Ticks(@b), dbo.Get_TimeInterval(@a,@b),


         cast(dbo.Add_TimeInterval(@a,dbo.Get_TimeInterval(@a,@b)) as time)


go


Note,  I created a alias type BigInt_TimeInternal since the TimeInterval keyword might be used in future SQL Server release, so I use a BigInt_TimeInterval to say it is a Time Interval represented in bigint type.  The input type is datetimeoffset, thus all other types, such as date, time, will be implicited converted into datetimeoffset.  It is trival to add a Ticks_ToString() method for displaying purpose. Get_TimeInternal(@a,@b) is not equal to datediff(nanoseconds, @a,@b)/100 since the later one returns int type and will overflow in many cases.


This is my first post on date and time related topic.  I plan to following topics in the future:



  • TimeZone and Daylight saving is a non-trival issue

  • Using ADO .Net with SQL Server 2008's date and time types

  • Colon(:) and dot (.) in factional second is not the same

  • Precision and Resolution is not the same

  • Date and Time ariticles/links

Comments (0)

Skip to main content