Time for a Date?

Artikkeli
05/27/2008

As a DBA I wear many hats! If I had to choose a hat for my data architect role it would probably be a nice white panama, the sort you see in the Agatha Christie re-runs on ITV3, but I digress!.

As a data architect you extol the virtues of “correctly data typing” in your design, such as not using varchar when char would be more appropriate, declaring a smallint or tinyint where a column can only be an integer less than or equal to 32,767 or 255 (respectively), so when it comes to dates and times SQL Server has, until till now, forced you to compromise.

If you use the 8 byte datetime data type for only storing a date, the time portion will default to midnight, and if you only store a time the date portion will default to 1901-01-01 e.g.

Running the query:

SELECT CAST('20080101' AS datetime) as DateOnly

,CAST('14:00:00.000' AS datetime) as TimeOnly

Returns:

DateOnly	TimeOnly
2008-01-01 00:00:00.000	1900-01-01 14:00:00.000

The datetime data type can hold date values from January 1, 1753, through December 31, 9999 and time values from 00:00:00 through 23:59:59.997 rounded to increments of .000, .003, or .007 seconds, which means that if you want better accuracy you will have to resort to holding your dates as character data, or as an offset, or possibly in SQL 2005 as a .NET data type, which in themselves can cause issues and added complexity. The smalldatetime data type uses 4 bytes to store dates at a lower precision than datetime. This gives it a date range of January 1, 1900, through June 6, 2079 and a time accuracy of 1 minute.

So using either the datetime or smalldatetime data types for storing only a date or a time, are using excessive storage and may not fulfil your requirements. Fortunately in SQL Server 2008 several new data types have been added which will help to resolve these issues. These data types are time, date, datetime2 and datetimeoffset and I will look at each of these separately

Time

The time data type has a range between 00:00:00.0000000 to 23:59:59.9999999 and will use 3 to 5 bytes depending on the precision. This makes the time accurate to 100 nanoseconds.

Specified scale	Result (precision, scale)	Column length (bytes)	Fractional seconds precision
time	(16,7)	5	7
time(0)	(8,0)	3	0-2
time(1)	(10,1)	3	0-2
time(2)	(11,2)	3	0-2
time(3)	(12,3)	4	3-4
time(4)	(13,4)	4	3-4
time(5)	(14,5)	5	5-7
time(6)	(15,6)	5	5-7
time(7)	(16,7)	5	5-7

So the queries:

SELECT CAST('14:13:12.1234567' AS time) AS [time]

,CAST('14:13:12.1234567' AS time(0) ) AS [time(0)]

,CAST('14:13:12.1234567' AS time(1) ) AS [time(1)]

,CAST('14:13:12.1234567' AS time(2) ) AS [time(2)]

,CAST('14:13:12.1234567' AS time(3) ) AS [time(3)]

, CAST('14:13:12.1234567' AS time(4) ) AS [time(4)]

,CAST('14:13:12.1234567' AS time(5) ) AS [time(5)]

,CAST('14:13:12.1234567' AS time(6) ) AS [time(6)]

,CAST('14:13:12.1234567' AS time(7) ) AS [time(7)]

Returns:

time	time(0)	time(1)	time(2)	time(3)
14:13:12.1234567	14:13:12	14:13:12.1000000	14:13:12.1200000	14:13:12.1230000
time(4)	time(5)	time(6)	time(7)
14:13:12.1235000	14:13:12.1234600	14:13:12.1234570	14:13:12.1234567

As you can see the default format for a time data type is hh:mm:ss[.nnnnnnn]

Date

The date data type has a range 0001-01-01 to 9999-12-31 with the fixed granularity of 1 day. This will take 3 bytes of storage. The default format for a date data type is YYYY-MM-DD.

Datetime2

A variable precision date and time data type with a range 0001-01-01 00:00:00.0000000 to 9999-12-31 23:59:59.9999999 using between 6 to 8 bytes for storage

Specified scale	Result (precision, scale)	Column length (bytes)	Fractional seconds precision
Datetime2	(27,7)	8	7
Datetime2(0)	(19,0)	6	0-2
Datetime2(1)	(21,1)	6	0-2
Datetime(2)	(22,2)	6	0-2
Datetime(3)	(23,3)	7	3-4
Datetime(4)	(24,4)	7	3-4
Datetime(5)	(25,5)	8	5-7
Datetime(6)	(26,6)	8	5-7
Datetime(7)	(27,7)	8	5-7

So the queries:

SELECT CAST('2008-05-29 14:13:12.1234567' AS datetime2) AS [datetime2]

,CAST('2008-05-29 14:13:12.1234567' AS datetime2 (0) ) AS [datetime2(0)]

,CAST('2008-05-29 14:13:12.1234567' AS datetime2 (1) ) AS [datetime2(1)]

