Search code examples
postgresqlcastingtimezonetimestamp

Confused about TIMESTAMPTZ internal conversion

I've spent a lot of time reading about 'best practices' when it comes to handling timestamps in Postgres, and have got a lot of conflicting answers. When using the TIMESTAMPTZ data type, I had assumed that NOW() and NOW() at time zone 'utc' would result in the same data being inserted in the database. Numerous comments online have suggested that internally the timezone isn't actually stored, but instead converted to UTC.

Why is it then, that when I run the following I don't get duplicate results? 

CREATE TABLE testtime(
  mytime TIMESTAMPTZ,
  descr VARCHAR
);

INSERT INTO testtime VALUES
  (NOW(), 'Now at NZST'),
  (NOW() AT TIME ZONE 'utc', 'Now at UTC');
SELECT *
FROM testtime;

Result: 

2016-02-17 02:08:30.845071  Now at NZST
2016-02-16 13:08:30.845071  Now at UTC
Solution

  • The reason is the implicit type cast from timestamp to timestamptz.

    • now() returns data type timestamptz.
    • now() AT TIME ZONE 'UTC' returns data type timestamp.
    • (now() AT TIME ZONE 'UTC')::timestamptz casts timestamp to timestamptz, assuming your current time zone in the process. This is where the difference is introduced.

    And this is what happens when you INSERT a timestamp value into a timestamptz column. Postgres has to assume some time zone. You seem to have expected that UTC would be assumed. The more reasonable default is the current time zone setting, though. If you set UTC in your session, you get the behavior you expected.

    Demo:

    With my time zone 'Europe/Vienna', which is currently 1 hour ahead of UTC (during winter time):

    SET timezone = 'Europe/Vienna';
SELECT now() AS now1
     , now() AT TIME ZONE 'UTC' AS now2
     , (now() AT TIME ZONE 'UTC')::timestamptz AS now3;

                  now1             |          now2              |           now3
-------------------------------+----------------------------+-------------------------------
 2016-02-16 14:30:07.243082+01 | 2016-02-16 13:30:07.243082 | 2016-02-16 13:30:07.243082+01

    The same with 'UTC' as time zone setting for the session:

    SET timezone = 'UTC';
SELECT now() AS now1
     , now() AT TIME ZONE 'UTC' AS now2
     , (now() AT TIME ZONE 'UTC')::timestamptz AS now3;

                  now1             |          now2              |           now3
-------------------------------+----------------------------+-------------------------------
 2016-02-16 13:30:58.739772+00 | 2016-02-16 13:30:58.739772 | 2016-02-16 13:30:58.739772+00

    Note how the first two columns have identical values - even though the text representation in now1 looks different, because that is adjusted to the time zone of the session, the value is the same.

    The third column has a different value, because a different time zone was assumed for the type cast.

    Basics here: