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