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