Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
add comment

migrated from stackoverflow.com Jul 1 '12 at 18:08

This question came from our site for professional and enthusiast programmers.

3 Answers

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.

share|improve this answer
    
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. –  Nick Chammas Jul 2 '12 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.) –  Aaron Bertrand Jul 2 '12 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. –  Nick Chammas Jul 2 '12 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... –  Aaron Bertrand Jul 2 '12 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. –  Nick Chammas Jul 2 '12 at 16:59
show 5 more comments

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.

share|improve this answer
add comment

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

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.