In a previous blog entry I suggested storing the original time zone offset in a separate column if clients need to know the stored time in the original time zone. There is some more complexity to this issue that I would like to cover.
First, when I suggested using select extract(timezone from current_timestamp), I assumed the user was also storing the current_timestamp value in the database. If they were storing a past or future timestamp in the database, they would need to use that value in extract, instead of current_timestamp.
Second, as was pointed out by Miha Vrhovnik in a blog comment, things get more complicated if a future time is stored in the database and the future time zone rules change after the data is stored. You might think this concern is only theoretical, but Russia made such changes in 2014 and 2016.
To get the proper behavior, you have to ask yourself, if you are storing a future timestamp, do you want to retain the same wall-clock time? If you were recording a future doctor's appointment, odds are you would want to adjust the stored value to reflect the same time of day (wall-clock time). If you were recording a future astronomical event, you would want to keep the same instant in time, even if the visible time changes. The default timestamp with time zone behavior is to retain the same instant in time, i.e., astronomical behavior.
To retain the same future wall-clock time after a time zone rule change, you would need to store the timestamp using without time zone and store the time zone name in a separate column. You would then need to combine the timestamp without time zone and the time zone name in each query to compute the instant in time based on the current time zone rules. Any indexes that did such computations would also need to be reindexed when time zone rules change.
This method allows the data to adjust to future time zone rule changes by computing the time zone offset on demand, rather than being locked in the time zone rules which were current at the time the data was entered, e.g.:
-- controls the OUTPUT time zone of AT TIME ZONE when passed a WITHOUT TIME ZONE value
SET TIME ZONE 'Europe/Moscow';
-- AT TIME ZONE specifies the time zone for the literal value
SELECT TIMESTAMP WITHOUT TIME ZONE '2012-03-08 09:00:00' AT TIME ZONE 'Europe/Moscow';
timezone
------------------------
2012-03-08 09:00:00+04
SELECT TIMESTAMP WITHOUT TIME ZONE '2022-03-08 09:00:00' AT TIME ZONE 'Europe/Moscow';
timezone
------------------------
2022-03-08 09:00:00+03
Basically, if the future time zone rules change, the output time of day would be the same, but the instant in time compared to utc would change, e.g. the +03. Of course, if all events are in the same time zone, and you don't want "astronomical behavior," then there is no need for time zone adjustments. Miha Vrhovnik is right that using time zones can be more trouble than they are worth, especially if all entries are in the same time zone and future time zone changes are a possibility.
Bruce Momjian is a Senior Database Architect at EnterpriseDB.
This post originally appeared on Bruce's personal blog.
