Обсуждение: BUG #2387: Incorrect sorting of timestamp with time zone

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.

"" <christian.koth@smiths-heimann.com> writes:
> 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.

AFAICS, they are the same value, so they can be sorted in either order.
Which way they'll end up depends on undocumented properties of your
platform's qsort() routine.

            regards, tom lane