Thread: BUG #1787: Timestamp issue for moment when clock moved to DST

The following bug has been logged online:

Bug reference:      1787
Logged by:          Lumir Vanek
Email address:      vanek@idea-envi.cz
PostgreSQL version: 8.0
Operating system:   Windows XP Czech SP2
Description:        Timestamp issue for moment when clock moved to DST
Details:

I have simple database table, where is TIMESTAMP column. There is *one row
per hour* in this table.

Run this script in pgAdmin and compare inserted *HOUR* values and compare
with returned ones by SELECT.

-- BEGIN
CREATE TABLE test_date (start_time timestamp (6) NOT NULL);

INSERT INTO test_date VALUES(to_timestamp('26.3.2000 01:00:00', 'dd.mm.YYYY
hh24:mi:ss'));
INSERT INTO test_date VALUES(to_timestamp('26.3.2000 02:00:00', 'dd.mm.YYYY
hh24:mi:ss'));
INSERT INTO test_date VALUES(to_timestamp('26.3.2000 03:00:00', 'dd.mm.YYYY
hh24:mi:ss'));
INSERT INTO test_date VALUES(to_timestamp('26.3.2000 04:00:00', 'dd.mm.YYYY
hh24:mi:ss'));

select * from test_date;
--END

The result depends from your OS clock settings, try UTC, CET = UTC+1, or CET
with automaticaly move to DST.

As you may see, inserted HOUR value is different from selected in one row.

Wrong hour value is for moment, when time is moving to Daylight saving time.
This moment is Sunday, 26.3 02:00 for year 2000, here in central Europe.

Lumir Vanek

"Lumir Vanek" <vanek@idea-envi.cz> writes:
> CREATE TABLE test_date (start_time timestamp (6) NOT NULL);

> INSERT INTO test_date VALUES(to_timestamp('26.3.2000 01:00:00', 'dd.mm.YYYY
> hh24:mi:ss'));

to_timestamp produces a timestamp with time zone.  Rotating that to a
timestamp without time zone is going to have strange behaviors at DST
boundaries.  You probably really want to be storing timestamp with tz
anyway, given that you think the timestamps represent real time instants
;-)

Alternatively, why bother with to_timestamp at all?  Given an
appropriate DateStyle setting, the native timestamp input data converter
is going to do just fine with this data format.

            regards, tom lane