How to keep time zone information for timestamps in Postgres

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

All timestamps have been converted to the current active time zone¹, 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

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

1set timezone = 'America/New_York';
2table sensors;
3

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

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

• Basil Bourque's post on Stackoverflow
• Matthew Schinckel's timezone check