1

I new to Postgres and v9.6.3 on a Linux system.

From the docs and the PostgreSQL: Up and Running book, I learned that the timestamptz type just subtracts the offset of a timestamp on insertion and then just stores UTC on disk. The timezone information is 'lost' so to say.

Now I'm wondering how the psql client can display the timezone correctly despite that:

The following row was created by something like:

insert into table values ('1999-12-31T00:00:00+0100'::timestamptz, ...);

and is displayed as 

select time from table;

          time          | field1 | field2 | 
------------------------+--------+---------
 1999-12-31 00:00:00+01 |      1 |       1 | 
...

and 

select time AT TIMEZONE 'Europe/Berlin' from table;

          time          
---------------------
 1999-12-31 00:00:00
...

You can see in the first snippet that postgres seems to know the correct timezone from the insertion ('Europe/Berlin' which has +01h offset to GMT in winter) But how does this come? I would have expected it do display UTC time.

Improve this question
1
  • 1
    What is the output of SHOW TIMEZONE Commented Oct 5, 2017 at 19:16

2 Answers 2

Reset to default
0

I think it's a bit simpler than I assumed. What we're seeing here is that the top is returning a timestamp with time zone in ISO 8601, with tz offset. The bottom, because you're using AT TIMEZONE is returning a timestamp without time zone in the time zone specified. It gets stringified differently because the type is different and the timezone portion has been lost.

Using IS OF to verify this.

SELECT now(),
  now() IS OF (timestamp without time zone) AS "timestamp without time zone",
  now() IS OF (timestamp with time zone) AS "timestamp with time zone",
  now() IS OF (timestamp) AS "timestamp";
              now              | timestamp without time zone | timestamp with time zone | timestamp 
-------------------------------+-----------------------------+--------------------------+-----------
 2017-10-05 15:24:58.479183-05 | f                           | t                        | f

(1 row)

SELECT now() AT TIME ZONE 'America/Chicago',
  now() AT TIME ZONE 'America/Chicago' IS OF (timestamp without time zone) AS "timestamp without time zone",
  now() AT TIME ZONE 'America/Chicago' IS OF (timestamp with time zone) AS "timestamp with time zone",
  now() AT TIME ZONE 'America/Chicago' IS OF (timestamp) AS "timestamp";
          timezone          | timestamp without time zone | timestamp with time zone | timestamp 
----------------------------+-----------------------------+--------------------------+-----------
 2017-10-05 15:24:34.342588 | t                           | f                        | t
(1 row)

Thanks goes out to @johto on irc.freenode.net/#postgresql.

Improve this answer
2

From the PostgreSQL documentation you link to, about halfway down the page:

When a timestamp with time zone value is output, it is always converted from UTC to the current timezone zone, and displayed as local time in that zone. To see the time in another time zone, either change timezone or use the AT TIME ZONE construct.

So (as Evan Carroll hinted in his comment), if your system has 'Europe/Berlin' set as timezone, you would expect to see the UTC date/times output using that timezone.

To confirm, try inserting a date/time from another timezone; that should also be output showing the 'Europe/Berlin' time instead. Or, try changing timezone, and confirm that the dates that were showing the time from Europe/Berlin now show the time from the new time zone instead.

Improve this answer

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.