Skip site navigation (1) Skip section navigation (2)

BUG #2387: Incorrect sorting of timestamp with time zone

From: "" <christian(dot)koth(at)smiths-heimann(dot)com>
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #2387: Incorrect sorting of timestamp with time zone
Date: 2006-04-11 12:47:33
Message-ID: 200604111247.k3BClXU1098518@wwwmaster.postgresql.org (view raw or flat)
Thread:
Lists: pgsql-bugs
The following bug has been logged online:

Bug reference:      2387
Logged by:          
Email address:      christian(dot)koth(at)smiths-heimann(dot)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.

Responses

pgsql-bugs by date

Next:From: Tom LaneDate: 2006-04-11 13:55:02
Subject: Re: constraints & tableoid [pgsql8.1]
Previous:From: Michael FuhrDate: 2006-04-11 08:43:50
Subject: Re: constraints & tableoid [pgsql8.1]

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group