Skip site navigation (1) Skip section navigation (2)

Re: Dates and daylight saving time

From: Thomas Lockhart <lockhart(at)fourpalms(dot)org>
To: Fduch the Pravking <fduch(at)antar(dot)bryansk(dot)ru>
Cc: Tom Pfau <T(dot)Pfau(at)emCrit(dot)com>, pgsql-bugs(at)postgresql(dot)org
Subject: Re: Dates and daylight saving time
Date: 2002-01-31 18:06:34
Message-ID: 3C5987AA.FC7AC7BA@fourpalms.org (view raw or flat)
Thread:
Lists: pgsql-bugs
...
> Yes, it works!
> But now postgres accepts input and returns output
> in GMT, not local time like before! Is it a feature?

This strategy will not work in general unless you *do* set the time zone
to GMT (if it works at one boundary, say in the fall, then it will fail
at the other boundary in the spring). 

It is likely that you set the time zone to one unrecognized by your
system (maybe a typo?), so it reverts to GMT. And with GMT you do not
have to worry about daylight savings time or offsets between dates and
times of day in different time zones.

But that is a workaround for the fundamental problem that you want to
solve, which is to get exact *qualitative* date calculations around DST
boundaries.

In the long run, we should probably implement some exact date/interval
arithmetic instead of relying on timestamp types in the intermediate
calculations.

In the meantime you can set time zones or, if you have a fixed query
with date in and date out, and intervals which are multiples of a day,
then you can simply add 12 hours in the query to get the rounding you
expect:

  cast((date '2001-10-28' + interval '1 day' + interval '12 hours') as
date)

                  - Thomas

In response to

Responses

pgsql-bugs by date

Next:From: Tom LaneDate: 2002-01-31 18:48:10
Subject: Re: Dates and daylight saving time
Previous:From: Tom LaneDate: 2002-01-31 18:00:31
Subject: Re: Dates and daylight saving time

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group