SQL Server 2008 TIME data type

The new TIME type stores a time with a specified scale that defines the fractional second precision.

The scale ranges from 0-7 representing 0-7 significant digits for the fractional seconds. The default precision is TIME(7), giving 7 significant digits, a range of .0000000 to .9999999.

TIME is stored as an integer of various sizes, depending on the scale. For a scale of 0-2 it is stored as a 3 byte integer, 3-4 a 4 byte integer, and for scale 5-7 it is stored as a 5 byte integer.

The scale is then used to calculate the time since midnight, with an accuracy ranging from 1 second to 100 nanoseconds.

If t is the value stored in the time column and n is the scale the time from midnight in seconds can be calculated by t / 10n.

Here’s a summary of the storage and scaling (seconds, milliseconds, and nanoseconds are the respective duration t is multiplied by):

Scale Storage (bytes) Seconds Milliseconds Nanoseconds
TIME(0) 3 1 1000 1000000000
TIME(1) 3 0.1 100 100000000
TIME(2) 3 0.01 10 10000000
TIME(3)  4 0.001 1 1000000
TIME(4)  4 0.0001 0.1 100000
TIME(5) 5 0.00001 0.01 10000
TIME(6) 5 0.000001 0.001 1000
TIME(7) 5 0.0000001 0.0001 100

It’s possible to extract the unscaled value from a TIME value, although it requires a few steps.

DECLARE @Time TIME(7) = '00:01:00' -- Format HH:mm:SS[.nnnnnnn]

DECLARE @BinaryTime VARBINARY(8)

 

SET @BinaryTime = SUBSTRING(CONVERT(VARBINARY, REVERSE(CONVERT(VARBINARY, @Time))),

                            1,

                            DATALENGTH(@Time))

                                               

SELECT CONVERT(BIGINT, @BinaryTime) -- Unscaled TIME value

-- Result: 600000000

The above example gives a result of 600000000, which, looking at the scale makes sense. The scale is 7, so a time of 1 minute past midnight is 60 seconds = 600000000 / 107.

DECLARE @Time TIME(3) = '00:01:00' -- Format HH:mm:SS[.nnnnnnn]

DECLARE @BinaryTime VARBINARY(8)

 

SET @BinaryTime = SUBSTRING(CONVERT(VARBINARY, REVERSE(CONVERT(VARBINARY, @Time))),

                            1,

                            DATALENGTH(@Time))

                                               

SELECT CONVERT(BIGINT, @BinaryTime) -- Unscaled TIME value

 

-- Result: 60000


A scale of 3 gives a result of 6000 as 60 seconds = 6000 / 103

Books Online has more information about the new DATE type here.

Published 10 January 2008 07:34 PM by Danny

Comments

# Александр Волок said on 12 January, 2008 10:04 AM

Новый тип TIME содержит в себе значение времени, но уже с определенной пользователем точностью, которая

# Russian SQL Server Club said on 31 January, 2009 10:37 AM

Новый тип TIME содержит в себе значение времени, но уже с определенной пользователем точностью, которая...