Thread: Date Conversion Bug

Date Conversion Bug

From
pgsql-bugs@postgresql.org
Date:
Ayal Leibowitz (ayal@modelity.com) reports a bug with a severity of 2
The lower the number the more severe it is.

Short Description
Date Conversion Bug

Long Description
Hi,

I encountered this bug in Postgres version 6.5 to 7.1 and on RedHat Linux versions 6.1 to 7.1.

It's similar or even identical to bugs #208 and #249, but I want to clarify a little more.

It looks like Postgres refers to the time zone when converting character string to date type. For example, when I type:
select date('1993-04-02') from xxx;
I get 1993-04-01 for each line in xxx.
My default time zone is GMT+2. If I type: set time zone 'gmt'; before the above query the result is as expected:
1993-04-02.

To my understanding, date shouldn't be influenced by the time zone, only the time and timestamp types. This is the
reasonwhy the newly version 7.1 "WITHOUT TIME ZONE" type qualifier does not relate to the date type. 

Like specified in #249, it happens only on the switch from normal to daylight saving time.

Ayal Leibowitz,
Modelity Technologies.

Sample Code
select date('1993-04-02') from xxx;

results with 1993-04-01 in psql.

No file was uploaded with this report

Re: Date Conversion Bug

From
Tom Lane
Date:
Ayal Leibowitz (ayal@modelity.com) writes:
> I encountered this bug in Postgres version 6.5 to 7.1 and on RedHat Linux versions 6.1 to 7.1.

Several of the developers have tried to reproduce this bug, with no
success.

> select date('1993-04-02') from xxx;
> I get 1993-04-01 for each line in xxx.
> My default time zone is GMT+2.

Exactly how do you set the time zone?  What does "SHOW TIMEZONE" show?
What do you get from
    select timestamp('1993-04-01');
    select timestamp('1993-04-02');
    select timestamp('1993-04-03');

> Like specified in #249, it happens only on the switch from normal to
> daylight saving time.

One reason I'm so interested in your timezone is that 1993-04-02 is not
a DST transition day in any timezone that I know about.  In Europe, DST
transitions happen in the last week of March, never in April, according
to the references I can find.  In the USA, we do have transitions in
April, but the transition day in 1993 was 04-04.

            regards, tom lane