Dates, time zones,daylight saving time

Discussion in 'ASP .Net' started by Harlan Messinger, Apr 15, 2010.

  1. 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?
     
    Harlan Messinger, Apr 15, 2010
    #1
    1. Advertising

  2. Harlan Messinger

    Eric Isaacs Guest

    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
     
    Eric Isaacs, Apr 16, 2010
    #2
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. David Lozzi

    Dates dates dates dates... SQL and ASP.NET

    David Lozzi, Sep 29, 2005, in forum: ASP .Net
    Replies:
    1
    Views:
    881
    Rob Schieber
    Sep 30, 2005
  2. Pranav Kantawala
    Replies:
    5
    Views:
    758
    P.Hill
    Feb 25, 2004
  3. Filip Lyncker
    Replies:
    3
    Views:
    760
    Karl Heinz Buchegger
    Apr 5, 2005
  4. Paul J. Lucas

    Parsing dates and daylight savings time

    Paul J. Lucas, Jan 15, 2009, in forum: Java
    Replies:
    6
    Views:
    2,141
    Paul J. Lucas
    Jan 16, 2009
  5. PW

    Dates! Dates! Dates!

    PW, Aug 7, 2004, in forum: ASP General
    Replies:
    4
    Views:
    209
    Mark Schupp
    Aug 9, 2004
Loading...

Share This Page