Timezones and UTC offsets

Understanding the purpose of specifying the timezone depends on understanding the 'timestamp and timestamptz' section.

To understand when, and why, you should specify the timezone (or, more carefully stated, the offset with respect to the UTC Time Standard) at which an operation will be performed, you need to understand the timestamptz data type, and converting its values to/from plain timestamp values. See the plain timestamp and timestamptz data types section.

The single word spelling, 'timezone' is used throughout the prose of the YSQL documentation.

The two spellings "timezone", as one word, and "time zone", as two words, both occur in SQL syntax. For example both set timezone = <arg> and set time zone <arg> are legal. On the other hand, you can decorate a plain timestamp or a timestamptz value with the at time zone operator—but here spelling it as the single word "timezone" causes an error. In contrast, the name of the run-time parameter, as is used in the invocation of the built-in function, must be spelled as a single word: current_setting('timezone').

Usually, the spelling of both the single word and the two separate words is case insensitive. Exceptionally, the column in the catalog view pg_settings.name includes the value 'TimeZone'. Of course, SQL queries against this view must respect this mixed case spelling.

The YSQL documentation, in the prose accounts, always spells "timezone" as a single word. And where the SQL syntax allows a choice, it is always spelled as a single word there too.

It's very likely indeed that anybody who has reason to read the YSQL documentation has large experience of attending, and arranging, meetings where the participants join remotely from locations spread over the planet. These days, this experience is commonplace, too, for anybody whose network of friends and relatives spans several countries, several states in North America, or the like—and who takes part in virtual social gatherings. Such meetings invariably use an online calendar that allows anyone who consults it to see the dates and times in their own local time.

Some of these calendar implementations will use a PostgreSQL or YugabyteDB database. Systems backed by these two databases (or, for that matter, by any SQL database) would doubtless represent events by their starts and ends, using a pair of timestamptz values—or, maybe by their start and duration, using a [timestamptz, interval] value tuple.

The timestamptz data type is overwhelmingly to be preferred over plain timestamp.

This is because, using timestamptz, the ability to see an absolute time value as a local time in any timezone of interest is brought simply and declaratively by setting the session's timezone environment variable.

In other words, the valuable semantic properties of the timestamptz data type are brought by the session TimeZone notion and are inseparable from it.

In the calendar use case, the setting will, ideally, be to the real, and canonically named, timezone to which the reader's location belongs. PostgreSQL and YugabyteDB use an implementation of the tz database. And the notions real and canonically named come from that. See the section The extended_timezone_names view.

The extended_timezone_names view joins the tz database data to the pg_timezone_names view. It's critically important to understand how the facts that this catalog view presents relate to those that the pg_timezone_abbrevs catalog view presents.

This page has these child pages: