Thread: Dates and daylight saving time

Dates and daylight saving time

From

Fduch the Pravking

Date:

31 January 2002, 11:30:11

I got the following problem in PostgreSQL 7.1.3.

When I need to get next day relative to another one,
I do the following query:
SELECT date(date ? + interval '1 day').

But on '2001-10-28', I get this:

test=> SELECT date(date '2001-10-28' + interval '1 day');
    date
------------
 2001-10-28
(1 row)

It might look VERY strange if we don't know that the date of '2001-10-28'
is the date of switching from daylight saving time to winter time,
and before the following query is executed:

test=> SELECT timestamp(date '2001-10-28' + interval '1 day');
       timestamp
------------------------
 2001-10-28 23:00:00+03
(1 row)

Why the addition of '1 day' to some date (or timestamp) is equal to
addition of exactly 24 hours (regardless of real length of this day),
while the addition of '1 month' to some date varies depending on month's length?
And why no separate '+' operator for 'date' and 'interval' types?

How to handle such situation?


--
Fduch M. Pravking

Re: Dates and daylight saving time

From

"Tom Pfau"

Date:

31 January 2002, 11:53:18

try 'set timezone to <tzname>' replacing <tzname> with your timezone
before your query.

template1=# set timezone to est;
SET VARIABLE
template1=# SELECT date(date '2001-10-28' + interval '1 day');
    date
------------
 2001-10-29
(1 row)

-----Original Message-----
From: Fduch the Pravking [mailto:fduch@antar.bryansk.ru]
Sent: Thursday, January 31, 2002 11:04 AM
To: pgsql-bugs@postgresql.org
Subject: [BUGS] Dates and daylight saving time


I got the following problem in PostgreSQL 7.1.3.

When I need to get next day relative to another one,
I do the following query:
SELECT date(date ? + interval '1 day').

But on '2001-10-28', I get this:

test=> SELECT date(date '2001-10-28' + interval '1 day');
    date
------------
 2001-10-28
(1 row)

It might look VERY strange if we don't know that the date of
'2001-10-28'
is the date of switching from daylight saving time to winter time,
and before the following query is executed:

test=> SELECT timestamp(date '2001-10-28' + interval '1 day');
       timestamp
------------------------
 2001-10-28 23:00:00+03
(1 row)

Why the addition of '1 day' to some date (or timestamp) is equal to
addition of exactly 24 hours (regardless of real length of this day),
while the addition of '1 month' to some date varies depending on month's
length?
And why no separate '+' operator for 'date' and 'interval' types?

How to handle such situation?


--
Fduch M. Pravking

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

Re: Dates and daylight saving time

From

Fduch the Pravking

Date:

31 January 2002, 12:36:48

On Thu, Jan 31, 2002 at 11:44:53AM -0500, Tom Pfau wrote:
> try 'set timezone to <tzname>' replacing <tzname> with your timezone
> before your query.
>
> template1=# set timezone to est;
> SET VARIABLE
> template1=# SELECT date(date '2001-10-28' + interval '1 day');
>     date
> ------------
>  2001-10-29
> (1 row)

Yes, it works!

But now postgres accepts input and returns output
in GMT, not local time like before! Is it a feature?

--
Fduch M. Pravking

Re: Dates and daylight saving time

From

Thomas Lockhart

Date:

31 January 2002, 13:16:55

...
> 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

Re: Dates and daylight saving time

From

Tom Lane

Date:

31 January 2002, 13:16:58

This was just discussed yesterday on pgsql-novice, see
http://archives.postgresql.org/pgsql-novice/2002-01/msg00177.php
http://archives.postgresql.org/pgsql-novice/2002-01/msg00178.php

            regards, tom lane

Re: Dates and daylight saving time

From

Tom Lane

Date:

31 January 2002, 14:05:28

Thomas Lockhart <lockhart@fourpalms.org> writes:
> In the long run, we should probably implement some exact date/interval
> arithmetic instead of relying on timestamp types in the intermediate
> calculations.

AFAIK type "date" solves his problem just fine, and there's no need to
mess with timestamps and intervals at all.  But in the long run it'd
be nice to handle this sort of situation more intuitively.

I have suggested more than once that type interval ought to have three
components not two: months, days, and seconds.  Just as a month is not
a fixed number of days, a day is not a fixed number of seconds.  Not
sure if we can get away with that when SQL92 believes otherwise, however.

            regards, tom lane