Right Place, Wrong Time

November 13, 2007 6:37 PM

I have a set of dates/times in a MS SQL table that lack a timezone offset. I want to convert them to UTC, but I have to be mindful of Daylight Time for each of these dates (they're in the future). How can I convert them to UTC correctly?

The nuts and bolts are these:
We have a calendaring application with an MS SQL Server 2000 backend. When event dates get entered into the system, they're entered without a timezone code, e.g.:

2007-11-13 12:00:00.000

Each of these events is in the future. Some are in March or April. We observe DST.

I'm making changes to the data feed so that we can implement the hCalendar microformat on our calendars. The problem is the time zone. hCalendar calls for dates like:

2007-11-13T12:00:00-08:00

Originally, one of the engineers slapped the current time offset from UTC on there. Unfortunately, if you're looking at the calendar while we're on standard time, an event that will be in DST will not be offset correctly, being an hour earlier or later.

So, back to the drawing board. We decided to convert all the dates to UTC before putting them in the data feed for the microformat. But the time zone problem came right back.

Is there an easy way to convert these future dates to the correct UTC that's mindful of what the DST offset is in the future? There's no function in MS SQL 2000 that allows you to look up whether a date is PDT or PST, is there?

posted by dw to computers & internet (3 comments total)

Ok, so presumably you know what the timezone is for each of these datetimes, right? (Probably they're all one TZ, or you've associated to the datetime some data (location) that allows you to deduce the TZ).

Ok, so now you write the function you need. You'll write it as a view.

Now, I don't know what range of dates you're working with, so I'm just going to give you a dead simple one-off answer that lacks flexibility or elegance.

Assuming your dates are all after the last time DST ended and prior to the next time DST ends, we can partition the problem dates into two parts: those in Standard Time, and those in DST. Those in DST are those on or after 2008-03-09 2:00.

Ok, I'll assume your base table is called event_date with a datetime column d.

Since I don't know what TZ you're in, I'll assume it's UTC-5

So to find out UTC when not in DST, I'll add 5 hours; in DST I'll add 4 hours.

so:

create view event_date_utc as
select d, dateadd(hh, case when d < '2008-03-09 2:00' then 5 else 4 end, d) as d_UTC
from event_date;


Of course, that's not tested. You should spend some time testing it.

A better solution, which would work for any date, not just those prior to the end of 2008 DST is to look up the offset in a table of DST times, but I suspect that's beyond the scope of what you need (and certainly beyond the scope of what you're paying for ;) )
posted by orthogonality at 7:37 PM on November 13, 2007

I've recently been working with time zones in a Ruby/Rails web application. I'm sure this isn't the best or only way to do what you want, but it's the way I currently have in my head, so I'll share it:

There is a nice library for Ruby called TZInfo, which is built on the massive tz database, which contains all the rules about all the time zones around the world, including the specifics of their observance of Daylight Savings Time.

The nice thing about TZInfo is that it hides all the esoteric time zone rules from you; you just give it a date and some time zones to convert between, and it does the rest.

To accomplish your goal -- convert a bunch of Pacific Time Zone times, some of which occur during DST and some of which don't, to UTC -- you'd write a short Ruby script like this:

time_zone = TZInfo::Timezone.get('America/Los_Angeles')
for record in records
  record.time = time_zone.local_to_utc(record.time)
  record.save
end

Some of this is pseudocode: records represents a collection of rows from your database; time is the field that contains your local time; and save represents saving the updated time back to the database.

All of the time zone-related stuff, however, is working code; those are the actual methods you would call on the TZInfo library to do your conversions.

As it sounds like you've realized, simply converting all your dates to UTC, while a good idea, won't totally solve your problem. At display time, assuming you want to display a time to user in his local time, you need to be able to intelligently convert that time based on the user's local time zone and the time itself ("This user is in the Pacific Time Zone. This date is in December, so it falls during Pacific Standard Time, so we subtract 8 hours; but this other date is in July, so it falls during Pacific Daylight Time, so we subtract 7 hours.") TZInfo provides handy methods for doing all of this as well.

Anyway, if you're not using Ruby, the specifics here won't be helpful, but hopefully the general approach I've described will. Good luck!
posted by medpt at 9:00 PM on November 13, 2007

I'm not trying to convert the dates within in the DB. That would mean rewriting a lot of code (since the application itself is TZ-agnostic), and I'm not up for that.

What I'm trying to do is convert them to UTC so I can use them more effectively with the hCalendar microformat. So, take a date, convert it to the correct UTC.

What we've decided on is both solutions you've suggested. Take the date, determine whether it falls between the second Sunday of March, 2am and the first Sunday of November, 2am. If it does, add seven hours. If not, add eight.

Unfortunately, we're not using Ruby on Rails, so we'll have to create a DIY function in a SQL server stored proc. But it seems doable.

Thanks all.
posted by dw at 12:14 PM on November 14, 2007