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

Karel Zak <zakkr@zf.jcu.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