PostgreSQL Timestamps and Time zones: Why am I reading different results than I wrote?

Question

I am trying to understand what is going in with timestamps and how postgreSql handles time zones and have been remarkable unsuccessful.

Here is and example script I created to learn:

drop table if exists timestampTest;
create table timestampTest(
  ts timestamp with time zone
);
insert into timestampTest (ts) values (to_timestamp(0) at time zone 'utc');
select
    to_timestamp(0) at time zone 'utc' INSERTED,
    ts at time zone 'utc' RETRIEVED_UTC,
    ts RETRIEVED_DEFAULT,
    extract(epoch from ts) RETRIEVED_TS_DEFAULT,
    extract(epoch from ts at time zone 'utc') RETRIEVED_TS_UTC,
    extract(epoch from to_timestamp(0) at time zone 'utc') INSERTED_TS
from timestampTest

When I run the script on my Laptop (PostgreSQL 9.3.5 on x86_64-unknown-linux-gnu, compiled by gcc (Ubuntu 4.8.2-19ubuntu1) 4.8.2, 64-bit) with german time it outputs:

      inserted       |    retrieved_utc    |   retrieved_default    | retrieved_ts_default | retrieved_ts_utc | inserted_ts 
---------------------+---------------------+------------------------+----------------------+------------------+-------------
 1970-01-01 00:00:00 | 1969-12-31 23:00:00 | 1970-01-01 00:00:00+01 |                -3600 |            -3600 |           0

Which stuns me, because I expected inserted and retrieved_utc to be the same because the only difference that I can see is that once the value was stored in a table and then loaded which should not change the value (should it?).

Additionally I expected all timestamp values to be the same (seconds since begin of unix epoch UTC). The value I least expected to differ from the others was inserted_ts. However it indeed differs. Why?

Then, more by accident than by purpose I executed the script on a server I am running (PostgreSQL 9.3.5 on x86_64-unknown-linux-gnu, compiled by gcc (Ubuntu 4.8.2-19ubuntu1) 4.8.2, 64-bit) which is also located in Germany and has its time set to German time. It gives the following result:

      inserted       |    retrieved_utc    |   retrieved_default    | retrieved_ts_default | retrieved_ts_utc | inserted_ts 
---------------------+---------------------+------------------------+----------------------+------------------+-------------
 1970-01-01 00:00:00 | 1969-12-31 23:00:00 | 1970-01-01 00:00:00+01 |                -3600 |            -7200 |       -3600

What actually happened here?

UPDATE: I just tried to set the system time to UTC on my Laptop. In this case I get the desired result:

      inserted       |    retrieved_utc    |   retrieved_default    | retrieved_ts_default | retrieved_ts_utc | inserted_ts 
---------------------+---------------------+------------------------+----------------------+------------------+-------------
 1970-01-01 00:00:00 | 1970-01-01 00:00:00 | 1970-01-01 01:00:00+01 |                    0 |                0 |           0

And YAY, that's a result I would actually expect and not be astounded by (well, I am astounded that retrieved_default is still displayed in German time, but at least it is still refering to the same point in time). But setting the time zone of my computers permanently to UTC is not an option. I need to figure out how to deal with these things no matter which time zone happens to be set on the server as system time zone.

Which client are you using? In my end it is all equal... and also on fiddle look here: http://sqlfiddle.com/#!1/30ecd/1 — Jorge Campos, Jan 05 '15 at 22:32
@JorgeCampos: I copy&pasted the two example from the command line psql client that ships with postgre. On my laptop I also used GUI pgAdmin which gives the same results. Too bad that there are no timestamp settings to be seen on sqlfiddle. Maybe their system time is set to UTC and maybe in this case the result is different? (Just tried setting my Laptops timezone to UTC in this case I get the same results as sqlfiddle) — yankee, Jan 05 '15 at 22:39
I find this question http://stackoverflow.com/questions/9202857/timezones-in-sql-date-vs-java-sql-date which is related (not entirely) with yours, maybe it is something to do with GMT and CMT. take a look — Jorge Campos, Jan 05 '15 at 22:57

score 1 · Accepted Answer · answered Jan 06 '15 at 00:47

1

When you insert using to_timestamp(0) at time zone 'UTC', the "at time zone" part removes the timezone from the return type, thus ending up with a "local", that is re-converted back to UTC when inserted. You do not need the "at time zone 'utc'" in your insert statement.

To better see what's happening, try the following:

set timezone='America/Chicago';
select to_timestamp(0), (to_timestamp(0) at time zone 'UTC') at time zone 'UTC'

answered Jan 06 '15 at 00:47

Joe Love

5,594
2
20
32

I was inclined to think something in long the lines "as if I did not try that $%!". But obviously I did not not. You are completely right, thanks a million! – yankee Jan 06 '15 at 11:28

PostgreSQL Timestamps and Time zones: Why am I reading different results than I wrote?

1 Answers1