Converting Between Timezones in Postgres

Question

I am trying to understand the timestamps and timezones in Postgre. I think I got it, until I red this article.
Focus on the "Converting Between Timezones" part. It has two examples.

(Consider the default timezone configuration to be UTC.)

Example 1

db=# SELECT timezone('US/Pacific', '2016-01-01 00:00'); outputs 2015-12-31 16:00:00

According to the article and what I understand, because the '2016-01-01 00:00' part of the timezone function is just a string, it is silently converted to the default UTC. So from '2016-01-01 00:00' UTC it is then converted to US/Pacific as asked by the timezone function, that is 2015-12-31 16:00:00.

Example 2

db=# SELECT timezone('US/Pacific', '2016-01-01 00:00'::timestamp); outputs 2016-01-01 08:00:00+00

Excuse me, I dont see why and the explanation there does not help. Ok, the '2016-01-01 00:00'::timestamp part of the timezone function is no longer a string, but an actual timestamp. In what timezone? If it is UTC, the output would have to be the same as the Example 1. So it is automatically converted to US/Pacific? Then the output is in UTC? But why? I asked for a US/Pacific in my timezone not a UTC.

Please explain how the timezone behaves when gets a timestamp and gets asked to transform it. Thank you.

Answer 1

Let me explain the two examples:

In both we assume a timezone UTC (i.e. SET timezone TO UTC).

db=# SELECT timezone('US/Pacific', '2016-01-01 00:00');
      timezone
---------------------
 2015-12-31 16:00:00
(1 row)

This is equivalent to SELECT timezone('US/Pacific', '2016-01-01 00:00'::timestamptz), i.e. Postgres implicitly converted the string to a timestamptz.

We know that the timezone function converts back and forth between timestamp and timestamptz:

Since we are giving it a timestamptz as input, it'll output a timestamp. In other words, it is converting the absolute point in time 2016-01-01 00:00Z to a wall time in US/Pacific, i.e. what the clock in Los Angeles showed at that absolute point in time.

In example 2 we are doing the opposite, namely taking a timestamp and converting it to a timestamptz. In other words, we are asking: what was the absolute point in time when the clock in Los Angeles showed 2016-01-01 00:00?

You mention:

Ok, the '2016-01-01 00:00'::timestamp part of the timezone function is no longer a string, but an actual timestamp. In what timezone?

'2016-01-01 00:00'::timestamp is a timestamp, i.e. a wall time. It doesn't have a notion of timezone.

I think you might not have fully understood the difference between timestamp and timestamptz, which is key here. Just think of them as wall time, i.e. the time that showed somewhere in the world on a clock hanging on the wall, and absolute time, i.e. the absolute time in our universe.

The examples you make in your own answer are not quite accurate.

SELECT ts FROM  (VALUES
(timestamptz '2012-03-05 17:00:00+0') -- outputs 2012-03-05 17:00:00+00 --1
,(timestamptz '2012-03-05 18:00:00+1') -- outputs 2012-03-05 17:00:00+00 --2
,(timestamp   '2012-03-05 18:00:00+1') -- outputs 2012-03-05 18:00:00+00 --3
,(timestamp   '2012-03-05 11:00:00'  AT TIME ZONE '+6') -- outputs 2012-03-05 17:00:00+00 --4
,(timestamp   '2012-03-05 17:00:00'  AT TIME ZONE 'UTC') -- outputs 2012-03-05 17:00:00+00 --5
,(timestamp   '2012-03-05 17:00:00'::timestamp) -- outputs 2012-03-05 17:00:00+00 --6
,(timestamp   '2012-03-05 17:00:00'::timestamptz) -- outputs 2012-03-05 17:00:00+00 --7
    ) t(ts);

The problem with your example is that you're constructing one data set with a single column. Since a column can only have one type, each row (or single value in this case) is being converted to the same type, namely timestamptz, even though some values were calculated as timestamp (e.g. value 3). Thus, you have an additional implicit conversion here.

Let's split the example into separate queries and see what is going on:

Example 1

db=# SELECT timestamptz '2012-03-05 17:00:00+0';
      timestamptz
