Today I Learned

Postgres timezone-aware fields don't store zone info

Let’s assume you have 2 columns in your database: time_without_tz and time_with_tz. You can now add some data:

INSERT INTO timestamps (time_without_tz, time_with_tz)
VALUES ('2019-10-19 10:30:00+02', '2019-10-19 10:30:00+02');

You can now query data with following output:

time_without_tz     | time_with_tz
--------------------+-----------------------
2019-10-19 10:30:00 | 2019-10-19 10:30:00+02

As you can see, the time_with_tz column returned the timestamp with +02 offset.

What’s really important here: Postgres do not store any information about time zone at all. If you pass the time zone information when you write the data - it is only used by Postgres to convert it to UTC. When querying, all timezone-aware fields are converted from the UTC value back to the local value (based on the timezone set by the database server). There is no way to retrieve a timezone that was used to save the record in a database - this information is gone.

It may be an obvious fact for most of you, but still, I think it’s worth noticing.

Sources: 1, 2