POSTGRESQL TIME ZONE MANAGEMENT

Preamble

Time zones and the SQL standard

The SQL standard only offers a few options for time zone specification:

<timestamp string> ::= <quote> <unquoted timestamp string> <quote> <unquoted time string> ::= <time value> [ <time zone interval> ] <time zone interval> ::= <sign> <hours value> <colon> <minutes value>

It states the following regarding time zones:

The surface of the earth is divided into zones, called time zones, in which every correct clock tells the same time, known as local time. Local time is equal to UTC (Coordinated Universal Time) plus the time zone displacement, which is an interval value that ranges between INTERVAL ‘–14:00’ HOUR TO MINUTE and INTERVAL ‘+14:00’ HOUR TO MINUTE. The time zone displacement is constant throughout a time zone, and changes at the beginning and end of Summer Time, where applicable.

In other words, the standard only includes time zone management clauses for a UTC offset. Users or programs that must adapt to changes in daylight saving time must adjust the offset accordingly at the proper intervals. It appears that not many usability factors were taken into account when developing this portion of the SQL standard (or none of the major database vendors with clout in the standard committee had a smarter implementation).

It’s also interesting to note that, although it’s not entirely clear on the subject, the SQL standard appears to regard the time zone offset as a component of the timestamp value.

Timestamp data types in PostgreSQL

In PostgreSQL, timestamps are represented by two different data types: timestamp (also known as timestamp without time zone) and timestamp with time zone (or, shorter, timestamptz). Time zone considerations are unimportant to us in this case because the timestamp is unaware of them. The resolution for both forms of data is microseconds (six decimal places).

Timestamps with time zones deviate from SQL standard guidelines in two ways:

• The time zone is not stored. Instead, the timestamp is kept as the duration since midnight on January 1, 2000, UTC.
• There are several ways to establish a time zone than the offset from UTC (details about that later). This enables you to efficiently manage changes in daylight saving time.

Users of PostgreSQL are confused by both of these issues, but the first one more so than the second. The name “timestamp with time zone” doesn’t really fit the PostgreSQL data type because it shows a specific point in time, not the time zone where an event happened. Instead, the term “absolute timestamp” would be more appropriate.

For the sake of completeness, let me include one terrible practice: for reasons I cannot fathom, many people record timestamps in databases as the duration in seconds since the “Unix epoch” (midnight, 1970-01-01 UTC). From the perspective of a database, there are several drawbacks to doing that rather than any advantages:

• To the unaided eye, the values kept in the database are incomprehensible.
• The majority of datetime arithmetic’s features, including the ability to compute current timestamp – INTERVAL “1 month,” are being lost.

The complexity of your WHERE criteria tends to increase as a result of the previous point, which hurts the efficiency of your queries.

The TimeZone database parameter

You might be wondering how to use timestamps with time zones in PostgreSQL after reading the observations in the preceding section. The key to this is in the database option TimeZone, which shows the time zone that is being used during the current database session. PostgreSQL shows a suitable offset from UTC when it turns a timestamp with a time zone into a string and formats it based on the current TimeZone setting.

CREATE TABLE tstz ( id bigint PRIMARY KEY, t timestamp with time zone NOT NULL ); INSERT INTO tstz VALUES (1, '2022-04-01 12:00:00+02:00'); INSERT INTO tstz VALUES (2, '2022-04-01 12:00:00-02:00'); SET TimeZone = 'UTC'; TABLE tstz; id │ t ════╪════════════════════════ 1 │ 2022-04-01 10:00:00+00 2 │ 2022-04-01 14:00:00+00 (2 rows)

It’s crucial to note that TimeZone isn’t meant to be configured centrally on the database server in this case. Instead, the option should be overridden for each database session in accordance with the time zone that is in effect on the client side. By doing this, everyone will receive the same timestamp formatted according to their local time zone.

Time zone management: syntax in PostgreSQL

The three time zone specification options in PostgreSQL are an addition to the limited time zone specifications in the SQL standard. They can be used as values for the TimeZone parameter and the AT TIME ZONE construct, as well as to specify a time zone in a timestamp constant.

IANA time zone names

IANA is a company that manages IP address blocks and top-level domains on the internet, but they also maintain a database of time zone definitions that are used by many software components all over the world. This database is frequently referred to as the “Olson database” in honor of its creator.

IANA uses the format “area/location” for time zone names. The word “area” can refer to a continent, an ocean, a particular area, etc. when used to describe “administrative zones” that don’t have a fixed position in space. Location tells what time zone the area is in and gives the name of the largest city or other important place in the area. These time zones include, for instance:

Europe/Paris, America/New York, Europe/Vienna, Pacific/Pitcairn, etc., are all in UTC.

To find a list of the time zones that are supported by your PostgreSQL installation, look in the system catalog under “pg timezone names.” Despite being supported by most operating systems, PostgreSQL maintains the IANA time zone database. You can select which copy to use when building PostgreSQL from source. To determine which version of the IANA time zone database your PostgreSQL binaries are using, run “pg config —configure”; if the output includes —with-system-tzdata, the operating system’s copy is being used.

Several of these time zones, including Europe/Vienna and Europe/Paris, are currently the same, so keep that in mind. These time zones weren’t always the same, and they don’t have to be in the future; therefore, it makes logical sense to keep the varied names.

The IANA time zones include definitions of daylight saving time, current and previous time zones, and more:

