Add a SET TIMEZONE command - by Erland Sommarskog

Status : 


Sign in
to vote
ID 293933 Comments
Status Active Workarounds
Type Suggestion Repros 1
Opened 8/18/2007 3:31:59 AM
Access Restriction Public


SQL 2008 adds the datetimeoffset type which is great. sysdatetimeoffset() (which should
really be CURRENT_TIMESTAMP if this name was not already in use in an incorrect way), returns a value in the server's local time zone. There is also a discussion to change
switchoffset() to support switch to local time zone when no offset is passed.

But what is the local time zone? Is it really feasible to trust the server? Should it not
be on session-level? I think it's better to try to add this to Katmai. If you defer it until
later, you may get some compatibility issues.

There are two ways that a session could set the time zone: 1) implicitly from the client API
that would read the client's regional settings 2) a SET TIMEZONE command. The problem with #1 is that this would require changes in the APIs, so #2 appears the only feasible 
solution, at least in the short term.

Next question is: how do I specify the time zone? Of course, this is simple:

   SET TIMEZONE +02:00

but I suspect that users will want something which is DST aware, so that I get +02:00 
in summer, and +01:00 in winter. (For you in Redmond that is -07:00 in summer and
-08:00 in winter.) I don't think it's a good idea to give SET TIMEZONE an elaborate
syntax to specify the DST changes, but users would like to refer to Windows time zones,
not the least so that they don't have to change code if the timz-zone rules changes,
as happend in the US this year. So you would need to find a way to identify the
Windows time zones. Maybe:

   SET TIMEZONE Amsterdam

from the first city specified in the Windows drop-down for time zones. Explicit offsets
could still be permitted, and they would explicitly not be DST aware.

The default for a connection would of course be to inherit the time zone from the
server. And here is one thing to note: if you only implement SET TIMEZONE with
offsets, the default would be a DST-unaware setting, but once you have DST-aware
settings, this would be the desired default. But changing defaults would cause
compatibility issues. Therefore, there is all reason to think this through until the
end of the road. If you do not go all the way in Katmai, be careful to leave the door
open for the future. (Because DST-awareness will be a customer requirement sooner
or later.)

Sign in to post a comment.
Posted by Vachhrajani, Nakul on 5/27/2013 at 10:03 PM
Hello! Any updates on this one?
Posted by Microsoft on 9/14/2007 at 11:20 PM
Thanks for the great feedback.

We're currently considering this seriously for Katmai.

Posted by Mark Yudkin on 8/26/2007 at 11:50 PM
The ANS SET TIME ZONE statement (e.g. ISO/IEC 9075-2:1999, section 18.4) has an <interval value expression> as its argument, so that one could also argue that MS should be implementing INTERVALs and interval arithmetic as part of the date and time enhancement, rather than using MS-specific functions such as a datetimeoffset type.

ANS in fact specifies that DATEs, TIMEs and TIMESTAMPs support WITH TIME ZONE and WITHOUT TIME ZONE options, thus making these options of the basic data types.

It is a great pity that MS have chosen to implement their own date / time functionalty, instead of the ANS standard. Worse still is that the MS functionality is bith more awkard and less complete than the standard.
Posted by Razvan Socol on 8/21/2007 at 1:50 AM
I'd like to point out that SET TIME ZONE is a part of the ANSI SQL standard since SQL-92. It would be a good time to implement this in Katmai (or at least think thouroughly about how to implement it in the release after Katmai), following the ANSI standard as much as possible.