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

Re: bug in date_part() function in 6.5.2, 7.0.2

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Karel Zak <zakkr(at)zf(dot)jcu(dot)cz>
Cc: Thomas Lockhart <lockhart(at)alumni(dot)caltech(dot)edu>, analyst(at)sibinet(dot)ru, pgsql-bugs(at)postgresql(dot)org
Subject: Re: bug in date_part() function in 6.5.2, 7.0.2
Date: 2000-09-06 14:37:33
Message-ID: 14222.968251053@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-bugs
Karel Zak <zakkr(at)zf(dot)jcu(dot)cz> writes:
>  ....it's not date_part() bug, it's to_date() bug:

> test=# select to_date('26.03.2000','dd.mm.yyyy')::timestamp;
>         ?column?
> ------------------------
>  2000-03-25 23:00:00+01
>             ^^^
>            ! Bug !

> test=# select to_timestamp('26.03.2000','dd.mm.yyyy');
>       to_timestamp
> ------------------------
>  2000-03-26 00:00:00+01
>             ^^^
>           ! correct !

Looks like it's a DST-transition issue.  Here in EST5EDT, where this
year's EST->EDT transition occurred at 2000-04-02 02:00, I get

regression=# select to_date('26.03.2000','dd.mm.yyyy')::timestamp;
        ?column?
------------------------
 2000-03-26 00:00:00-05
(1 row)

regression=# select to_timestamp('26.03.2000','dd.mm.yyyy');
      to_timestamp
------------------------
 2000-03-26 00:00:00-05
(1 row)

regression=# select to_timestamp('02.04.2000','dd.mm.yyyy');
      to_timestamp
------------------------
 2000-04-02 00:00:00-05
(1 row)

regression=# select to_date('02.04.2000','dd.mm.yyyy')::timestamp;
        ?column?
------------------------
 2000-04-01 23:00:00-05
(1 row)

I presume 26 March was the transition date where you live?

>  But to_date() call to_timestamp() only:

> Datum
> to_date(PG_FUNCTION_ARGS)
> {
>         /* Quick hack: since our inputs are just like to_timestamp,
>          * hand over the whole input info struct...
>          */
>         return DirectFunctionCall1(timestamp_date, to_timestamp(fcinfo));
> }

>  Comments, some idea?

There's nothing wrong in the above code.  What's broken is the date-to-
timestamp type conversion for a DST transition date:

regression=# select to_date('02.04.2000','dd.mm.yyyy');
  to_date
------------
 2000-04-02
(1 row)

regression=# select '2000-04-02'::date::timestamp;
        ?column?
------------------------
 2000-04-01 23:00:00-05
(1 row)

regression=# select '2000-10-29'::date::timestamp;
        ?column?
------------------------
 2000-10-29 01:00:00-04
(1 row)

Looks to me like an off-by-one kind of problem in deciding which
timezone applies to midnight of a transition day.

			regards, tom lane

In response to

Responses

pgsql-bugs by date

Next:From: Karel ZakDate: 2000-09-06 14:42:55
Subject: Re: bug in date_part() function in 6.5.2, 7.0.2
Previous:From: Martin NeimeierDate: 2000-09-06 13:37:21
Subject: Re: Error with union in sub-selects

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