3

I have a need to track user's time zone so processing of their information (or no processing) can happen at specific times they specify (in their own time zone).

The obvious answer is to store the time zone in the user database with their profile information. The thing that is a little tricky is daylight savings. Notice from this image below that most northern and southern regions use a daylight time offset. So storing a time zone offset doesnt feel like it will work, since that offset could change through the year. Instead I'm thinking of storing the time zone code (something like PST) and letting a standard time library lookup the offset.

So a couple things I'm thinking about are:

1) Does TSQL have this library for converting from a time zone code (PST) to a current UTC offset? 2) I know .net can do this, but I'd prefer to do this in the database since that's where my queries will run.

enter image description here

Paul Fryer
  • 9,268
  • 14
  • 61
  • 93
  • I noticed TSQL has a 'SWITCHOFFSET' function that converts from a given offset (say -00:00) to some other time zone offset (say -08:00). However that function assumes the offset "to be daylight-saving aware and adjusted", so that doesn't really help find the DTS adjusted offset value dynamically. [1] http://msdn.microsoft.com/en-us/library/bb677244.aspx – Paul Fryer Jul 24 '12 at 15:45
  • 1
    My experience is that TSQL does not adjust for time zone. There was great post on using CLR with SQL for getting correct time. – paparazzo Jul 24 '12 at 15:48
  • http://stackoverflow.com/questions/3404646/how-to-calculate-the-local-datetime-from-a-utc-datetime-in-tsql-sql-2005 – paparazzo Jul 24 '12 at 15:49
  • @Blam Good idea, using a CLR function. I have been considering using this in a shared hosted SQL environment, so I'm not sure if they will support CLR functions. I'm starting to think the only way to do this with TSQL is to use time zone table(s). http://en.wikipedia.org/wiki/Tz_database – Paul Fryer Jul 24 '12 at 15:59
  • This might be relevant http://stackoverflow.com/questions/4331189/datetime-vs-datetimeoffset/14268167#14268167 – Lester S Feb 10 '15 at 00:33

1 Answers1

0

Found what looks like a promising .Net library. The advantage of this one over the built in .Net one is that you can update the timezone database by dropping a new copy on disk. With the .Net one you have to wait for new compiled release. (however time zone changes are probably rare).

Paul Fryer
  • 9,268
  • 14
  • 61
  • 93