3

I'm looking for suggestions and personal experiences with time localizations in TSQL and sql job scheduling (We are using SQL Server 2012).

I came across those situations where I would need more support for timezones and DST support in MS SQL Server:

  • Attempt to store autocomputed column for date in different timezone in order to be able to cluster index on this column.
  • Being able to also update exisiting table containing datetime2 field in UTC time in order to achieve previous goal (e.g. index table on PST date; without time part)
  • Attempt to schedule a sql job that would ran at specific time in different timezone (e.g. to create materialized view containing only data for current PST day).

I searched through web and found those possibilities:

  • A Store table with time zones offsets + DST info and calculate the proper localized time with TSQ (= reinventing the wheel requiring maintenance of the table with timezones info)
  • B Perform the localization completely in client code and store the result in DB. This option requires custom throw away apps for updating existing data, and also custom scheduled task app replacing the sql jobs functionality.
  • C Using CLR stored procedures. I'm yet to investigate this option more in depth.

Are there any other options? Are there any strong pros or cons of any option that would rule such an option or other options out?

Andomar
  • 232,371
  • 49
  • 380
  • 404
Jan
  • 1,905
  • 17
  • 41
  • 1
    generally speaking, you should be storing all dates/times as UTC in the database, and converting to appropriate to client TZs upon retrieval. – Marc B Dec 09 '13 at 14:38
  • AFAIK, despite improvements like [datetimeoffset](http://msdn.microsoft.com/en-us/library/bb630289.aspx), things like summertime are still impossible to do in T-SQL. MSDN has a funny way of saying "you're on your own": "The given time zone offset will be assumed to be daylight saving time (DST) aware and adjusted for any given datetime that is in the DST period.". That means MarcB's suggestion is still the only sane approach. – Andomar Dec 09 '13 at 14:41
  • Thanks MarcB and Andomar. Your answers are exactly the ones I was afraid of :|. Does it also meant that if I want to run sql job at specific localizable DST-aware time I shouldn't use sql job? – Jan Dec 09 '13 at 16:10
  • @Jan - Regarding the SQL job, probably not a great idea. [Take a look here](http://serverfault.com/a/554761/100701) – Matt Johnson-Pint Dec 09 '13 at 19:11

1 Answers1

1

Unfortunately, your possibilities "A" and "B" are presently the only two viable approaches.

The downside to option "A" is the maintenance cost, and that I have yet to see something that cleanly parses a valid time zone database (such as the IANA database) into SQL tables with functions that operate over those tables cleanly. This should be possible to do, I just haven't seen it yet. (Note that it does exist for MYSQL, and Oracle and Postgres have theirs built in, but nothing for MS SQL Server that I am aware of).

Option "B" is the usual recommendation, but you run into a few unachievable use cases like those you mentioned. A more commonly desired use case is to emit a "daily report" for a specific time zone. Grouping by date is unachievable when a UTC datetime has to first be converted to a specific time zone. Also, one might want to run these from SSRS or other reporting tools.

Option "C" sounds promising, but there is a problem: The TimeZoneInfo class can't operate in SQLCLR unless you enable "unsafe" mode, which creates a security and stability risk that is undesirable for a production environment. You can read more here.

I am working on a better solution, which is to modify Noda Time to be usable from a "safe" mode SQLCLR environment. This is on the roadmap, and preliminary testing looks promising. It would require you to use IANA TZDB time zones, which is probably a good idea anyway. When done, you will have some simple user-defined functions to do timezone conversions, which rely on the code and data of the Noda Time assembly. I will update this post when it is ready, but I don't have an ETA as of yet.

UPDATE After a lot of testing, I decided it would be better to build a SQL-based solution, similar to what is described in option A. You can now use my SQL Server Time Zone Support project, which imports IANA time zone data (via Noda Time) directly into SQL-Server tables. It then binds that information to functions you can call for easy conversion. It is reliable, and maintainable, and works in all SQL Server environments, 2008 R2 and higher, including Azure SQL DB.

Community
  • 1
  • 1
Matt Johnson-Pint
  • 230,703
  • 74
  • 448
  • 575
  • Thanks for the answer Matt. I read through your other posts to this topic and I see you did a deep research in this topic. And reality seems to be as unfortunate as I was afraid of. I really appreciate your effort and time put into sharing your findings! Btw. Have you been doing any preliminary perf. tests of your adjustments to the Noda Time? – Jan Dec 09 '13 at 20:18
  • Not quite yet. Part of the problem is that proper [caching is very difficult to do in SQLCLR](http://stackoverflow.com/q/16303557/634824). I'll be sure to take some metrics when I get closer to a release. Slow moving right now. – Matt Johnson-Pint Dec 09 '13 at 20:29
  • Matt, just wondering if there has been any progress on the Noda Time => SQLCLR mapping? – EverPresent Sep 18 '14 at 15:59
  • @EverPresent - sorry, but I haven't done much with it since then. – Matt Johnson-Pint Sep 18 '14 at 16:39