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

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