Sql-server – SQL Server 2005/2008 system timezone

sql serversql-server-2005sql-server-2008-r2

I work on a ship that has multiple SQL Servers, both 2005/2008 versions.

As the ship moves from area to area so does our timezone. Sometimes we can jump ahead one hour and then back again.

My question is what is the best practice for this sort of situation, is it safe to change the system time so repeatedly? Should the DB be stopped before doing so? What sort of problems can arise?

Any feedback appreciated.

Thanks,
Kris

Best Answer

Even when a machine is fixed in a given timezone, storing timezone-dependent date/time information is not very safe. What do you do during Daylight Saving Time? You have the same problem - you skip ahead or repeat an hour. As the others suggested, you should always store your data using UTC, then it is easy to convert to any time zone you want, without having to know what time zone the server was in when the data was stored (though you can optionally store that information separately, or you can use DATETIMEOFFSET - but I haven't found that very useful since it, also, doesn't account for DST).

For easy situations (e.g. no DST involved), you can write a simple helper function to convert UTC to whatever time zone is appropriate. Even better if you just know the offset (e.g. +5 hours or -4 hours). This is a simple DATEADD operation.

For situations where DST is involved, I've always used a calendar table. It is very easy to populate a table with 30 years of data (one row for each day) and also store the offset, in minutes, for each day (given your time zone and whether the date falls in DST or not). Yes these can change for future dates, but the only time this can become tricky is if you're booking events farther into the future than the advance warning you get when the government decides to change the policy (as they did a few years ago). Converting UTC data on a given date to the time zone of your choice is still a simple DATEADD operation, except now it involves looking up the actual difference for that date in a table.

For data you're entering now, you can simply use GETUTCDATE() instead of GETDATE()/CURRENT_TIMESTAMP. This returns UTC time regardless of your time zone, and whether or not the machine is set up to observe DST.

Related Question