Re: Best practices: Handling Daylight-saving time - Mailing list pgsql-general

On Fri, Mar 11, 2005 at 03:25:28PM +0100, Egy?d Csaba wrote:
> Hi All,
> I'd like to ask your opininon about how to handle DST on an 7/24 system.
> Where should it be handled: on the server side or on the client side? And
> how could I (at all could I???) make it transparent?

As others have mentioned, store timestamps on the server in UTC, and
translate to/from local time on the client side if desired.  Postgres
can do this for you in the query; just look in the docs to see how.

I have personally encountered situations where that is not quite
adequate, however, because the data elements may originate in
different time zones, and it may be necessary to display in the
original time zone instead of (or in addition to) the local time zone
of the client.  (Weather data, for example, should generally be
displayed using the time zone of the source of the data.)  In this
case, you must store some representation of the source time zone in a
separate field, in addition to the UTC timestamp.  You can then use
both fields together to retrieve the correct original local time.
(Make sure to note in that time zone field whether or not DST is
observed in that locale.)  Exactly how to do this depends on your
application.

> Or we must bow to the fact that twice a year there are two unusable hours?
> If it cannot be solved technically, than it is acceptable, but if there is a
> chance to do it, I'd like to try it.

When timestamps are stored in UTC, the missing (when skipping forward)
and duplicate (when setting back) hours are only an issue in the user
interface, for entry and display of the times.  The missing hours are
generally easier to deal with, since the only thing affected is the
calculation of durations.  If your interface displays elapsed time,
make sure you take this into account.  (The easy way is to do the
duration calculations in the database, using UTC.)  Duplicate times
are more difficult: when displaying, you need to indicate whether DST
was in effect or not (i.e., was it the first 02:30 or the second?).
If times are to be entered manually in local time, the interface needs
to notice when an ambiguous time has been entered and ask the user to
disambiguate somehow.  This is a hell of a lot of trouble to go to for
something that will only come up very rarely or never in most
applications, but you have to do it if you want to get it right.

> Our system stores 200-1000 measured data per minute, comming from
> substations. The substations clock is synchronized periodically as well.
> When the DST is switched there is 1 hour of data missing (or overlapped).
> Certainly the client machines are autmatically adjusted for the DST.

If you have control over the production of data on these client
machines, just make sure it is produced in UTC, and the issue goes
away.  Otherwise, you can convert their local time back to UTC for
storage in the database, but then you have the duplicate hour
ambiguity to deal with.  If you know the data will be coming in
sequentially and/or in near real-time, you can probably figure it out
with a little extra logic in the app that loads the data into the DB.

Randall