------------------------
 2012-03-05 17:00:00+00

As you might already know, timestamptz '2012-03-05 17:00:00+0' and '2012-03-05 17:00:00+0'::timestamptz are equivalent (I prefer the latter). Thus, just to use the same syntax as in the article, I'll rewrite:

db=# SELECT '2012-03-05 17:00:00+0'::timestamptz;
      timestamptz
------------------------
 2012-03-05 17:00:00+00

Now, what's going on here? Well, less than in your original explanation. The string is simply parsed as a timestamptz. When the result gets printed, it uses the currently set timezone config to convert it back to a human readable representation of the underlying data structure, i.e. 2012-03-05 17:00:00+00.

Let's change the timezone config and see what happens:

db=# SET timezone TO 'Europe/Berlin';
SET
db=# SELECT '2012-03-05 17:00:00+0'::timestamptz;
      timestamptz
------------------------
 2012-03-05 18:00:00+01

The only thing that changed is how the timestamptz gets printed on screen, namely using the Europe/Berlin timezone.

Example 2

db=# SELECT timestamptz '2012-03-05 18:00:00+1';
      timestamptz
------------------------
 2012-03-05 17:00:00+00
(1 row)

Again, just parsing the date.

Example 3

db=# SELECT timestamp '2012-03-05 18:00:00+1';
      timestamp
---------------------
 2012-03-05 18:00:00
(1 row)

This is the same as '2012-03-05 18:00:00+1'::timestamp. What happens here is that the timezone offset is simply ignored because you're asking for a timestamp.

Example 4

db=# SELECT timestamp '2012-03-05 11:00:00' AT TIME ZONE '+6';
        timezone
------------------------
 2012-03-05 17:00:00+00
(1 row)

Let's rewrite to be simpler:

db=# SELECT timezone('+6', '2012-03-05 11:00:00'::timestamp);
        timezone
------------------------
 2012-03-05 17:00:00+00
(1 row)

This is asking: what was the absolute time when the clock on the wall in the timezone with an offset of +6 hours was showing 2012-03-05 11:00:00?

Example 5

db=# SELECT timestamp '2012-03-05 17:00:00' AT TIME ZONE 'UTC';
        timezone
------------------------
 2012-03-05 17:00:00+00
(1 row)

Let's rewrite:

db=# SELECT timezone('UTC', '2012-03-05 17:00:00'::timestamp);
        timezone
------------------------
 2012-03-05 17:00:00+00
(1 row)

This is asking: what was the absolute time when the clock on the wall in the timezone UTC was showing 2012-03-05 17:00:00?

Example 6

db=# SELECT timestamp '2012-03-05 17:00:00'::timestamp;
      timestamp
---------------------
 2012-03-05 17:00:00
(1 row)

Here you're casting twice to timestamp, which makes no difference. Let's simplify:

db=# SELECT '2012-03-05 17:00:00'::timestamp;
      timestamp
---------------------
 2012-03-05 17:00:00
(1 row)

That's clear I think.

Example 7

db=# SELECT timestamp '2012-03-05 17:00:00'::timestamptz;
      timestamptz
------------------------
 2012-03-05 17:00:00+00
(1 row)

Let's rewrite:

db=# SELECT ('2012-03-05 17:00:00'::timestamp)::timestamptz;
      timestamptz
------------------------
 2012-03-05 17:00:00+00
(1 row)

You're first parsing the string as a timestamp and then converting it to a timestamptz using the currently set timezone. If we change the timezone, we get something else because Postgres assumes that timezone when converting a timestamp (or a string lacking timezone information) to timestamptz:

db=# SET timezone TO 'Europe/Berlin';
SET
db=# SELECT ('2012-03-05 17:00:00'::timestamp)::timestamptz;
      timestamptz
------------------------
 2012-03-05 17:00:00+01
(1 row)

This absolute time, expressed in UTC, is 2012-03-05 16:00:00+00, thus different from the original example.

---

I hope this clarifies things. Again, understanding the difference between timestamp and timestamptz is key. Think of wall time versus absolute time.