How to handle TimeZone properly in SQL Server?
My local development server is in the Middle East, but my production server is in the UK.
I need to show the date to the user in their time zone. For example, if a user is in Saudi Arabia then I need to show the time according to Saudi Arabia format.
Should I create a new database table called TimeZone and save the Time in UTC?
Effectively Converting dates between UTC and Local (ie. PST) time in SQL 2005 http://stackoverflow.com/questions/24797/effectively-converting-dates-between-utc-and-local-ie-pst-time-in-sql-2005
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.
This web as well as native mobile. Yes this effect different clients differently.
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:50is meaningless without either (a) including the time zone's then-current UTC offset(note 1), 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 incomparable, and the data is corrupt. (The latter method is playing with fire(note 2), by the way; don't do that.)
In SQL Server 2008+, you can store the offset with the time directly by using the
datetimeoffsetdata type. (For completeness, in 2005 and before, I would add a second column to store the then-current UTC offset value (in minutes).)
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.
A timezone's UTC offset is not fixed: consider daylight savings where a zone's UTC offset varies by plus or minus an hour. Also zones' daylight savings dates vary on a regular basis. So using
datetimeoffset(or a composite of
UTC offset at that time) results in maximum information recovery.
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).
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.
As I understand it an offset is not the same as timezone... times stored in datetimeoffset lose information like daylight savings time awareness... More info: http://stackoverflow.com/tags/timezone/info
@PeterMcEvoy DST has no relevance here. `datetimeoffset` means "this is the local time of the user/computer when the thing happened" - we don't need to know if DST was in effect or not, because the given UTC offset is always there (embedded within the `datetimeoffset` value).
Don't conflate "public API" with decisions about how to store internally. The public API could specify that an offset is expected. Internally, datetimes could (still) always be stored in UTC+0. Best of both worlds, IMHO. Storing an offset *with* each datetime risks offset getting separated from datetime. And harder to sort by time. Also you'll end up converting to/from a local time offset that may be different than the original event time offset. E.g. event at facility elsewhere. Don't complicate that by *storing* the offset; offset is dynamic, depending on POV. OTOH your approach is valid too
I have developed a comprehensive solution for time zone conversion in SQL Server. See SQL Server Time Zone Support on GitHub.
It properly handles conversions between time zones, and to and from UTC, including daylight saving time.
It is similar in concept to the "T-SQL Toolbox" solution described in adss's answer, but it uses the more common IANA / Olson / TZDB time zones instead of Microsoft's, and it includes utilities to keep the data maintained as new releases of the TZDB come out.
Example usage (to answer the OP):
SELECT Tzdb.UtcToLocal(sysutcdatetime(), 'Asia/Riyadh') as CurrentTimeInSaudiArabia
See the readme on GitHub for additional APIs and detailed explanation.
Also, note that since this is a UDF based solution, it is not designed with performance as the primary objective. It would still be better if this functionality was built in to SQL Server, similarly to how the
CONVERT_TZfunction exists in Oracle and MySQL.
I have developed and published the “T-SQL Toolbox” project on codeplex to help anybody who struggles with datetime and timezone handling in SQL Server. It’s open source and completely free to use.
It offers easy datetime conversion UDFs using plain T-SQL with additional configuration tables out of the box.
In your example, you can use the following sample:
SELECT [DateTimeUtil].[UDF_ConvertLocalToLocalByTimezoneIdentifier] ( 'GMT Standard Time', -- the original timezone in which your datetime is stored 'Middle East Standard Time', -- the target timezone for your user '2014-03-30 01:55:00' -- the original datetime you want to convert )
This will return the converted datetime value for your user.
A list of all supported timezones can be found in table "DateTimeUtil.Timezone" also provided within the T-SQL Toolbox database.
Such a toolkit would seem to be a great help for a developer. However, a scalar function is a black box to the query optimiser. It means that when I apply your function to a column, the configuration tables you are mentioning will be hit as many times as there are rows in the result set (assuming I use the function in the SELECT clause only). That doesn't seem a nice perspective in terms of performance. I haven't really tested your toolkit, though, so I can't say for sure, that's just a general concern based on what I know.
Andriy, sure, you are right from a performance perspective. The UDFs query tables are not designed for mass data operations, but for easy usage. Nevertheless, they perform quite ok up to about 100.000 records on my machine.
If one has to process more records in his use case, and performance matters, you should better think of persisting pre-calculated data. But even then, these UDFs might help to persist the datatime values in the required timzones.
From my perspective this issue is not about performance, and more about just getting the basic functionality in there. To be able to return accurate information from a query is the first thing. Working out some temp table to cache things and then refer to that in the query or however you handle it to improve performance comes secondary.
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.
I might be missing something obvious, but if all you want to do is display the date using the user timezone and language format, the simplest way is to always store the dates in UTC in the database and let the client application convert from UTC to local timezone and use the user regional settings to format the date accordingly.
For most cases this is correct, but consider daylight saving time. If you record a time in the summer, in the winter it will be shown as a different time. This is a bug that was in Exchange/Outlook for many years frustrating customers for whom the exact time could mean winning or losing a patent. So if that's not what you want, you will need to know what the timezone was at the time this time was recorded. Ideally, you will even know this information for the time at which the time was recorded, as you may need historical information on timezones, which sometimes change.