SQL SERVER 2008 (KATMAI): SQL Server 2008 (KATMAI) TSQL Enhancements - Part 6

In the previous few sessions we have discussed a few new data types introduced by SQL Server 2008. So far we have seen DATE, TIME and DATETIME2. In this session we will examine yet another new data type, DATETIMEOFFSET.

DATETIMEOFFSET

DATETIMEOFFSET stores a DATE and TIME value along with the TIMEZONE information. SQL Server 2005 had two DATETIME related data types: DATETIME and SMALLDATETIME. None of them was able to store time-zone information. SQL Server 2008 added a few more date/time related data types: DATE, TIME, DATETIME2 and DATETIMEOFFSET. Out of this, DATETIMEOFFSET is the only SQL Server data type that can store TIMEZONE aware date values. The following code snippet shows a basic example.

DECLARE @dto AS DATETIMEOFFSET
SELECT @dto = '2008-05-15 3:30:00 PM +05:30' -- IST
SELECT @dto
/*
----------------------------------
2008-05-15 15:30:00.0000000 +05:30
*/

A Use Case

DATETIMEOFFSET data type is very much helpful to handle date/time data that is collected from different locations falling into different time zones. There are many situations were you really need a time-zone aware date/time value. The following example shows a scenario where the DATETIMEOFFSET data type is very much useful.

Assume that we have a database that split into different servers located in different parts of the world. For the sake of this example, assume that we have 3 servers, in NY, India and Singapore. Suppose we have an auditing functionality which keeps track of the data modifications being done by the users. When something is modified, we log the name of the user and current time to an audit table.

Suppose the following sequence of events happen one morning.

User Bob modifies a record from Singapore at 11 AM
User Mike makes a modification at 11.30 AM from India
User Jacob makes another modification at 10 AM from NY

In the absence of a TIME-ZONE aware date column, we might get the following results.

DECLARE @t TABLE (UserName VARCHAR(20), ModificationTime DATETIME)
INSERT INTO @t (UserName, ModificationTime)
 SELECT 'Jacob','2008-05-15 10:00:00' -- NY
INSERT INTO @t (UserName, ModificationTime)
 SELECT 'Mike','2008-05-15 11:30:00' -- India
INSERT INTO @t (UserName, ModificationTime)
 SELECT 'Bob','2008-05-15 11:00:00' -- Singapore

SELECT * FROM @t ORDER BY ModificationTime
/*
UserName             ModificationTime
-------------------- -----------------------
Jacob                2008-05-15 10:00:00.000
Bob                  2008-05-15 11:00:00.000
Mike                 2008-05-15 11:30:00.000
*/

It is apparent that the results given above is wrong. User Jacob modified the record several hours after Mike modified it from India. Bob has modified the data before anyone else from Singapore. A time zone aware date/time column can make this much easier. Here is an example.

DECLARE @t TABLE (UserName VARCHAR(20), ModificationTime DATETIMEOFFSET)
INSERT INTO @t (UserName, ModificationTime)
 SELECT 'Jacob','2008-05-15 10:00:00 AM -05:30' -- NY
INSERT INTO @t (UserName, ModificationTime)
 SELECT 'Mike','2008-05-15 11:30:00 AM +05:30' -- India
INSERT INTO @t (UserName, ModificationTime)
 SELECT 'Bob','2008-05-15 11:00:00 AM +08:00' -- Singapore

SELECT * FROM @t ORDER BY ModificationTime
/*
UserName             ModificationTime
-------------------- ----------------------------------
Bob                  2008-05-15 11:00:00.0000000 +08:00
Mike                 2008-05-15 11:30:00.0000000 +05:30
Jacob                2008-05-15 10:00:00.0000000 -05:30
*/

This example clearly demonstrates a typical usage of the DATETIMEOFFSET data type. Let us have a closer look into the DATETIMEOFFSET data type in detail.

Range

DATETIMEOFFSET supports the same range as DATETIME2. It supports dates ranging from 1st January, 0001 through 31st December 9999. In addition, it supports time zone information between -14:00 and +14:00.

