Getting a local date/time for a specific timezone in SQL Server

Question

Using SQL Server 2008+. SQLCLR is not an option.

I have a situation where I have data all stored in UTC. I need to do a comparison against that data by determining what a certain local time, let's say 8am, is in UTC. The timezone for the local time will vary on a row by row basis. (The timezone for each row is stored, so that's not an issue.) That certain local time has no date associated with it. It's always just "8am".

I have timezone data in the database, and this tells me the base UTC offset as well if the timezone follows daylight savings time.

But now I'm kind of stuck.

My problem is that in order to do a daylight savings time adjustment, I need to know if the current date/time in a particular timezone falls within certain ranges, but I can only convert to the appropriate local time to do that check if I know if it's daylight savings! In other words, how can I check to see if it's daylight savings unless I know whether a UTC offset is off due to daylight savings?

It's a chicken and egg problem.

It seems to me that the only solution is to be able to have a table that calculates daylight-savings aware offsets on a per-timezone basis.

Ideas?

Answer 1

You do have an ambiguity problem here, but it's not a chicken and egg issue.

The piece of information you are missing is, "what defines a day?" I know, it sounds crazy, but a "day" is not a universal concept. It's a local one.

For just a minute, put aside issues of time zones, DST and UTC. If I ask you, "How many hours are we apart from 8 AM right now?" You could give me two different answers. It's 7PM right now, so you might say "11 hours" - since that's how much time we are from 8 AM today. But I could also have said "13 hours" - since that's how much time we are from 8 AM tomorrow. Now in this very simplistic sample, you could disambiguate in one of two different ways. You might say "the last 8AM" or "the next 8AM". Or you might say "whichever happened today."

Now go back to the concept of UTC. What is a "UTC day?" Well, we know it's 24 hours, since UTC doesn't follow any daylight savings time. But saying that it runs "midnight to midnight UTC", isn't a very meaningful measure. Sure, there are some places that use this definition (for example, StackOverflow's stats engine). But for most people, we think of "today" in our own local time.

So I can't really say "whichever 8AM happened today". The only date measurement you have is a UTC date. You won't know which local date you should be looking at. Let's take a real example:

• I live in Phoenix, Arizona, so my time zone offset is UTC-7. We don't have DST here.
• It is currently June 14th 2013, 7 PM local time.
• So that's June 15th 2013, 2 AM UTC.

Now I record that time in the database, and later I ask:

• "How far away are we from 8 AM Arizona time?"
• With the information I have, I don't know if I should be looking for 8 AM on June 14th, or 8 AM on June 15th. Only the latter falls on the same UTC date, but I certainly could be interested in either one of them.

If you can decide in your business logic that you want the last time, or the next time, then you can resolve this. Simply convert the UTC datetime to the local time zone. Then roll forward or backward to the desired time. If your time zone has DST and you cross a transition date along the way, you can adjust for that.

You could also pick the nearest time of the two, but of course that all depends on your business logic.

Another approach would be to figure out which local today you are in, using the UTC time you are comparing. So in my example above, Arizona's local June 14th runs from June 13th 17:00 UTC to June 14th 17:00 UTC.

So to summarize, you wanted to know "Is 8 AM in DST surrounding this UTC datetime?", and you can't answer that without more information, either the date of the 8AM, or some logical relationship to follow, of which there are several options available. Pick a strategy that works for your needs.

UPDATE

You asked in comments:

How can I know if right now in UTC is in dst in X time zone so I can adjust accordingly?

This is where the datetimeoffset type can be helpful. You don't just want to track "is DST in effect", you want to track the precise offset for the target time zone, including any DST that might be in effect. The difference is subtle, but it comes down to tracking a full offset rather than just a boolean yes/no.

So, let's pretend I live in New York City. Checking this site, we know that EDT went into effect on March 10th 2013 at 2AM local time, and it will go back to EST on November 3rd 2013 at 2AM local time.

So we have the following:

UTC                     Local datetimeofffset              
2013-03-10T05:00:00Z    2013-03-10T00:00:00-05:00    
2013-03-10T06:00:00Z    2013-03-10T01:00:00-05:00    
2013-03-10T07:00:00Z    2013-03-10T03:00:00-04:00  <--- transition
2013-03-10T08:00:00Z    2013-03-10T04:00:00-04:00
...
2013-11-03T04:00:00Z    2013-11-03T00:00:00-04:00
2013-11-03T05:00:00Z    2013-11-03T01:00:00-04:00
2013-11-03T06:00:00Z    2013-11-03T01:00:00-05:00  <--- transition
2013-11-03T07:00:00Z    2013-11-03T02:00:00-05:00

Now notice that if you strip off the offset, you only have a one-way function. In other words, you can always determine the correct local time for the UTC time, but you can't go the other direction unless you know the offset during the fall-back transition (or unless you are willing to live with ambiguity).

So the algorithm for going from UTC to local time should be something like this:

1. Starting with the UTC datetime: 2013-11-03T05:30:00Z
2. Apply the standard offset (-5) 2013-11-03T00:30:00-05:00
3. Apply the daylight offset (-4) 2013-11-03T01:30:00-04:00
4. Which one is valid according to the time zone rules?
   • In this case, the daylight offset is valid.
   • Your time zone data should have this information.
   • If not, then you need to reconsider the source of your time zone tables.

Let's try it again with the other 1:30 time:

1. Starting with the UTC datetime: 2013-11-03T06:30:00Z
2. Apply the standard offset (-5) 2013-11-03T01:30:00-05:00
3. Apply the daylight offset (-4) 2013-11-03T02:30:00-04:00
4. Which one is valid according to the time zone rules?
   • In this case, the standard offset is valid.
   • How do we know? Because -4 is the daylight offset, and DST is supposed to be over at 2:00 local time. We have 2:30 local time associated with that offset, so only the standard one is valid in this time zone.

So can you convert from UTC to local? Yes. Always.

But you also said that the local value in the other column is just something like 8AM. So if it was 1:30AM, then certainly you would have an ambiguity during the fall-back transition. There is no way to resolve this, other than just picking one.

Sometimes, you might want to just pick one or the other, but sometimes you might want to error. And sometimes you might want to let your user pick which of the two they were interested in. It's not unheard of to see a dialog such as the following:

DAYLIGHT SAVING TIME
We're sorry, but there are two different instances of 1:30 AM on this day.
Which did you mean?

        [1:30 AM Eastern Daylight Time]     [1:30 AM Eastern Standard Time]

...those are buttons, if you couldn't tell. :)