,CAST('2008-05-29 14:13:12.1234567' AS datetime2 (2) ) AS [datetime2(2)]

,CAST('2008-05-29 14:13:12.1234567' AS datetime2 (3) ) AS [datetime2(3)]

, CAST('2008-05-29 14:13:12.1234567' AS datetime2 (4) ) AS [datetime2(4)]

,CAST('2008-05-29 14:13:12.1234567' AS datetime2 (5) ) AS [datetime2(5)]

,CAST('2008-05-29 14:13:12.1234567' AS datetime2 (6) ) AS [datetime2(6)]

,CAST('2008-05-29 14:13:12.1234567' AS datetime2(7) ) AS [datetime2(7)]

Returns:

datetime2	datetime2(0)	datetime2(1)	datetime2(2)	datetime2(3)
2008-05-29 14:13:12.1234567	2008-05-29 14:13:12.0000000	2008-05-29 14:13:12.1000000	2008-05-29 14:13:12.1200000	2008-05-29 14:13:12.1230000
datetime2(4)	datetime2(5)	datetime2(6)	datetime2(7)
2008-05-29 14:13:12.1235000	2008-05-29 14:13:12.1234600	2008-05-29 14:13:12.1234570	2008-05-29 14:13:12.1234567

Note: This gives the wrong precision when using the February CTP.

Like the datetime data type the datatime2 and datetimeoffet data type will default a time portion to midnight 00:00:00.000 if not specified i.e.

SELECT CAST('2008-05-29’ AS datetime2) AS [datetime2]

Returns:

datetime2

2008-05-29 00:00:00.0000000

Note: This gives the wrong precision when using the February CTP.

Datetimeoffset

A timezone aware, variable precision date and time data type with a range 0001-01-01 00:00:00.0000000 to 9999-12-31 23:59:59.9999999 using between 8 to 10 bytes for storage. The timezone offset is based on a 24hr clock and can have the range -14:00 through +14:00. The two ISO 8601 formats YYYY-MM-DDThh:mm:ss[.nnnnnnn][{+|-}hh:mm] and YYYY-MM-DDThh:mm:ss[.nnnnnnn]Z (UTC) are valid string representations of the datetimeoffset data type

Specified scale	Result(precision, scale)	Column length (bytes)	Fractional seconds precision
datetimeoffset	(34,7)	10	7
datetimeoffset (0)	(26,0)	8	0-2
datetimeoffset(1)	(28,1)	8	0-2
datetimeoffset(2)	(29,2)	8	0-2
datetimeoffset(3)	(30,3)	9	3-4
datetimeoffset(4)	(31,4)	10	3-4
datetimeoffset(5)	(32,5)	10	5-7
datetimeoffset(6)	(33,6)	10	5-7
datetimeoffset(7)	(34,7)	10	5-7

The following are all the same moment:

SELECT CAST('2008-05-29 16:28:12.1234567 +02:15' AS datetimeoffset(7)) AS 'datetimeoffset'

,CAST('2008-05-29T11:58:12.1234567-02:15' AS datetimeoffset(7)) AS 'datetimeoffset IS08601'

,CAST('2008-05-29 14:13:12.1234567Z' AS datetimeoffset(7)) AS 'datetimeoffset UTC'

For more information on ISO 8601 date formats see http://en.wikipedia.org/wiki/ISO_8601

Datepart types

With the addition of these new more precise data types, it makes sense that the functions such as DATEPART have also been updated to reflect the higher accuracy.

Datepart	Abbreviation
microsecond	mcs
nanosecond	ns
TZoffset	tz
ISO_WEEK	isowk, isoww

For example:

DECLARE @datetime2 datetime2(7), @datetimeoffset datetimeoffset

SET @datetime2 = '2008-05-29T14:13:12.1234567'

SET @datetimeoffset = '2008-05-29T14:13:12.1234567+02:15'

/* Using full names */

SELECT DATEPART ( microsecond, @datetime2 ) AS [Microsecond]

,DATEPART ( nanosecond, @datetime2 ) AS [Nanosecond]

,DATEPART ( TZoffset, @datetimeoffset ) AS [TZoffset]

,DATEPART ( ISO_WEEK, @datetime2 ) AS [ISO_Week]

/* Using abbreviations */

SELECT DATEPART ( mcs, @datetime2 ) AS [Microsecond]

,DATEPART ( ns, @datetime2 ) AS [Nanosecond]

,DATEPART ( tz, @datetimeoffset ) AS [TZoffset]

,DATEPART ( isowk, @datetime2 ) AS [ISO_Week]

Both queries return the result set

Microsecond	Nanosecond	TZoffset	ISO_Week
123456	123456700	135	22

TZoffset requires a datetimeoffset value to return a non-null value according to Books Online although the February CTP 0 is returned instead. The Timezone offset value is in minutes so 2 hours 15 minutes is (2x60)+15=135

ISO_WEEK returns the week for the ISO 8601 week-date system. Each week is associated with the year in which the Thursday in the week occurs. i.e. week 1 of 2008 (2008W01) ran from Monday 31 December 2007 to Sunday, 6 January 2008.

DECLARE @datetime2_1 datetime2(7), @datetime2_2 datetime2

SELECT @datetime2_1 = '2008-05-29T14:13:12.1234567',

@datetime2_2 = '2008-05-29T14:13:12.1234578'

SELECT DATEDIFF( mcs, @datetime2_1, @datetime2_2 ) AS [Microseconds Difference]

,DATEDIFF( ns, @datetime2_1, @datetime2_2 ) AS [Nanoseconds Difference]

Returns:

Microseconds Difference	Nanoseconds Difference
1	1100

ISO_WEEK is not supported as a datepart in the datediff function.

DAY, MONTH and YEAR changes

The functions DAY, MONTH and YEAR can be used on datetime2, dateoffset and date datatypes but not time

DECLARE @datetime2 datetime2(7), @datetimeoffset datetimeoffset, @date date

SELECT @datetime2 = '2008-05-29T14:13:12.1234567'

,@datetimeoffset = '2008-05-29T14:13:12.1234567'

,@date = '2008-05-29'

SELECT DAY(@datetime2)

,DAY(@datetimeoffset)

,DAY(@date)

,MONTH(@datetime2)

,MONTH(@datetimeoffset)

,MONTH(@date)

,YEAR(@datetime2)

,YEAR(@datetimeoffset)

,YEAR(@date)

High Precision System Date and Time functions

Additional non-deterministic high precision functions have added to return the more accurate data types

SYSDATETIME returns the current database time stamp as datetime2(7)
SYSDATETIMEOFFSET similar to SYSDATETIME but also includes the database timezone offset as a datetimeoffset(7)
SYSUTCDATETIME returns the current database time stamp as datetime2(7) as a UTC time.

SELECT SYSDATETIME() AS [SysDateTime]

,SYSDATETIMEOFFSET() AS [SysDateTimeOffset]

,SYSUTCDATETIME() AS [SysUTCDateTime]

Returns:

SysDateTime	SysDateTimeOffset	SysUTCDateTime
2008-05-29 15:28:51.1202384	2008-05-29 15:28:51.1202384 +01:00	2008-05-29 14:28:51.1202384

This reflects that we are currently on British Summer Time. If you change the timezone to pacific time (GMT – 8:00) the results would be something like:

SysDateTime	SysDateTimeOffset	SysUTCDateTime
2008-05-29 07:29:47.6967536	2008-05-29 07:29:47.6967536 -07:00	2008-05-29 14:29:47.6967536

Note: This gives the wrong precision/format when using the February CTP.

DATE and Time modification functions

SWITCHOFFSET will return a datetimeoffset value into the time zone offset specified. The syntax of the function is :

SWITCHOFFSET ( DATETIMEOFFSET, time_zone )

SELECT SYSDATETIMEOFFSET() as [Current Time (BST)]

,SWITCHOFFSET(SYSDATETIMEOFFSET(), '-07:00' ) AS [Pacific Summer Time]

Returns:

Current Time (BST)	Pacific Summer Time
2008-05-29 21:32:10.4515504 +01:00	2008-05-29 13:32:10.4515504 -07:00

TODATETIMEOFFSET converts a local date and time value and a time zone offset to a datetimeoffset UTC value. The syntax of the function is:

TODATETIMEOFFSET ( datetime, time_zone )

CREATE TABLE #tmpdts (datetimecol1 datetime2, datetimecol2 datetime2, datetimecol3 datetime2 )

INSERT INTO #tmpdts ( datetimecol1, datetimecol2, datetimecol3 )

VALUES( SYSDATETIME(), SYSDATETIME(), SYSDATETIME())

UPDATE #tmpdts

SET datetimecol2 = TODATETIMEOFFSET(datetimecol2, '-07:00' )

,datetimecol3 = TODATETIMEOFFSET(datetimecol3, -420 )

SELECT datetimecol1, datetimecol2, datetimecol3 FROM #tmpdts

DROP TABLE #tmpdts

Returns:

datetimecol1	datetimecol2	datetimecol3
2008-05-29 21:47:36.6533632 +01:00	2008-05-29 13:47:36.6533632 -07:00	2008-05-29 13:47:36.6533632 -07:00

Both of these functions are deterministic

Looking Forward

As you can see there has been a great deal of improvements and added functionality available to the database architect and designer in the next release of SQL Server. These will help improve storage and accuracy when using the new precision data types for date and time.

For more information on these new data types and functions check out the SQL Server 2008 Books Online at http://msdn.microsoft.com/en-us/library/ms180878(SQL.100).aspx

Jaa

Time for a Date?

Lisäresursseja