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.

Answer 2

Here is what I understand. Please bare with me.
My default timezone, defined in the postgresql.conf is UTC. Check this code

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);

Now, pretend this is Postgre talking :
There is special timezone defined for the output. So I will output everything in the default UTC. Lets go.

1 (timestamptz '2012-03-05 17:00:00+0')
This is time-aware data, the offset is 0, so its UTC. The default is also UTC. I will save it as is (no need to convert) and output 2012-03-05 17:00:00+00 because UTC input to UTC save to UTC output.

2 (timestamptz '2012-03-05 18:00:00+1')
Also time-aware data, the offset is +1, so its not UTC. Offset by minus 1 to convert it to UTC, so I can save it as UTC, that is the default. Output 2012-03-05 17:00:00+00 because not-UTC input to UTC save to UTC output.

3 (timestamp '2012-03-05 18:00:00+1')
Time-unaware data. Ignore the offset, assume this is the default UTC and save it as is. Output 2012-03-05 18:00:00+00 because, I-dont-know-I-dont-care-I-will-pretend-this-is-my-default-UTC-input to UTC save to UTC output.

4 (timestamp '2012-03-05 11:00:00' AT TIME ZONE '+6')
Again time-unaware data. Ignore the offset, if any. Then convert it to the given AT TIME ZONE '+6' offset so I can treat it as a complete time-unaware data. So my final data is 2012-03-05 17:00:00+00. But this is still not time-aware data. So, I will assume this is my default UTC and save it as is. Output 2012-03-05 17:00:00+00 because, I-dont-know-I-dont-care-I-will-pretend-this-is-my-default-UTC-input to UTC save to UTC output.

5 (timestamp '2012-03-05 17:00:00' AT TIME ZONE 'UTC')
Like the previous data, time-unaware data. I will ignore the offset, if any. Then I will convert it to the given AT TIME ZONE 'UTC', so no actual conversion, because there is no actual offset (UTC offset is 0). So my final data is 2012-03-05 17:00:00. But this is still not time-aware data. So, I will assume this is my default UTC and save it as is. Output 2012-03-05 17:00:00 because, I-dont-know-I-dont-care-I-will-pretend-this-is-my-default-UTC-input to UTC save to UTC output

6 (timestamp '2012-03-05 17:00:00'::timestamp)
This is time-unaware data , converted to time-unaware data again. So, like 4, I will ignore any offset , if any. There is no AT TIME ZONE either, so no conversions. My final time-unaware data is '2012-03-05 17:00:00'. I will assume this is my default UTC and save it as is. Output 2012-03-05 17:00:00+00 because, I-dont-know-I-dont-care-I-will-pretend-this-is-my-default-UTC-input to UTC save to UTC output

7 (timestamp '2012-03-05 17:00:00'::timestamptz)
This is time-unaware data , converted to time-aware data. But there is no offset, conversion, nothing. So, this is UTC. So, I will save it as is. Output 2012-03-05 17:00:00+00 because UTC input to UTC save to UTC output.

(Hope the above will help anyone, in general)

NOW! Regarding the article
Example 1
SELECT timezone('US/Pacific', '2016-01-01 00:00');
Time-unaware data, but I can convert it to time-aware. According to the article, since there is no time zone information, it can be parsed in the default UTC timezone. So, time-aware, UTC data, save it as is, but convert it to US/Pacific before output it. This is why article says "We get the wall time in California for 2016-01-01 00:00 UTC. " Output is 2015-12-31 16:00:00, that is the wall time in California, for the '2016-01-01 00:00' UTC input.

Article also says "Note that we passed the timestamp as a string, which was implicitly cast to a timestamptz".
This could be written as SELECT timezone('US/Pacific', '2016-01-01 00:00'::timestamptz); and still output 2015-12-31 16:00:00. Time-aware data, no offset, so its offset 0, so its UTC. UTC is also the default, so just save it as is. Convert it to US/Pacific before outputting it. This is why it outputs 2015-12-31 16:00:00 again.

Since "timezone(zone, timestamp) is equivalent to the SQL-conforming construct timestamp AT TIME ZONE zone", according to the article, then

SELECT timezone('US/Pacific', '2016-01-01 00:00');
SELECT timezone('US/Pacific', '2016-01-01 00:00'::timestamptz);
timestamptz '2016-01-01 00:00' at time zone 'US/Pacific'
timestamptz '2016-01-01 00:00+00' at time zone 'US/Pacific'

are all the same
Time-aware data (or make it be time-aware), no offset, save it as UTC, output it converted, as US/Pacific.

Example 2
SELECT timezone('US/Pacific', '2016-01-01 00:00'::timestamp);
Time-unaware data. Can I convert it to the UTC default, like in Example 1? No, because is converted to time-unaware (::timestamp part). There is nothing I can do. It is time-aware data.

I will ignore the offset, if any. Unlike 4 above, there is no offset defined, no AT TIME ZONE '+ or -X'. So, to get the UTC I will convert the '2016-01-01 00:00' back to UTC, according to the US/Pacific. Add 8 hours to go from Pacific to UTC. My UTC now is 2016-01-01 08:00:00+00. Save it as is. Output 2016-01-01 08:00:00+00 because, I-dont-know-I-dont-care-I-will-pretend-this-is-my-default-UTC-input to UTC save to UTC output

Again, according to the article "timezone(zone, timestamp) is equivalent to the SQL-conforming construct timestamp AT TIME ZONE zone", so

SELECT timezone('US/Pacific', '2016-01-01 00:00'::timestamp);
timestamp '2016-01-01 00:00' at time zone 'US/Pacific'
timestamp '2016-01-01 00:00+00' at time zone 'US/Pacific'

are all the same

Time-unaware data, ignore offset, convert back to UTC, this is UTC, save as UTC output as UTC.

Thanks