PostgreSQLで、タイムゾーンから現地時刻を得るには

「PostgreSQL Advent Calendar 2020」の 3 日目です。

以下、いざやろうとしたら意外と考えてしまったので記録です。

目的

以下のような事業所テーブルがあるとして、各事業所について現在時刻と時差を表示したいと思います。

=> SELECT * FROM branches ORDER BY id;
 id |     name     |      timezone       
----+--------------+---------------------
  1 | 日本         | Asia/Tokyo
  2 | 中国         | Asia/Shanghai
  3 | アメリカ     | America/New_York
  4 | カナダ       | America/Vancouver
  5 | イギリス     | Europe/London
  6 | フランス     | Europe/Paris
  7 | メキシコ     | America/Mexico_City
  8 | シンガポール | Asia/Singapore
  9 | タイ         | Asia/Bangkok
(9 rows)

※ CREATE TABLE

CREATE TABLE branches (
    id SERIAL,
    name TEXT,
    timezone TEXT
);

INSERT INTO branches (name, timezone) VALUES
('日本',         'Asia/Tokyo'),
('中国',         'Asia/Shanghai'),
('アメリカ',     'America/New_York'),
('カナダ',       'America/Vancouver'),
('イギリス',     'Europe/London'),
('フランス',     'Europe/Paris'),
('メキシコ',     'America/Mexico_City'),
('シンガポール', 'Asia/Singapore'),
('タイ',        'Asia/Bangkok');

※ 補足

タイムスタンプの型には、タイムゾーン情報の有無によって 2 種類あります。（参照）

名称	略称	例
TIMESTAMP	timestamp	2020-11-27 13:55:25.536255
TIMESTAMP WITH TIME ZONE	timestamptz	2020-11-27 13:55:25.536255+09

現在時刻（日時）を取得する関数も、タイムゾーン情報の有無によって 2 種類あります。（
参照）

関数名	返り値の型	返り値例
localtimestamp	timestamp	2020-11-27 13:55:25.536255
current_timestamp	timestamptz	2020-11-27 13:55:25.536255+09

1. pg_timezone_names を使っての出力

タイムゾーン名と現在の時差は、pg_timezone_names ビューから取得することができます。（参照）

※ \d pg_timezone_names

=> \d+ pg_timezone_names 
                       View "pg_catalog.pg_timezone_names"
   Column   |   Type   | Collation | Nullable | Default | Storage  | Description 
------------+----------+-----------+----------+---------+----------+-------------
 name       | text     |           |          |         | extended | 
 abbrev     | text     |           |          |         | extended | 
 utc_offset | interval |           |          |         | plain    | 
 is_dst     | boolean  |           |          |         | plain    | 
View definition:
 SELECT pg_timezone_names.name,
    pg_timezone_names.abbrev,
    pg_timezone_names.utc_offset,
    pg_timezone_names.is_dst
   FROM pg_timezone_names() pg_timezone_names(name, abbrev, utc_offset, is_dst);

=> \x
Expanded display is on.

=> \df+ pg_timezone_names
List of functions
-[ RECORD 1 ]-------+----------------------------------------------------------------------------
Schema              | pg_catalog
Name                | pg_timezone_names
Result data type    | SETOF record
Argument data types | OUT name text, OUT abbrev text, OUT utc_offset interval, OUT is_dst boolean
Type                | normal
Volatility          | stable
Parallel            | safe
Owner               | rdsadmin
Security            | invoker
Access privileges   | 
Language            | internal
Source code         | pg_timezone_names
Description         | get the available time zone name

pg_timezone_names ビューの utc_offset カラムが UTC との時差です。

=> SELECT * FROM pg_timezone_names WHERE name = 'Asia/Tokyo';

    name    | abbrev | utc_offset | is_dst 
------------+--------+------------+--------
 Asia/Tokyo | JST    | 09:00:00   | f
(1 row)

なので、以下のように UTC での localtimestamp の値に pg_timezone_names.utc_offset を足せば、目的の各事業所の現在時刻は求められます。

=> SET timezone TO 'UTC';
SET

=> SELECT
    b.*,
    t.utc_offset,
    localtimestamp + t.utc_offset AS _local_time
FROM branches AS b
JOIN pg_timezone_names AS t ON b.timezone = t.name
ORDER BY t.utc_offset DESC, b.id;

 id |     name     |      timezone       | utc_offset |        _local_time         
