What to do with DATETIMEOFFSET?

Someone asked me today if the time zone of a specific instance of SQL Server could be changed to match the country which that instance served.

Some database products allow you to set this at an engine level which made me wonder if your data’s time fields “move” with the time zone setting of the database server instance?  If something was logged as happening at 9am Paris time it happened then, if I change my database server parameters did that event now happen at 9am New York time?  Perhaps relative timestamps are useful in some situations but how do we ensure that business transactions which only ever happen once and at a specific and static point in time get logged permanently?

I knew SQL Server gets its local and UTC times directly from the Windows operating system which means any recognition of time zone within a record’s time stamp needs to be identified and recorded at the application level.  SQL Server will always provide and handle dates independently of time zone deviations, which makes the data it hands globally portable.

Looking deeper into this subject topic I looked at how the new datetimeoffset datatype could help.  This stores the local time along with its offset from UTC and although having a datatype which stores that information in one value is new those datetime components aren’t as the four queries below show: (change your Windows time zone to something extreme to see the benefit during UK winter time!)

select ‘My local date/time is ‘ + convert(varchar(20),GETDATE(),120)

select ‘My UTC date/time is ‘ + convert(varchar(20),GETUTCDATE(),120)

select ‘My time zone offset is ‘ + CONVERT(varchar(20),datediff(hh,GETUTCDATE(),GETDATE())) + ‘ hours’

select ‘My sysdatetime as a datetimeoffset is ‘ + convert(varchar(20),datepart(tz,sysdatetimeoffset()) / 60)

I then thought about how to use the datetimeoffset datatype to store times which had some level of variance in their time zones, such as the following:

rowDateOffSet   rowLocalDate rowUTCDate rowOffset
————–  ———— ———- ———
12:20:27 -08:00 12:20:27     20:20:27   -8
12:20:27 -08:00 12:20:27     20:20:27   -8
15:20:45 -05:00 15:20:45     20:20:45   -5
15:20:45 -05:00 15:20:45     20:20:45   -5
20:20:59 +00:00 20:20:59     20:20:59   0
20:20:59 +00:00 20:20:59     20:20:59   0
09:21:20 +13:00 09:21:20     20:21:20   13
09:21:20 +13:00 09:21:20     20:21:20   13

The abbreviated for formatting data in the table could be part of the summary following an import of global sales data for example. 

If I wanted to look at just one country’s business hours trading data I’d probably query on rowLocalDate, if I wanted to look at global activity in a specific period I’d probably use rowUTCDate.  By knowing the local datetime, the UTC datetime and calculating the time zone difference at the point of capture I can use regular T-SQL functions to find or search on any combination of local time and UTC times, regardless of what the server’s local timezone setting is or where in the world the source data came from. 

The datetimeoffset column is a great way to visually see that the time in the left of the column isn’t UTC and what timezone it came from.  Where in my view SQL Server lacks functionality at the moment is to do much with that datetimeoffset column, like easily convert it back to a base UTC time.  I suspect there are a generation of .net apps which will directly consume and manipulate the datetimeoffset datatype, time will tell.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s