UTC and Daylight savings scenario.

Question

I am using UTC to store data and time values in the DB. The values are converted to localtime on the client or per client timezone. I stepped on these scenarios from the MSDN article, where displaying the time from UTC seems to pose issues during daylight savings.

Someone living on the east coast of the United States types in a value like "Oct 26, 2003 01:10:00 AM".

1) On this particular morning due to daylight savings, at 2:00 AM, the local clock is reset to 1:00 AM, creating a 25-hour day. Since all values of clock time between 1:00 AM and 2:00 AM occur twice on that particular morning—at least in most of the United states and Canada, the computer really has no way to know which 1:10 AM was meant—the one that occurs prior to the switch, or the one that occurs 10 minutes after the daylight savings time switch.

2) Similarly, the problem happens in the springtime when, on a particular morning, there is no such time as 2:10 AM. The reason is that at 2:00 on that particular morning, the time on local clocks suddenly changes to 3:00 AM. The entire 2:00 hour never happens on this 23-hour day.

How have you handled the situation #1, when you might have had 4 transactions, two prior to switch and two after switch in daylight savings? How to display the time to the user for the transactions, as the last two transaction could show up earlier time than the first two transactions due to the shift.? Sometimes, it could prove illogical for eg: in a mail chain.

ADDED:

To add more info about the context, the RIA apps such as Silverlight/Flash run on client(or any client app talking to server via Webservice) allow user to select time of delivery or schedule with the pc local time.

If i could check a given input time for invalid time, i could probably alert the user. Also, for travelers, the timezone needs to be found at the point of time and not based on user selection as they could be moving between zones and saving their timezone in the user profile won't help.

Some C# test samples for evaluating the input time:
//2:30 am CT to UTC --> 8:30 am
DateTime dt = new DateTime(2009, 03, 08, 2, 30, 00, DateTimeKind.Local);

//8:30 am UTC to CT --> 3:30 am.. which is as expected
DateTime dt1 = new DateTime(2009, 03, 08, 8, 30, 00, DateTimeKind.Utc);

//check for daylight saving time returns false.. ??
TimeZoneInfo.Local.IsDaylightSavingTime(dt);

//check for daylight saving time returns true
TimeZoneInfo.Local.IsInvalidTime(dt);

Answer 1

Those scenarios are cases advocating for the use of DST. It doesn't matter what you display as long as you store and sort values in UTC. That is, if you use UTC properly, the problems presented in those scenarios are solved.

Yes, it would be confusing to see records like this: 12:30, 1:20, 1:10, 3:30 but if that's how they are ordered according to UTC (what really happened), I think that's the right way to do it.

SO avoids this problem altogether by recording everything in UTC and then displaying it all in UTC or relative times (like "17 mins ago...").

---

If you're referring to user supplied dates/times as suggested in the comments, I have some bad news for you: it sucks. I think the best, most obvious solution is to pick a rule and run with it. If you really do need to handle it perfectly, your UI will need to be expanded to pedantically handle this edge case that occurs a mere 1 hour each year and then only to transactions created not-in-real-time (because if they were real-time, you'd know the DST equivalent).

Answer 2

How to display the time to the user for the transactions, as the last two transaction could show up earlier time than the first two transactions due to the shift.? Sometimes, it could prove illogical for eg: in a mail chain.

Sort them by UTC, and display them in local time.

So the user might see a list like this:

01:10:00
01:50:00
01:05:00
01:20:00

Either that, or show and sort them by UTC.

Answer 3

If you have someone entering the data by hand, good luck unless their entering it in UTC time. There isn't really a "right" way to handle that. If you're dealing with dates that aren't user entered, like the time a transaction took place, just store those all as UTC and life is good. :)

Answer 4

SQL Server has a new type, 'datetimeoffset' which handles this well, since you can have the same time with different offsets. For my SQL Server 2005 DB I use a string literal of that type, nvarchar(25) of form "YYYY-MM-DD hh:mm:ss-hh:mm".

For this I've created routines to convert these strings to the correct times.

Answer 5

There are two parts to your question in general:

1. Receiving user inputs
2. Displaying data to users

The two ought to be handled similarly, but in some ways have somewhat separate workarounds. As for 1, the simplest option is to find out if your business really requires an unambiguous way to specify times in that particular hour. A lot of applications simply ignore it (when was the last time you used a date-selector that had a provision for it?) and simply assume any consistent guessing algorithm will be sufficient. You ought to provide a safeguard (i.e. throw an error) if the non-existent hour is entered.

As for 2, the skipped hour doesn't matter, since your database is in UTC. The repeat hour could be confusing, especially in a trail of timestamps. If it's worth it, consider formatting your date string with a timezone identifier that includes a reference to the daylight savings offset. Most old style, i.e. non-Olson, timezone names include this (EST vs EDT, GMT vs BST, etc.). This will be sufficient for disambiguation in a pinch. That's probably all you need, since this boundary case might not be worth mucking up the display too much. If you need a little more output, you can also format the timestamp with the UTC offset, which will make the changeover very unambiguous in a trail of timestamps.

Answer 6

You need to store the time offset.

Currently the time on the east coast is (roundtrip format)

2009-08-11T13:22:13.8493713-04:00

Even if the east coast is considered to be at -5, during daylight saving time, the time will be at -4.

On October 26th, at 01:10 am, the time will be

2009-10-26T1:10:00.0000000-04:00

but when the clock goes beyond 02:00 and that we switch back to normal time, your time will be

2009-10-26T1:10:00.0000000-05:00

In order to handle the offset, .NET, starting from 2.0sp1, offers the type DateTimeOffset. SqlServer 2008 also offers the data type DateTimeOffset that will help you store that value. If you are not using SqlServer 2008, you could store the date as a string using the roundtrip format:

DateTime.Now.ToString("o")

Answer 7

Why not just ask the user to disambiguate?