Daylight saving changes affecting UTC conversion

tags:

.net
c#

views:

1218

answers:

+6 Q:

Daylight saving changes affecting UTC conversion

Basically I'm converting local dates stored in the database into UTC. But I've read somewhere that daylight saving rules have changed in 2007. So does the Date.ToUniversalTime() function still work correctly. Basically the dates before 2007(when the new rules came into effect) would be converted correctly but the dates after that would be not. Am I right here? Or would .Net take care of the conversion internally i.e. depending upon the different daylight saving rules?

EDIT: Dates are stored in DB as local times. I am converting it into UTC. So a date such as '9 March 2005' should be converted using day light rules of 2005 instead of today's rules. The rules changed in the US in 2007. So the date is coming out wrong by one hour.

I would expect ToUniversalTime() to take that into account. Have you tried and checked the result with dates from before and after the DST change?

EDIT

If you know the timezone offset of all the dates in your DB, I definitively recommend you to convert them to UTC at the table level. You get rid of a whole lot of headaches that way. Converting to local time for display purposes is easier.

Tomalak 2008-10-21 13:18:13

Yes the dates before 2007 are coming out incorrectly for 2 months because .Net is following the day light saving rules of today.

Daud 2008-10-21 13:21:37

+3 A:

It will depend on which version of .NET you're using and possibly which version of Windows you're using. .NET 3.5 has the TimeZoneInfo class which includes historical changes etc - before then, the support was far more patchy, unfortunately.

Jon Skeet 2008-10-21 13:18:41

How could this work? TimeZoneInfo would need to know when the value was added to the db in order to know which rules to apply

Isak Savo 2008-10-21 13:24:08

Windows started supporting a rich timezone model (with historical changes) relatively recently - possibly with 2K3 or maybe even Vista and then service packs to older OSes. The .NET support used to follow the old model (one set of rules, perpetually applied). TimeZoneInfo follows the new model.

Jon Skeet 2008-10-21 13:28:17

Yeah, but assume I have this date in the db: "2005-03-10 03:30" (expressed in "localtime") How can TimeZoneInfo convert this date to UTC time without knowing which DST rules were used to save it in the first place?

Isak Savo 2008-10-21 13:29:55

It would have to assume that the DST rules were correct at the time of insertion. There are some ambiguities (local times which occur twice etc) but it's manageable. However, the OP stated that the database contained UTC values.

Jon Skeet 2008-10-21 13:31:09

@Isak: It wouldn't need to know when the value was added to the database. Only if the value itself is that old, and since that's what the value _is_, there shouldn't be a problem.

Joel Coehoorn 2008-10-21 13:31:16

@jon: I think you missed my point. Are there guarantees that the value in any given db row is the same as the time it was added. I mean, the value "2005-03-10.." could have been inserted to the db yesterday using yesterday's rules for all I know :)

Isak Savo 2008-10-21 13:37:31

No the database contains local time values. NOT UTC. I'm converting them to UTC by using ToUniversalTime() method. The problem is that for a date like '9 march 2005' it should be converted to UTC using daylight rules of 2005. But its using the rules of today instead. So dates are coming out wrong.

Daud 2008-10-21 13:38:28

@Xardas: Ah yes. Apparently I can't read :(

Jon Skeet 2008-10-21 13:41:27

@Isak: Yes, I see what you mean now. It's basically a case of "can we trust that the data has ever been accurate."

Jon Skeet 2008-10-21 13:42:05

@jon: well in a sense yes. But there's nothing wrong with doing "INSERT INTO mytable VALUES('Moon Landing', '1969-06-29 12:34');" but it would still raise the question - should DST rules from 1969 (maybe) be used, or some other rules (maybe). Depends on when it was inserted

Isak Savo 2008-10-21 13:48:13

Ok, the last clairification by Xardas makes it clear. My concern about 'when it was inserted' shouldn't apply to this case.

Isak Savo 2008-10-21 13:50:33

+1 A:

It depends on how the information is stored in the database.

Hopefully, the data in the db contains the UTC offset, and if so, any changes to daylight saving rules will be irrelevant.

If the UTC offset isn't known then it is virtually impossible to know how to convert it to UTC. For example, if the time is stored as an integer with no metadata then the system would have to know when it was added to the db to be able to figure out the corresponding UTC timestamp.

Isak Savo 2008-10-21 13:22:48

The time is stored as local time in DB. Needs to be converted to UTC obeying the rules of day light saving at that time.

Daud 2008-10-21 13:24:38

Changes to daylight savings rules won't be irrelevant - if you're converting to local time for display purposes, and you're displaying old dates/times, they'll be wrong :(

Jon Skeet 2008-10-21 13:24:59

@Jon: ah, you're meaning the answer to the question "Was this date during DST period or not?"? If so, sure the rules are relevant. But not for the conversion to UTC (since the actual datetime in the db contains the real offset)

Isak Savo 2008-10-21 13:27:14

Ah, I see what you mean by "the UTC offset" now - I thought you meant "the UTC value".

Jon Skeet 2008-10-21 13:30:04

Daylight savings time might be one of the top 10 worst ideas they've ever had. I can't think of a single problem this concept solves, but a whole bunch of problems it creates.

Tomalak 2008-10-21 13:33:13

@Tomalak: hehe, that's only because you think of implementation problems caused by it. Most people don't care about that and the extra hour of sunlight during summer clearly outweight this :)

Isak Savo 2008-10-21 13:40:23

Thats why it is wrong to ask "the most people" on any given problem. :-DIn Germany (same elsewhere I guess), trains are stopped in the middle of the night for one hour just so the schedule does not blow. WTF?!

Tomalak 2008-10-21 13:53:00

@Isak: DST doesn't magically create sunlight. There are the same number of hours of daylight (no "extra hour") regardless of what the clock says.

Dave Sherohman 2008-10-21 13:58:30

@Dave: you are of course absolutely correct. :) Although in the icy north where I live, the morning hour you loose is when most people sleep (~3am) so in practice it's an extra hour of sunlight.

Isak Savo 2008-10-21 15:24:50

+1 A:

i hate to say it, but you are screwed. Bite the bullet and change the dates in the database to UTC before the problem gets any worse. Your code will become a nightmare of special-case date-math in no time flat if you continue to try to store local times in the database.

compromise: store both local time and UTC time in separate columns; at least then you'll have a reference

see this post for more reasons never to store db times in local time

Steven A. Lowe 2008-10-21 14:20:33

See if http://geekswithblogs.net/ewright/archive/2004/09/14/11180.aspx helps you at all.

jasoncrider 2009-08-13 14:05:18

ansaurus

tags:

views:

answers:

Daylight saving changes affecting UTC conversion

related questions