Dates, time zones,daylight saving time

[Harlan Messinger]

I live in the U.S. Eastern time zone (EST = GMT-5). An application I
wrote his hosted on a server in the Pacific time zone (PST = GMT-8). I'm
anticipating a time when I could have customers in yet other time zones,
and each of them may have users that are themselves in multiple time zones.

Even without going that far--even just serving my own users in my own
time zone--I've had to make an adjustment. I've got a web page that
shouldn't allow users to submit new data after a date and time that's
stored in the database, April 15, 2010, at 10:00 PM. So I have April
15,2010, 10:00 PM in the database. Then in my application (ASP.NET 3.5,
C#) I compare the current moment, DateTime.Now, to the value retrieved
from the database. Well, that's wrong, because DateTime.Now is what time
it is now *on the other side of the country*. So I have to add 3 hours
to DateTime.Now to get a valid comparison. If, as I project, I wind up
with other customers in yet other places, I have to come up with an
effective and maintainable way to handle dates and times. Are there are
any write-ups with good practices for this sort of situation, possibly
with specific reference to SQL Server and ASP.NET?

I'm also curious about how daylight saving time (DST, also known as
summer time) is handled. Suppose both I and my server are on Eastern
Daylight Time (GMT-4) and it's the day in autumn when DST ends at 3:00
(0700 GMT) in the morning and we switch back to Eastern Standard Time
(GMT-5). Say I have this table:

CREATE TABLE Foo (
id int NOT NULL PRIMARY KEY,
content varchar(100) NOT NULL,
when datetime DEFAULT (getdate())
)

At 0630 GMT I execute
INSERT INTO Foo (content) VALUES ('A')
At 0640 GMT I execute
INSERT INTO Foo (content) VALUES ('B')
At 0650 GMT I execute
INSERT INTO Foo (content) VALUES ('C')
At 0735 GMT I execute
INSERT Into Foo (content) VALUES ('D')
At 0745 GMT I execute
INSERT INTO Foo (content) VALUES ('E')
At 0755 GMT I execute
INSERT INTO Foo (content) VALUES ('F')

What is stored in the "when" column each time? What do I see if I execute

SELECT * FROM Foo ORDER BY when

Will the results be displayed in the order (A, B, C, D, E ,F)? In the
order (A, D, B, E, C, F)? Some other order? What date and time will be
displayed next to each letter? Will it be different if I run the same
SELECT query next summer, when daylight saving time is again in effect?

Where's a good treatment of best or common practices to deal with these
issues?

 

[Eric Isaacs]

I would suggest that you make a list of timezones, and associate one
timezone for each user. Maybe go as far as to have a location table
that has a many to one relationship with a timezone. Have a timezone
table that has a name and a value to add to GMT to get the local time
(GMT - 4) would store -4. But then that timezone table would need to
have another table that stores the offset based on the current month,
day, and year range. You would need to preload that table with the
dates and times that the times change based on the timezone. But
that's the extent of what your database should do. It just tracks the
name of their location (which corresponds to a timezone and DST range
of dates and offsets.) You store all dates in GMT in your database.
When you display those dates in your application (and this part would
probably be best handled in your application, though it is possible in
SQL Server.) You just add the current offset to the GMT time to
determine the date and time to display. If this is a web application,
the browser will report the timezone offset, which can also be
utilized to determine the local time, but that's the local time
today. If you're displaying a date in the future, that offset might
not be applicable because of DST in that area. The most of the state
of AZ doesn't participate in DST, so it's in Mountain Standard Time
all the time. Those are the things your timezone tables need to
capture, so that the proper offset can always be calculated based on
the date you're trying to display (or even store.)

-Eric Isaacs