----+--------------+---------------------+------------+----------------------------
  1 | 日本         | Asia/Tokyo          | 09:00:00   | 2020-11-27 13:55:25.536255
  2 | 中国         | Asia/Shanghai       | 08:00:00   | 2020-11-27 12:55:25.536255
  8 | シンガポール | Asia/Singapore      | 08:00:00   | 2020-11-27 12:55:25.536255
  9 | タイ         | Asia/Bangkok        | 07:00:00   | 2020-11-27 11:55:25.536255
  6 | フランス     | Europe/Paris        | 01:00:00   | 2020-11-27 05:55:25.536255
  5 | イギリス     | Europe/London       | 00:00:00   | 2020-11-27 04:55:25.536255
  3 | アメリカ     | America/New_York    | -05:00:00  | 2020-11-26 23:55:25.536255
  7 | メキシコ     | America/Mexico_City | -06:00:00  | 2020-11-26 22:55:25.536255
  4 | カナダ       | America/Vancouver   | -08:00:00  | 2020-11-26 20:55:25.536255
(9 rows)

しかしここでイマイチなことが 2 つ・・・

• 起点とする UTC の時刻を取得するためだけに、SET timezone TO 'UTC' したくない
• 任意の日時についても出したい

2. AT TIME ZONE による出力

「AT TIME ZONE」を使うことで、指定した日時をタイムゾーンに変換することができます。（参照）

/* (1) timezone パラメータから変換 */
'timestamp 型の値'
    AT TIME ZONE '変換後のタイムゾーン'

/* (2) timestamptz 型のタイムゾーンから変換 */
'timestamptz 型の値'
    AT TIME ZONE '変換後のタイムゾーン'

/* (3) 指定タイムゾーンから変換 */
'timestamp 型の値'
    AT TIME ZONE '変換前のタイムゾーン'
    AT TIME ZONE '変換後のタイムゾーン'

以下、「日本がお正月を迎えたとき、UTC では何時？」という例です。

(1) timezone パラメータから変換

=> SHOW Timezone;
  TimeZone  
------------
 Asia/Tokyo
(1 row)

=> SELECT
    '2021-01-01 00:00:00'::timestamptz
        AT TIME ZONE 'UTC'
        AS _ts_utc;

       _ts_utc       
---------------------
 2020-12-31 15:00:00
(1 row)

(2) timestamptz 型のタイムゾーンから変換

=> SELECT
    '2021-01-01 00:00:00 Asia/Tokyo'::timestamptz
        AT TIME ZONE 'UTC'
        AS _ts_utc;

       _ts_utc       
---------------------
 2020-12-31 15:00:00
(1 row)

(3) 指定タイムゾーンから変換

-- timezone パラメータにかかわらないことを確認するため、適当に変更
=> SET timezone TO 'Europe/Moscow';
SET

=> SELECT
    '2021-01-01 00:00:00'::timestamp
        AT TIME ZONE 'Asia/Tokyo'
        AT TIME ZONE 'UTC'
        AS _ts_utc;

       _ts_utc       
---------------------
 2020-12-31 15:00:00
(1 row)

これを当初の目的にあてはめると、同じように出力できます。

/* 日本がお正月を迎えたとき、各事業所は何時？ */
=> SELECT
    id, name, timezone,
    -- UTC との時差
    '2021-01-01 00:00:00'::timestamp AT TIME ZONE 'UTC' -
    '2021-01-01 00:00:00'::timestamp AT TIME ZONE timezone AS _utc_offset,
    -- 日本時刻 -> 現地時刻
    '2021-01-01 00:00:00'::timestamp
        AT TIME ZONE 'Asia/Tokyo'
        AT TIME ZONE timezone
        AS _local_time
FROM branches
ORDER BY 4 DESC, id;

 id |     name     |      timezone       | _utc_offset |     _local_time     
----+--------------+---------------------+-------------+---------------------
  1 | 日本         | Asia/Tokyo          | 09:00:00    | 2021-01-01 00:00:00
  2 | 中国         | Asia/Shanghai       | 08:00:00    | 2020-12-31 23:00:00
  8 | シンガポール | Asia/Singapore      | 08:00:00    | 2020-12-31 23:00:00
  9 | タイ         | Asia/Bangkok        | 07:00:00    | 2020-12-31 22:00:00
  6 | フランス     | Europe/Paris        | 01:00:00    | 2020-12-31 16:00:00
  5 | イギリス     | Europe/London       | 00:00:00    | 2020-12-31 15:00:00
  3 | アメリカ     | America/New_York    | -05:00:00   | 2020-12-31 10:00:00
  7 | メキシコ     | America/Mexico_City | -06:00:00   | 2020-12-31 09:00:00
  4 | カナダ       | America/Vancouver   | -08:00:00   | 2020-12-31 07:00:00
(9 rows)

（おまけ）関数を作成

なんだか長ったらしいので、関数にしてみます。

/** 時差 */
CREATE FUNCTION tz_offset(timestamp, text, text)
RETURNS interval IMMUTABLE LANGUAGE sql
AS $$
     SELECT $1 AT TIME ZONE $2 - $1 AT TIME ZONE $3;
$$;

/** タイムゾーン変換 */
CREATE FUNCTION tz2tz(timestamp, text, text)
RETURNS timestamp IMMUTABLE LANGUAGE sql
AS $$
    SELECT $1 AT TIME ZONE $2 AT TIME ZONE $3;
$$;

ちょっとすっきり。

=> SELECT
    id, name, timezone,
    -- UTC との時差
    tz_offset('2021-01-01 00:00:00', 'UTC', timezone),
    -- 日本時刻 -> 現地時刻
    tz2tz('2021-01-01 00:00:00', 'Asia/Tokyo', timezone)
FROM branches
ORDER BY 4 DESC, id;

 id |     name     |      timezone       | tz_offset |        tz2tz        
----+--------------+---------------------+-----------+---------------------
  1 | 日本         | Asia/Tokyo          | 09:00:00  | 2021-01-01 00:00:00
  2 | 中国         | Asia/Shanghai       | 08:00:00  | 2020-12-31 23:00:00
  8 | シンガポール | Asia/Singapore      | 08:00:00  | 2020-12-31 23:00:00
  9 | タイ         | Asia/Bangkok        | 07:00:00  | 2020-12-31 22:00:00
  6 | フランス     | Europe/Paris        | 01:00:00  | 2020-12-31 16:00:00
  5 | イギリス     | Europe/London       | 00:00:00  | 2020-12-31 15:00:00
  3 | アメリカ     | America/New_York    | -05:00:00 | 2020-12-31 10:00:00
  7 | メキシコ     | America/Mexico_City | -06:00:00 | 2020-12-31 09:00:00
  4 | カナダ       | America/Vancouver   | -08:00:00 | 2020-12-31 07:00:00
(9 rows)

（おまけ）夏時間の確認

ちなみに、夏時間もうまく解釈できています。

/* 日本事業所にとっての毎月 1 日 0 時の、アメリカ事業所の現地時刻 */

=> SELECT
    gs AS _japan_time,
    -- UTC との時差
    tz_offset(gs, 'UTC', timezone) AS _utc_offset,
    -- 日本時刻 -> 現地時刻
    tz2tz(gs, 'Asia/Tokyo', timezone) AS _local_time
FROM branches
CROSS JOIN
    generate_series(
        '2020-01-01'::timestamp, '2020-12-31'::timestamp, '1 month'::interval
    ) AS gs
WHERE name = 'アメリカ';

     _japan_time     | _utc_offset |     _local_time     
---------------------+-------------+---------------------
 2020-01-01 00:00:00 | -05:00:00   | 2019-12-31 10:00:00
 2020-02-01 00:00:00 | -05:00:00   | 2020-01-31 10:00:00
 2020-03-01 00:00:00 | -05:00:00   | 2020-02-29 10:00:00
 2020-04-01 00:00:00 | -04:00:00   | 2020-03-31 11:00:00
 2020-05-01 00:00:00 | -04:00:00   | 2020-04-30 11:00:00
 2020-06-01 00:00:00 | -04:00:00   | 2020-05-31 11:00:00
 2020-07-01 00:00:00 | -04:00:00   | 2020-06-30 11:00:00
 2020-08-01 00:00:00 | -04:00:00   | 2020-07-31 11:00:00
 2020-09-01 00:00:00 | -04:00:00   | 2020-08-31 11:00:00
 2020-10-01 00:00:00 | -04:00:00   | 2020-09-30 11:00:00
 2020-11-01 00:00:00 | -04:00:00   | 2020-10-31 11:00:00
 2020-12-01 00:00:00 | -05:00:00   | 2020-11-30 10:00:00
(12 rows)