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.