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