Synthetic timezones (do not observe Daylight Savings Time)

This table shows canonically-named timezones that are not associated with a specific country or region—in other words, they are synthetic timezones, a.k.a. time standards. UTC is the primary time standard. The remaining rows map all the offsets found among the real timezones that are multiples of one hour. (There are no synthetic timezones that correspond to real timezones with offsets that are non-integral multiples of one hour.) Do this to list the unusual timezones:

select
  name,
  lpad(std_offset::text, 9) as "STD offset",
  lpad(dst_offset::text, 9) as "DST offset"
from extended_timezone_names
where
  extract(minutes from std_offset) <> 0 or
  extract(minutes from dst_offset) <> 0
order by name, std_offset;

This is the result:

         name          | STD offset | DST offset
-----------------------+------------+------------
 America/St_Johns      | -03:30:00  | -02:30:00
 Asia/Calcutta         |  05:30:00  |  05:30:00
 Asia/Colombo          |  05:30:00  |  05:30:00
 Asia/Kabul            |  04:30:00  |  04:30:00
 Asia/Kathmandu        |  05:45:00  |  05:45:00
 Asia/Katmandu         |  05:45:00  |  05:45:00
 Asia/Kolkata          |  05:30:00  |  05:30:00
 Asia/Rangoon          |  06:30:00  |  06:30:00
 Asia/Tehran           |  03:30:00  |  04:30:00
 Asia/Yangon           |  06:30:00  |  06:30:00
 Australia/Adelaide    |  09:30:00  |  10:30:00
 Australia/Broken_Hill |  09:30:00  |  10:30:00
 Australia/Darwin      |  09:30:00  |  09:30:00
 Australia/Eucla       |  08:45:00  |  08:45:00
 Australia/LHI         |  10:30:00  |  11:00:00
 Australia/Lord_Howe   |  10:30:00  |  11:00:00
 Australia/North       |  09:30:00  |  09:30:00
 Australia/South       |  09:30:00  |  10:30:00
 Australia/Yancowinna  |  09:30:00  |  10:30:00
 Canada/Newfoundland   | -03:30:00  | -02:30:00
 Indian/Cocos          |  06:30:00  |  06:30:00
 Iran                  |  03:30:00  |  04:30:00
 NZ-CHAT               |  12:45:00  |  13:45:00
 Pacific/Chatham       |  12:45:00  |  13:45:00
 Pacific/Marquesas     | -09:30:00  | -09:30:00

Notice that Australia/Lord_Howe and Australia/LHI change the offset by just thirty minutes for Daylight Savings Time.

The results that the table below presents are based on the view canonical_no_country_no_dst and are ordered by the utc_offset column and then by the name column. Trivial code adds the Markdown table notation. The view is defined thus:

drop view if exists canonical_no_country_no_dst cascade;

create view canonical_no_country_no_dst as
select name, utc_offset
from extended_timezone_names
where
  name = 'UTC'

  or

  (
    -- There are rows with names 'Etc/GMT-0', 'Etc/GMT', and 'Etc/GMT0', all of
    -- which have an offset of zero from UTC. These entries are therefore redundant.
    name like 'Etc/GMT%'           and
    utc_offset <> make_interval()  and

    lower(status) = 'canonical'    and

    std_offset = dst_offset        and

    (
      country_code is null or
      country_code = ''
    )                              and

    (
      lat_long is null or
      lat_long = ''
    )                              and

    (
      region_coverage is null or
      region_coverage = ''
    )                              and

    lower(name) not in (select lower(abbrev) from pg_timezone_names) and
    lower(name) not in (select lower(abbrev) from pg_timezone_abbrevs)
  );

Here is the result:

name UTC offset
Etc/GMT+12 -12:00
Etc/GMT+11 -11:00
Etc/GMT+10 -10:00
Etc/GMT+9 -09:00
Etc/GMT+8 -08:00
Etc/GMT+7 -07:00
Etc/GMT+6 -06:00
Etc/GMT+5 -05:00
Etc/GMT+4 -04:00
Etc/GMT+3 -03:00
Etc/GMT+2 -02:00
Etc/GMT+1 -01:00
UTC 00:00
Etc/GMT-1 01:00
Etc/GMT-2 02:00
Etc/GMT-3 03:00
Etc/GMT-4 04:00
Etc/GMT-5 05:00
Etc/GMT-6 06:00
Etc/GMT-7 07:00
Etc/GMT-8 08:00
Etc/GMT-9 09:00
Etc/GMT-10 10:00
Etc/GMT-11 11:00
Etc/GMT-12 12:00
Etc/GMT-13 13:00
Etc/GMT-14 14:00

Notice that these timezones are named in accordance with the POSIX convention. This defines locations to the west of Greenwich UK to have a positive offset. But the value of the offset in pg_timezone_names for such a location is shown as a negative value.

You must be very careful to type correctly when you type these names. Because, for example, Etc/GMT-99 is not found in pg_timezone_names, it is interpreted at a POSIX-syntax specification of the offset—in this example positive ninety-nine hours.