DECLARE @min AS DATETIMEOFFSET = '0001-01-01 00:00:00.0000000 -14:00'
DECLARE @max AS DATETIMEOFFSET = '9999-12-31 23:59:59.9999999 +14:00'
SELECT @min AS Min, @max AS Max
/*
Min                                Max
---------------------------------- ----------------------------------
0001-01-01 00:00:00.0000000 -14:00 9999-12-31 23:59:59.9999999 +14:00

Storage

Storage of DATETIMEOFFSET data types takes 8 to 10 bytes depending on the precision requested. When declaring a DATETIMEOFFSET variable, you can specify the required precision, just like DATETIME2.

DECLARE
 @dto AS DATETIMEOFFSET = '2008-07-02 22:49:06.3593750 +05:30',
 @dto0 AS DATETIMEOFFSET(0) = '2008-07-02 22:49:06.3593750 +05:30',
 @dto1 AS DATETIMEOFFSET(1) = '2008-07-02 22:49:06.3593750 +05:30',
 @dto2 AS DATETIMEOFFSET(2) = '2008-07-02 22:49:06.3593750 +05:30',
 @dto3 AS DATETIMEOFFSET(3) = '2008-07-02 22:49:06.3593750 +05:30',
 @dto4 AS DATETIMEOFFSET(4) = '2008-07-02 22:49:06.3593750 +05:30',
 @dto5 AS DATETIMEOFFSET(5) = '2008-07-02 22:49:06.3593750 +05:30',
 @dto6 AS DATETIMEOFFSET(6) = '2008-07-02 22:49:06.3593750 +05:30',
 @dto7 AS DATETIMEOFFSET(7) = '2008-07-02 22:49:06.3593750 +05:30'

SELECT
 @dto,   --2008-07-02 22:49:06.3593750 +05:30
 @dto0,  --2008-07-02 22:49:06 +05:30
 @dto1,  --2008-07-02 22:49:06.4 +05:30
 @dto2,  --2008-07-02 22:49:06.36 +05:30
 @dto3,  --2008-07-02 22:49:06.359 +05:30
 @dto4,  --2008-07-02 22:49:06.3594 +05:30
 @dto5,  --2008-07-02 22:49:06.35938 +05:30
 @dto6,  --2008-07-02 22:49:06.359375 +05:30
 @dto7   --2008-07-02 22:49:06.3593750 +05:30

SELECT
 DATALENGTH(@dto),  --10
 DATALENGTH(@dto0), --8
 DATALENGTH(@dto1), --8
 DATALENGTH(@dto2), --8
 DATALENGTH(@dto3), --9
 DATALENGTH(@dto4), --9
 DATALENGTH(@dto5), --10
 DATALENGTH(@dto6), --10
 DATALENGTH(@dto7)  --10

New Functions

The following new time-zone related functions were added that either operates on a DATETIMEOFFSET variable or returns a DATETIMEOFFSET data type.

SWITCHOFFSET()

This function modifies the offset value (time zone info) of a DATETIMEOFFSET value. The following snippet shows an example where the time-zone offset of a DATETIMEOFFSET value is changed using SWITCHOFFSET function.

DECLARE @dto AS DATETIMEOFFSET
SELECT @dto = '2008-07-02 22:49:06.3593750 +05:30' -- Indian Time
SELECT @dto AS IndianTime
/*
IndianTime
----------------------------------
2008-07-02 22:49:06.3593750 +05:30
*/
SELECT @dto = SWITCHOFFSET(@dto,'-05:00') -- Estern time
SELECT @dto AS EasternTime
/*
EasternTime
----------------------------------
2008-07-02 12:19:06.3593750 -05:00
*/

TODATETIMEOFFSET()

This function adds time zone information to a DATETIME variable/value and returns a DATETIMEOFFSET value. In the following example, '-5:30' is added to a DATETIME variable to obtain the DATETIME value as per Eastern Standard Time.

DECLARE @dt AS DATETIME = '2008-07-02 22:49:06'
SELECT @dt DateTime
/*
DateTime
-----------------------
2008-07-02 22:49:06.000
*/
DECLARE @dto AS DATETIMEOFFSET = TODATETIMEOFFSET(@dt, '-05:00')
SELECT @dto AS DateTimeOffset
/*
DateTimeOffset
----------------------------------
2008-07-02 22:49:06.0000000 -05:00
*/

SYSDATETIMEOFFSET()

This function returns a DATETIMEOFFSET value containing current date, time and time-zone. For example:

SELECT SYSDATETIMEOFFSET()
/*
----------------------------------
2008-07-02 23:25:30.5000000 +05:30
*/

Enhanced Functions

DATEPART()

The DATEPART() function takes two more time-zone related flags: TZ and TZOFFSET. TZ is the short name of TZOFFSET. It returns the time-difference of the given time zone in minutes. For example:

DECLARE @dt1 DATETIMEOFFSET = SYSDATETIMEOFFSET()
SELECT
 @dt1,                   -- returns 2008-07-03 22:37:56.8750000 +05:30                
 DATEPART(TZ, @dt1),     -- returns 330 (05:30 hours IST)
 DATEPART(TZOFFSET,@dt1) -- returns 330 (05:30 hours IST)

Other

ISDATE()

The ISDATE() function still does not recognize DATETIMEOFFSET values. I guess this will be fixed in the future releases. The following returns '0' indicating that the value is not a valid DATE value.

SELECT ISDATE('2008-07-02 22:49:06.3593750 +05:30')

Time Zone Names

DATETIMEOFFSET data types can store time zone information in terms of the time-difference from GMT but cannot store the time zone name (say, Indian Standard Time etc). The following post shows a workaround to find time zone name from a DATETIMEOFFSET value. http://blogs.msdn.com/mssqlisv/archive/2008/03/18/using-time-zone-data-in-sql-server-2008.aspx

SQL Server 2008 (KATMAI) TSQL Enhancements

SQL SERVER 2008 (KATMAI)

Thursday, July 3, 2008

SQL Server 2008 (KATMAI) TSQL Enhancements - Part 6 - DATETIMEOFFSET

0 comments:

Labels

My Blog List

Contact

SQL Server Bloggers

Blog Directories