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

3 Answers 3


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.

  • What do you mean by "DATETIMEOFFSET ... doesn't account for DST"? That data type stores a time and offset, so you can store a UTC time plus the applicable offset where you recorded the time. It's pretty handy. Jul 2, 2012 at 16:21
  • @NickChammas But to give it the proper offset, you need to know whether DST is in effect on the date in question. Think about being in the Eastern time zone, and setting a project date that starts 6 months from now and ends 9 months from now. Are both those dates in DST? How do you dictate the offset for each date? SQL Server isn't going to figure that out for you. (Imagine for a moment that time is important in this case.) Jul 2, 2012 at 16:29
  • Right, it's just a data type. It doesn't figure anything out for you. The advantage it provides is that it lets you document, right alongside the timestamp, the relevant offset so you can reconstruct the local time as needed. You still have to input the offset yourself, but if you don't, you still have the UTC time in there. Jul 2, 2012 at 16:51
  • @NickChammas But if you store just the UTC time, isn't that all that's relevant? You can always figure out what time that is in a different time zone after the fact. In all cases I've seen, multiple people wanted the data shown in their own time zone. So DATETIMEOFFSET works great in that case but only if all users are in the same time zone. I think people expect it to be DST aware, but it isn't... Jul 2, 2012 at 16:52
  • No, the UTC time isn't all that's relevant. Easy example: BI cares to know that this user made this purchase right after getting out of work at 6pm local time as opposed to 1am local time, so we want to know the user's offset. At the same time, using the UTC time lets us consistently interpret the time and correlate it to other pieces of time data. DATETIMEOFFSET lets you store your times as UTC but maintain the relevant offset (typically of business value) in the same field. Jul 2, 2012 at 16:59

Store and retrieve your time related data in UTC rather than current time zone.

Also shift your databases on a server machine and don't change it's time.


Always store the time stamp as UTC. No need to stop DB while setting the system time.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.