OSX postgres 9.1.4 timestamp with time zone order by

Question

At OSX 10.8 on postresql server 9.1.4 is a unexpected behavior. Since on Ubuntu 12.04 it was correct.

To reproduce this issue use the the following postgres sql example database. Note: The timestamp values are selected in time zone 'Europe/Berlin' and the timestamps are around the DST end in 2012 which was the case of this test.

BEGIN;
DROP TABLE data;
CREATE TABLE data (
"id" int8 NOT NULL,
"timestampwithtimezone" timestamp(6) WITH TIME ZONE,
CONSTRAINT "data_pkey" PRIMARY KEY ("id")
);
COMMIT;

BEGIN; 
INSERT INTO data (id, timestampwithtimezone) VALUES (205,'2012-10-28 01:30:00+02');
INSERT INTO data (id, timestampwithtimezone) VALUES (204,'2012-10-28 02:00:00+02');
INSERT INTO data (id, timestampwithtimezone) VALUES (203,'2012-10-28 02:30:00+02');
INSERT INTO data (id, timestampwithtimezone) VALUES (202,'2012-10-28 02:59:59+02');
INSERT INTO data (id, timestampwithtimezone) VALUES (106,'2012-10-28 02:00:00+01');
INSERT INTO data (id, timestampwithtimezone) VALUES (107,'2012-10-28 02:30:00+01');
INSERT INTO data (id, timestampwithtimezone) VALUES (108,'2012-10-28 02:59:59+01');
INSERT INTO data (id, timestampwithtimezone) VALUES (109,'2012-10-28 03:00:00+01');
INSERT INTO data (id, timestampwithtimezone) VALUES (110,'2012-10-28 03:30:00+01');
COMMIT;

If I type the following SQL query which only looks for the last existent timestamp before a given timestamp:

SELECT id, timestampwithtimezone at time zone 'Europe/Berlin' as timestampwithtimezone
FROM data
WHERE 
timestampwithtimezone at time zone 'Europe/Berlin' 
< cast('2012-10-28T02:30:00.000+01' AS timestamp)
ORDER BY timestampwithtimezone DESC 
LIMIT 1;

my expected result was:

║ 106 ║ 2012-10-28 02:00:00 ║

Since I want the last timestamp before a given (2012-10-28T02:30:00.000+01) timestamp. But the result contains:

║ 204 ║ 2012-10-28 02:00:00 ║

It seems that postgresqls sorting is wrong in this case. Under Ubuntu I get the right answer which was id: 106 and NOT 204.

BUT:

If I set up this query: (Only difference is that I dropped the result set mapping to the time zone):

SELECT id, timestampwithtimezone
FROM data
WHERE timestampwithtimezone at time zone 'Europe/Berlin' 
< cast('2012-10-28T02:30:00.000+01' AS timestamp)
ORDER BY timestampwithtimezone DESC 
LIMIT 1;

The result set in OSX 10.8 is as expected as well as in Ubuntu 12.04:

║ 106 ║ 2012-10-28 02:00:00+01 ║

Why is there a difference between the OS versions? And the answer '204' to this query in this case is really wrong. But why there is a difference in the result set if I map the timestamp to a time zone? To my knowledge postgresql uses internally UTC to store timestamps. So the DST should be not a problem in the timeline. Why this happens?

Thanks in advance in the hope of a response.

sufleR sufleR · Accepted Answer · 2012-12-19T22:52:44

Previous answer was wrong.

Based on you data. Both answers are good.

In 2012-10-28 there was time change from Summer time to winter time (Here it is explained).

Look at hours in this result:

select id, 
  timestampwithtimezone, 
  timestampwithtimezone at time zone 'Europe/Berlin' as berlin  
from data 
order by berlin ;


 id  | timestampwithtimezone  |       berlin        
-----+------------------------+---------------------
 205 | 2012-10-28 01:30:00+02 | 2012-10-28 01:30:00
 204 | 2012-10-28 02:00:00+02 | 2012-10-28 02:00:00
 106 | 2012-10-28 02:00:00+01 | 2012-10-28 02:00:00
 203 | 2012-10-28 02:30:00+02 | 2012-10-28 02:30:00
 202 | 2012-10-28 02:59:59+02 | 2012-10-28 02:59:59

Records with id 204 and 106 have the same value in berlin column.

Read question and answers from here.

If you want to order by timestamptz do not use alias for column as the same name

SELECT 
  id, 
  timestampwithtimezone at time zone 'Europe/Berlin' as timestampwithtimezone_alias
FROM data
WHERE 
  timestampwithtimezone at time zone 'Europe/Berlin' 
  < cast('2012-10-28T02:30:00.000' AS timestamp)
ORDER BY timestampwithtimezone DESC 
LIMIT 1;

OSX postgres 9.1.4 timestamp with time zone order by

1 Answers