SET TimeZone = 'UTC'; SELECT TIMESTAMPTZ '2022-04-01 12:00:00 Europe/Vienna'; timestamptz ════════════════════════ 2022-04-01 10:00:00+00 (1 row) SELECT TIMESTAMPTZ '2022-03-01 12:00:00 Europe/Vienna'; timestamptz ════════════════════════ 2022-03-01 11:00:00+00 (1 row) SELECT TIMESTAMPTZ '1850-02-01 12:00:00 Europe/Vienna'; timestamptz ════════════════════════ 1850-02-01 10:54:39+00 (1 row)

The fact that Vienna switched to daylight saving time on the last Sunday in March 2022 accounts for the difference between the first and second results, and the peculiar last result reveals that Vienna used local solar time prior to 1893 rather than a time zone offset from Greenwich Mean Time.

Using IANA time zone names offers the richest functionality, and using them is the king’s way through the impenetrable jungle of regional time zone definitions.

acronyms for time zones

These are the well-known acronyms, such as “Pacific Standard Time” (PST) or “Central European Time” (CET). In the system catalog’s pg timezone abbreviations, you can discover the list that your PostgreSQL database recognizes.

Please take note of the following disadvantages of these time zone abbreviations:

• They do not include any information about daylight savings time; they are essentially abbreviations for a specific fixed offset from UTC. For instance, “Central European Time” (CET) and “Central European Summer Time” (CEST) exist.
• These acronyms are not governed by any international convention. For instance, IST stands for “Israel Standard Time” on my PostgreSQL installation (used in the winter and offset by 2 hours from UTC), while to an Irishwoman it stands for “Irish Summer Time,” and almost a billion people would assume it to be “India Standard Time.”

Even though it’s helpful to have a shorthand, I wouldn’t use anything other than UTC to shorten the name of a time zone.

time zone specifications in the POSIX style

Appendix B of the PostgreSQL documentation contains a description of the syntax for POSIX time zones. The main benefit of POSIX time zones is that they let you specify changes for daylight savings time. This means that they may be useful in situations that aren’t covered by the IANA time zone database, even if this is unlikely.

Here is the time zone that describes what is currently in use in the majority of European Union nations to demonstrate the full power of the format:

CET-1CEST,M3.5.0/2,M10.5.0/3

That is to be read as follows: during winter, the time zone abbreviation in effect isCET, which is one hour ahead of UTC. Daylight savings time, which has the abbreviationCEST, starts on the last (“fifth”) Sunday (day 0) of March (month 3) at 2 a.m. and ends on the last Sunday in October at 3 a.m.

Additionally, there are some issues with POSIX time zones:

• The offset in POSIX time zones has the opposite sign of the time zone offset used elsewhere. For example, “2022-04-01 12:00:00 UTC-2” is the same as “2022-04-01 12:00:00+02”: two hours east of UTC.
• More complicated POSIX time zones cannot be used in timestamp literals, since they confuse the timestamp parser.
• With POSIX time zone syntax, it is impossible to express historical time zone changes.

These points, in particular the first, are frequently unclear. Therefore, it is best to steer clear of POSIX time zones as well.

Type casts and conversions in PostgreSQL time zone management

Typecasts and the expression AT TIME ZONE are the two methods for converting between timestamps and time zones.

When data is cast between the two types, the timestamp is always taken to be in the time zone set by TimeZone.

SET TimeZone = 'UTC'; SELECT CAST (TIMESTAMP '2022-04-01 12:00:00' AS timestamp with time zone); timestamptz ════════════════════════ 2022-04-01 12:00:00+00 (1 row) SELECT CAST (TIMESTAMP WITH TIME ZONE '2022-04-01 12:00:00+02' AS timestamp); timestamp ═════════════════════ 2022-04-01 10:00:00 (1 row)

The expressionAT TIME ZONE also converts between the two data types:

When applied to atimestamp with time zone, the result is atimestamp that shows what a wall clock in the given time zone would show:

SELECT TIMESTAMP WITH TIME ZONE '2022-04-01 12:00:00+00' AT TIME ZONE 'Europe/Vienna'; timezone ═════════════════════ 2022-04-01 14:00:00 (1 row)

In time zone UTC, a Viennese wall clock would read two o’clock at twelve o’clock.

When applied to atimestamp, the result of AT TIME ZONE is a,timestamp with time zone which represents the absolute time corresponding to the given timestamp on a wall clock in the given time zone:

SELECT TIMESTAMP '2022-04-01 12:00:00' AT TIME ZONE 'Europe/Vienna'; timezone ════════════════════════ 2022-04-01 10:00:00+00 (1 row)

When a clock in Vienna reads 12, that means it is 10 a.m. UTC.

How to write a time zone-aware application

​When managing time zones in your application, there are two viable options:​

• Use timestamps consistently, save UTC timestamps, and leave time zone conversions up to the application.
• Use timestamps that include the time zone throughout, make sure that the TimeZone setting is accurate for each session, and let PostgreSQL handle time zone conversions.

Avoid hybrid solutions; they will likely cause discomfort and confusion.

Conclusion

Even though it is easy to get confused with time zones, you can steer clear of most problems if you use timestamp with timezone everywhere, stick with IANA time zone names and make sure to set the TimeZone parameter to the time zone on the client side. Then PostgreSQL will do all the heavy lifting for you.

About Enteros

​​​​Enteros offers a patented database performance management SaaS platform. It proactively identifies root causes of complex business-impacting database scalability and performance issues across a growing number of clouds, RDBMS, NoSQL, and machine learning database platforms.​