The extended_timezone_names view

Download and install the date-time utilities code.

The code on this page and its child pages doesn't depend on the date-time utilities code. However, the code that the section Recommended practice for specifying the UTC offset describes does depend on the extended_timezone_names view. You might also find the views that this page and its child-pages describe to be ordinarily useful by letting you use the power of SQL to get the same information that would be rather tedious to get simply by reading the source data that the List of tz database time zones presents.

The code-kit creates a table in a PostgreSQL or YugabyteDB database with the data that the "List of tz database time zones" shows. The simplest way to get the data is just to copy-and-paste the table from the browser display into a plain text file. This naïve approach ends up with a file that has the <tab> character as the field separator—but these separator characters are missing on each line where, in the browser display, the first and maybe next few table cells are empty. There aren't many such rows, and it's easy to fix the missing <tab> characters by hand. This cleaned up file is included in the code kit to save you that effort. (There are other ways to get the same data from the Internet, and you may prefer to use one of these.)

Once you have the data in a plain text file, it's easy to use the \copy meta-command at the psql or ysqlsh prompt. (It uses <tab> as the default column separator.) This stages the copied-and-pasted browser data into a table. It turns out that the resulting table content has the character − (i.e. chr(8722)) in place of the regular - character (i.e. chr(45)). This affects the columns that record the winter and summer offsets from UTC and the latitude and longitude. The built-in function replace() is used to correct this anomaly.

The kit also includes the code to create the user-defined function jan_and_jul_tz_abbrevs_and_offsets() that creating the extended_timezone_names view depends upon. It also creates the views that are used to produce the lists that this page's child pages show.

This page has these child pages:

The pg_timezone_names view is populated from the tz database:

The tz database is a collaborative compilation of information about the world's time zones, primarily intended for use with computer programs and operating systems... [It has] the organizational backing of ICANN. The tz database is also known as tzdata, the zoneinfo database or IANA time zone database...

The population of pg_timezone_names is refreshed with successive PostgreSQL Versions. As of the release date of PostgreSQL Version 13.2, the set of names in the pg_timezone_names view in that environment is identical to the set of names in the tz database. (The name column in each is unique.) But YugabyteDB Version 2.4, based on PostgreSQL Version 11.2, has three small discrepancies and a few other inconsistencies. GitHub issue #8550 tracks this.

The pg_timezone_names view shows a projection of the tz database's columns. And the List of tz database time zones shows a different, but overlapping projection.

It's useful, therefore, to join these two projections as the extended_timezone_names view.

Create the 'extended_timezone_names' view

The extended_timezone_names view is created as the inner join of pg_timezone_names and the tz_database_time_zones_extended table, created by the code kit. The user-defined table function jan_and_jul_tz_abbrevs_and_offsets() is used to populate this table from the staging table for the data from the List of tz database time zones page by adding the columns std_abbrev (the Standard Time timezone abbreviation) and dst_abbrev (the Summer Time timezone abbreviation).

Various quality checks are made during the whole process. These discover a few more anomalies. These, too, are tracked by GitHub issue #8550. You can see all this in the downloaded code kit. Look for the spool file YB-QA-reports.txt. You can also install the kit using PostgreSQL. This will spool a corresponding PG-QA-reports.txt file.

Here's an example query that selects all of the columns from the extended_timezone_names view for three example timezones.:

\x on
select
  name,
  abbrev,
  std_abbrev,
  dst_abbrev,
  to_char_interval(utc_offset) as utc_offset,
  to_char_interval(std_offset) as std_offset,
  to_char_interval(dst_offset) as dst_offset,
  is_dst::text,
  country_code,
  lat_long,
  region_coverage,
  status
from extended_timezone_names
where name in ('America/Los_Angeles', 'Asia/Manila', 'Europe/London')
order by name;
\x off

This is the result:

name            | America/Los_Angeles
abbrev          | PDT
std_abbrev      | PST
dst_abbrev      | PDT
utc_offset      | -07:00
std_offset      | -08:00
dst_offset      | -07:00
is_dst          | true
country_code    | US
lat_long        | +340308-1181434
region_coverage | Pacific
status          | Canonical
----------------+--------------------
name            | Asia/Manila
abbrev          | PST
std_abbrev      | PST
dst_abbrev      | PST
utc_offset      |  08:00
std_offset      |  08:00
dst_offset      |  08:00
is_dst          | false
country_code    | PH
lat_long        | +1435+12100
region_coverage |
status          | Canonical
----------------+--------------------
name            | Europe/London
abbrev          | BST
std_abbrev      | GMT
dst_abbrev      | BST
utc_offset      |  01:00
std_offset      |  00:00
dst_offset      |  01:00
is_dst          | true
country_code    | GB
lat_long        | +513030-0000731
region_coverage |
status          | Canonical

Notice that the abbreviation PST has two different meanings, as was emphasized in the section The columns pg_timezone_names.abbrev and pg_timezone_abbrevs.abbrev record different kinds of facts.

The installation of the code kit finishes by spooling the Markdown source snippets that define the lists that are presented on this page's child pages.