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 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Karel Zak | 2000-09-06 14:42:55 | Re: bug in date_part() function in 6.5.2, 7.0.2 |
Previous Message | Martin Neimeier | 2000-09-06 13:37:21 | Re: Error with union in sub-selects |