Go to Qiita Advent Calendar 2024 Top
LoginSignup
13
6

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 3 years have passed since last update.

PostgreSQLAdvent Calendar 2020

Day 3
@anzai323(Nozomi Anzai)

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

Last updated at Posted at 2020-12-02

「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)
13
6
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
13
6

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?