The following bug has been logged online:
Bug reference: 2387
Logged by:
Email address: christian.koth@smiths-heimann.com
PostgreSQL version: 8.1.3
Operating system: Linux 2.4.22
Description: Incorrect sorting of timestamp with time zone
Details:
When sorting by "timestamp with time zone" columns the daylight-saving time
is not interpreted correctly. I have inserted equal timestamps to a table
named timetest with two columns:
Column | Type
----------------------+-----------------------------
time_stamp | timestamp without time zone
time_stamp_with_zone | timestamp with time zone
INSERT INTO timetest VALUES ('2006-09-23 22:01:00', '2006-09-23 22:01:00' at
time zone 'TST-2TDT,M3.5.0/0,M9.5.0/1');
(Where TST and TDT are freely choosen abbreviations as explained in
PostgreSQL 8.1.3 Documentation - Appendix B. Date/Time Support)
My local timezone setting is UTC. I also inserted timestamps in the time of
daylight saving switching. Now when I use the query:
SELECT time_stamp, time_stamp_with_zone from timetest order by
time_stamp_with_zone;
I get the following result:
time_stamp | time_stamp_with_zone
---------------------+------------------------
2006-09-23 20:01:00 | 2006-09-23 23:01:00+00
2006-09-23 22:01:00 | 2006-09-24 00:01:00+00
2006-09-23 21:01:00 | 2006-09-24 00:01:00+00
2006-09-23 23:01:00 | 2006-09-24 01:01:00+00
2006-09-24 00:01:00 | 2006-09-24 02:01:00+00
As one can see lines 2 and 3 are in the wrong order.
Since time_stamp_with_zone is internally saved as UTC it should be possible
to sort the output corresponding to the underlying UTC timestamp correctly.