Explore our sites

Stack Exchange
tour help
Take the 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.
up vote 3 down vote favorite

I have some issue which needed to be fixed quickly. My local development server is in middle east. But my production server is in UK. Now, I need to show the date to user to thier timezone. For example, if a user is in Saudi Arabia, then I need to show the time according to Saudi Arabia format. Should I need to create a new database table called TimeZone and I will save the Time in UTC. Please suggest me/

share|improve this question
 
Effectively Converting dates between UTC and Local (ie. PST) time in SQL 2005 http://stackoverflow.com/questions/24797/effectively-converting-dates-between-u‌​tc-and-local-ie-pst-time-in-sql-2005 –  mehdi Apr 7 at 9:29
 
Is this a web application, or desktop application, or ...? How this gets implemented varies greatly by the client delivery mechanism, because what you need is dependent on the client side. –  Jon Seigel Apr 7 at 16:46
 
This web as well as native mobile. Yes this effect different clients differently. –  user960567 Apr 7 at 17:16
 
Seems like your question is not just about database itself, but involves application development too. This Stack Overflow question is a must-read for you then: Daylight saving time and Timezone best practices –  Gan Jun 13 at 11:23
add comment

2 Answers

up vote 5 down vote accepted

There's no quick fix for this, unfortunately. Internationalization of an application should be part of the very first design discussions, as it really goes to the core of a lot of different areas, including date/time comparisons and output formatting.

Anyways, to get on the track of Doing It Right, it's essential to store the time zone information with the time. In other words, realizing that the date/time 20130407 14:50 is meaningless without either (a) including the time zone offset, or (b) ensuring that all logic inserting these values first converts to a certain fixed offset (most likely 0). Without either of those things, two given time values are uncomparable, and the data is corrupt. (The latter method is playing with fire, by the way; don't do that.)

In SQL Server 2008+, you can store the time zone with the time directly by using the datetimeoffset data type. (For completeness, in 2005 and before, I would add a second column to store the time zone.)

This makes it easy for a desktop-type application, as these platforms normally have mechanisms to automatically convert a date/time + time zone to a local time and then format for output, all based on the user's regional settings.

For the web, which is an inherently disconnected architecture, even with the back-end data set up properly, it's more complex because you need information about the client to be able to do the conversion and/or formatting. This is usually done via user preference settings (the application converts/formats things before output), or simply showing things with the same fixed format and time zone offset for everyone (which is what the Stack Exchange platform currently does).

You can see how if the back-end data is not set up properly, very quickly it's going to get complicated and hacky. I would not recommend going down any of those paths because you'll just end up with more problems down the line.

share|improve this answer
 
Why is using the latter method (b) playing with fire? Could you explain more on that? –  Gan Jun 10 at 6:01
1  
@Gan: It's about controlling data inputs. While there's no fool-proof way to validate incoming values, it's better to enforce a simple standard that doesn't involve computations. If a public API expects a data type that includes an offset, that requirement will be clear to the caller. If that wasn't the case, the caller has to rely on documentation (if they read it), or the computation is done incorrectly, etc. There are fewer failure/bug modes when requiring an offset, in particular for a distributed system (or even just web/database on separate servers as the case here). –  Jon Seigel Jun 11 at 16:40
 
Yes, agree with your comment on controlling the data input. In your answer above, you were talking about storing datetime values. Generally, datetime values would be stored based on a chosen offset (i.e. 0), so there is no need to store the datetime values with offset, unless there is some kind of special requirements to know the origin local time, which is not often necessary. –  Gan Jun 13 at 11:11
 
@Gan: Perhaps. Storing the offset anyway kills two birds with one stone; and even if that isn't required now, it makes the possibility available later on if necessary. True it takes up more storage, but I think it's worth the trade-off because the data is lost if it's never recorded in the first place. –  Jon Seigel Jun 13 at 16:32
add comment
up vote 1 down vote

SQL Server made modifications in 2005 onward where the internal timezone is saved in UTC. This was largely due to geo-replication and HA projectors involving log shipping, and having the log shipping times saved in different time zones made it impossible for the old method to restore them.

Thus, saving everything internally in UTC time allowed SQL Server to work well globally. This is one of the reasons why daylight savings is kind of a pain to deal with in Windows, because other MS products such as Outlook also save the date/time internally as UTC and create a offset that needs to be patched.

I work in a company where we have thousands of servers (not MS SQL Servers though, but all kinds of servers) spread out all across the world, and if we didn't specifically force everything to go by UTC, we would all go insane very quickly.

share|improve this answer
add comment

Your Answer

 
discard

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.