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

Re: Dates and daylight saving time

From: "Tom Pfau" <T(dot)Pfau(at)emCrit(dot)com>
To: "Fduch the Pravking" <fduch(at)antar(dot)bryansk(dot)ru>, <pgsql-bugs(at)postgresql(dot)org>
Subject: Re: Dates and daylight saving time
Date: 2002-01-31 16:44:53
Message-ID: 5C47691674725C47B02996F02C0D362107B5E3@exchange.rane.net (view raw or flat)
Thread:
Lists: pgsql-bugs
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(at)antar(dot)bryansk(dot)ru]
Sent: Thursday, January 31, 2002 11:04 AM
To: pgsql-bugs(at)postgresql(dot)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(at)postgresql(dot)org so that your
message can get through to the mailing list cleanly

Responses

pgsql-bugs by date

Next:From: Tom LaneDate: 2002-01-31 16:55:17
Subject: Re: New to Postgresql - Backend timeout /JDBC
Previous:From: Fduch the PravkingDate: 2002-01-31 16:03:54
Subject: Dates and daylight saving time

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