[dba-SQLServer] Database servers and timezones

Gustav Brock gustav at cactus.dk
Sun Apr 24 02:43:53 CDT 2016


Hi all

When I think about it, I have seen close to nothing on how to handle timezones in database servers when users are spread across several timezones. Given that alone the US spans a handful of timezones and Russia elleven(!), this must be a common task to address.

I just ran a test from one of our Azure SQL databases:

    select getdate() from sometable

and it returned 

    2016-04-24 07:03:02.187 

which equaled the current UTC time.

This server is located in "Northern Europe", actually Ireland I think, so that is not a big surprise, but which timezone do you in the US set on a server located, say, in Chicago, that also will service users in Alaska or Hawaii? And how do you convert to and from the timezones, not to say deal with DST?

Perhaps one should store both the UTC time, the local (or "source") time, and the timezone difference (including DST offset) as - in queries - it will be very slow always to have to convert time either to or from UTC.

I have never dealt with applications expected to operate across timezones, and servers have been local only, so I have safely could use Date() (and GetDate() in PT queries) but this picture changes if I move data to the cloud.

/gustav


More information about the dba-SQLServer mailing list