Timezones and the Database

Simon Riggs
Simon Riggs

To avoid confusion with time settings globally and to ensure that customers don't have data sync issues, PostgreSQL allows users to set different timezone parameters within their systems. Here are some examples listed below.

EDB recommends using UTC everywhere to ensure that replication works as expected, whether this is for physical or logical replication (including BDR and pglogical).

TPAexec sets the OS timezone to UTC at initdb time, so that the server timezone and log_timezone are both set to UTC.

Messages sent to the server log are timestamped using the log_timezone parameter, which is set to the OS timezone at initdb. Server log messages are timestamped if the log_line_prefix contains %t, which includes the timezone. Comparing logs may be more difficult if done manually when the server logs on different nodes use a different log_timezone, which is why TPAexec forces log_timezone = UTC as best practice.

User sessions start in the server timezone (TimeZone configuration parameter), which is set to the OS timezone at initdb time.

User sessions may be specifically set away from the server timezone using SET TIME ZONE, a SQL Standard command, supported by PostgreSQL. If you do set timezones in your sessions you should make sure your data tables use the timestamp with time zone data type. The timestamp datatype (which is can be expanded to full data type name of timestamp without time zone) does not store the timezone information so data inserted into that datatype will have timezone data truncated. If the data is then viewed in a different timezone to the one in which the data was originally taken then this can appear to be different times. We do not recommend using the timestamp datatype, preferring instead timestamp with time zone, since it occupies same on-disk space, just has different semantics. Note that changing type using ALTER TABLE is not optimized (unless you are already in UTC).

When a transaction ends, the COMMIT record is inserted into the WAL with the commit timestamp set using the current OS time, returned as a timestamp with time zone datatype. This does not use the value of the log_timezone parameter, the server timezone, nor is it influenced by the current session timezone, but that doesn't matter because the timezone value is stored. Viewing commit timestamps can be performed using pg_waldump, noting that the displayed timezone will be the OS timezone at the time of execution. When two nodes have differing timezones it may be confusing to compare pg_waldump output, so best practice would be to always use TZ=UTC when executing pg_waldump.

As an example, here is what we get from pg_waldump with the Europe/Paris timezone:

postgres@kismet:~ $ TZ=Europe/Paris pg_waldump
/opt/postgres/data/pg_wal/000000010000000000000010 |grep COMMIT|grep 'lsn:
0/10367B70' rmgr: Transaction len (rec/tot): 46/ 46, tx: 459145, top: 0, lsn:
0/10367B70, prev 0/10367B08, desc: COMMIT 2020-01-23 05:45:37.787435 CET

Compared to what we get with the UTC timezone:

postgres@kismet:~ $ TZ=UTC pg_waldump
/opt/postgres/data/pg_wal/000000010000000000000010 |grep COMMIT|grep 'lsn:
0/10367B70' rmgr: Transaction len (rec/tot): 46/ 46, tx: 459145, top: 0, lsn:
0/10367B70, prev 0/10367B08, desc: COMMIT 2020-01-23 04:45:37.787435 UTC

This means that when BDR uses Timestamp Conflict Detection it will correctly compare commit timestamps from multiple nodes even when those servers use different timezone settings. Similarly, other logical replication such as pglogical and PostgreSQL 10+ Logical Replication will also handle timezone differences between servers correctly.

Note also that there are at least 3 timezone abbreviations that are associated with multiple timezones, e.g. IST and CST mean different things and hence have different timezone offsets in different countries. UTC is always the same timezone in all countries, as per the ISO8601 standard. UCT, UT, Z, ZULU are all synonyms for UTC, but we encourage the explicit use of UTC.

Was this article helpful?

0 out of 0 found this helpful