Storing Date/Time with Daylight savings

  • Thread starter Thread starter Lourenço Teodoro
  • Start date Start date
L

Lourenço Teodoro

Dear All,

I am developing an application that will save historical data; The table is
very simple, it will have a Time Stamp column and other columns to save real
values. The application should be able to work with several different
databases. My problem now is to make a decision about the date/time to be
saved in the Time Stamp; I can use either System Time (GMT) or Local Time;
if I choose the first one, the users will get confused when they try to open
the tables using a GUI other than mine. On the other hand, if I save using
Local Time, I can have problems with Daylight saving. Does anyone have any
idea about how to avoid problems with daylight saving other than saving the
System Time?

Note: I thought about using an additional field (maybe save both, System
Time and Local Time), but I think that you experts should have a better
solution.

Thank you in advance,
Lourenço.
 
Lourenço,

To add to the problems you've already described, here are a couple of
others:

1. The users won't necessarily all reside in the same time zone as the
server.
2. If one is accepting "promises" in local times (e.g.: what time will a
movie showing begin?), the UTC offset won't necessarily be known at the time
the promise is made.

#2 is only a concern if you need to accomodate "non-standard" time zones
(particularly those where the daylight savings time boundary dates are not
fixed), but since you haven't mentioned much about the nature of your
application, I'll assume it's a possibility.

Given #1, you really should be storing the UTC offset along with the target
date/time value. This would narrow down your choices for each date/time
value to one of the following column pairs, either of which would allow you
to determine both the local and UTC values for any given stored value pair:

UTC date/time
UTC offset

OR

local date/time
UTC offset


If you need accomodate future times in non-standard time zones, you'll might
want to choose the second of the above pairs, with the UTC offset remaining
null until the time the promise is kept. However, you might also want to
consider storing the UTC value + offset pair for all events that have
already occurred since it'll probably offer better overall querying
performance if time-based reporting is used by client applications.

HTH,
Nicole
 
Back
Top