Database, Postgres, PostgreSQL, Timestamps, Time Zone, Timezone, Timezones, timestamp with time zone

How to keep time zone information for timestamps in Postgres

Storing time zone information together with date and time in Postgres needs an extra column

Author Peter
Peter8th November 2022

Assume we want to build an application, where we store timestamps of various time zones. It might be a tool to collect incoming sensor data from many places around the world, and we want to show a nice table with those measured values together with the moment of occurrence in the actual application time zone, but also the original values, and related time zone.

"Easy!", you might think, we can just use the timestamp with time zone data type of Postgres, and we are settled. However, it is not that easy as it might seem. We'll see what the problem is and how we can solve it in two possible ways.

The Problem

First, let's create a minimal table and insert some sensor data from various time zones:

1create table sensors(
2    id int primary key,
3    value double precision,
4    measured_at timestamp with time zone
5);
6
7insert into sensors(id, value, measured_at)
8values
9    (0, 33.7, '2021-06-15 03:00:00+00'::timestamp with time zone),   -- UTC
10    (1, -1.3, '2021-06-15 03:00:00+02'::timestamp with time zone),   -- Europe/Rome
11    (2, 12.0, '2021-06-15 03:00:00-04'::timestamp with time zone);   -- America/New_York
12

Second, let's see what we have:

1table sensors;
2
idvaluemeasured_at
033.72021-06-15 05:00:00+02
1-1.32021-06-15 03:00:00+02
212.02021-06-15 09:00:00+02

All timestamps have been converted to the current active time zone1, that is, the time zone configured as either your database installation default (see the official documentation's postgresql.conf and initdb to know more), or something you have set during your connection establishment or on a ROLE basis. It will be used to display and interprete timestamps:

1show timezone;
2
timezone
Europe/Rome

When we change that time zone setting, we get the following 2:

1set timezone = 'America/New_York';
2table sensors;
3
idvaluemeasured_at
033.72021-06-14 23:00:00-04
1-1.32021-06-14 21:00:00-04
212.02021-06-15 03:00:00-04

The UTC offset -04 is different. This implies, that we have converted and stored our measured_at timestamps without time zone (in UTC), and afterwards converted them while querying. The problem however is, that we have lost the original time zone. This might come as surprise.

We conclude, that timestamp with time zone does not mean, that we store the time zone with the timestamp in the database, but just convert it during insertion into UTC from the given time zone. After that, the original time zone information gets discarded.

Solution #1 - Storing UTC offsets

One idea might be to separate the time zone from the timestamp, store the UTC offset as an integer in a separate column and restore the original values from there.

1create table sensors(
2    id int primary key,
3    value double precision,
4    measured_at timestamp with time zone,
5    measured_utc_offset int
6);
7
8insert into sensors(id, value, measured_at, measured_utc_offset)
9values
10    (0, 33.7, '2021-06-15 03:00:00+00'::timestamp with time zone, 0),    -- UTC
11    (1, -1.3, '2021-06-15 03:00:00+02'::timestamp with time zone, 2),    -- Europe/Rome
12    (2, 12.0, '2021-06-15 03:00:00-04'::timestamp with time zone, -4);   -- America/New_York
13

So, measured_at holds the date and time stored in UTC as all timestamps in Postgres. We build it as timestamp with time zone, because we do not want to calculate the offset inside our application logic, but rather rely on Postgres methods.

A problem with this solution is, that UTC offsets are not safe to use over time. They might not correspond to time zones either at some point in the past or future. Time zones are subject to change over time in a region for legal, commercial, and social purposes. Such a region might decide to switch to another time zone without notification or to use or not use the summer time adjustment. A better solution, therefore, is to always use a name of a time zone.

Solution #2 - Storing time zone names

As discussed before, we need to store the time zone name as text in a separate column. Our table might look like this:

1create table sensors(
2    id int primary key,
3    value double precision,
4    measured_at timestamp with time zone,
5    measured_time_zone text
6);
7
8insert into sensors(id, value, measured_at, measured_time_zone)
9values
10    (0, 33.7, '2021-06-15 03:00:00+00'::timestamp with time zone, 'UTC'),
11    (1, -1.3, '2021-06-15 03:00:00+02'::timestamp with time zone, 'Europe/Rome'),
12    (2, 12.0, '2021-06-15 03:00:00-04'::timestamp with time zone, 'America/New_York');
13

Hint: Postgres has a table with all those time zone names, abbreviations, utc offsets and a flag, which tells us if it has currently observing daylight savings:

1table pg_catalog.pg_timezone_names
2

To be sure that we do not insert wrong time zone names, we can add a check. This check translates the current timestamp now() to a given time zone stored in measured_time_zone. If a stored value therein is not a valid time zone, we get a good error message, telling us that the time zone is not recognized.

1create table sensors(
2    id int primary key,
3    value double precision,
4    measured_at timestamp with time zone,
5    measured_time_zone text check (now() at time zone measured_time_zone is not null)
6);
7

Building the result table

We have found a proper solution to store our measurements with timestamps and time zones, without losing the time zone information. Now, it is time to build the result table.

We wanted to know the time when sensors sent a measurement in the current local session's timestamp with time zone, but also the original measured_at timestamps without time zone, and their time zones in a separate column:

1select
2    id,
3    value,
4    measured_at as measured_at_local,
5    measured_at at time zone measured_time_zone as measured_at_original,
6    measured_time_zone
7from
8    sensors;
9
idvaluemeasured_at_localmeasured_at_originalmeasured_time_zone
033.72021-06-15 05:00:00+022021-06-15 03:00:00UTC
1-1.32021-06-15 03:00:00+022021-06-15 03:00:00Europe/Rome
212.02021-06-15 09:00:00+022021-06-15 03:00:00America/New_York

Conclusion

In this blog post, we have seen that Postgres never stores time zone information, but always a timestamp in UTC. The timestamp with time zone type, just adjusts to UTC during insertion. Therefore, we must store time zone information in a separate column, if we don't want to loose this information. This can be either done as UTC offset or with time zone names. The latter has the advantage, that it supports variations over time in a region for legal, commercial, and social purposes, like daylight savings or offset changes.

Sources

Footnotes

  1. It is possible to refer to the time zone of your settings in queries. For example, use SELECT current_setting('timezone').

  2. Please note, if you always see the same timestamp representation, it might be that your database application or driver applies the current session default time zone instead. pgAdmin and DBeaver for example do so. It might be possible to change this in your application's settings.