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
