Time Zoneの変換

by afaik

投稿日: 12月 4, 2021

アプリケーション側でもTimeZoneを変換する事は可能ですが、データベース側で生成列等を利用して、自動的に変換しておいてパフォーマンスを改善したりする等の活用方法もあるかと思います。そんな時に活用してみて頂ければと思います。

MySQLにおけるTimeZone変換

CONVERT_TZ() タイムゾーン間での変換

変換例

root@localhost [mysql]> select * from time_zone_name limit 5;
+--------------------+--------------+
| Name               | Time_zone_id |
+--------------------+--------------+
| Africa/Abidjan     |            1 |
| Africa/Accra       |            2 |
| Africa/Addis_Ababa |            3 |
| Africa/Algiers     |            4 |
| Africa/Asmara      |            5 |
+--------------------+--------------+
5 rows in set (0.00 sec)

root@localhost [mysql]> SELECT @@GLOBAL.time_zone, @@SESSION.time_zone;
+--------------------+---------------------+
| @@GLOBAL.time_zone | @@SESSION.time_zone |
+--------------------+---------------------+
| SYSTEM             | SYSTEM              |
+--------------------+---------------------+
1 row in set (0.00 sec)

root@localhost [mysql]> select convert_tz('2021-01-01 00:00:01', 'Asia/Tokyo', 'America/New_York');
+---------------------------------------------------------------------+
| convert_tz('2021-01-01 00:00:01', 'Asia/Tokyo', 'America/New_York') |
+---------------------------------------------------------------------+
| 2020-12-31 10:00:01                                                 |
+---------------------------------------------------------------------+
1 row in set (0.00 sec)

root@localhost [mysql]> select convert_tz('2022-01-01 00:00:01', 'Asia/Tokyo', 'America/New_York');
+---------------------------------------------------------------------+
| convert_tz('2022-01-01 00:00:01', 'Asia/Tokyo', 'America/New_York') |
+---------------------------------------------------------------------+
| 2021-12-31 10:00:01                                                 |
+---------------------------------------------------------------------+
1 row in set (0.00 sec)

root@localhost [mysql]> select convert_tz(now(), 'Asia/Tokyo', 'America/New_York');
+-----------------------------------------------------+
| convert_tz(now(), 'Asia/Tokyo', 'America/New_York') |
+-----------------------------------------------------+
| 2021-12-03 22:06:52                                 |
+-----------------------------------------------------+
1 row in set (0.00 sec)

root@localhost [mysql]> select convert_tz(now(), 'Asia/Tokyo', 'America/Chicago');
+----------------------------------------------------+
| convert_tz(now(), 'Asia/Tokyo', 'America/Chicago') |
+----------------------------------------------------+
| 2021-12-03 21:10:54                                |
+----------------------------------------------------+
1 row in set (0.00 sec)

root@localhost [mysql]>

UTC to JST

mysql> select now(),DATE_SUB(now(), INTERVAL 9 hour) UTC,
    ->  convert_tz(DATE_SUB(now(), INTERVAL 9 hour),'UTC','Asia/Tokyo') JST;
+---------------------+---------------------+---------------------+
| now()               | UTC                 | JST                 |
+---------------------+---------------------+---------------------+
| 2021-12-10 07:32:49 | 2021-12-09 22:32:49 | 2021-12-10 07:32:49 |
+---------------------+---------------------+---------------------+
1 row in set (0.00 sec)

mysql>

Summer Timeの確認

root@localhost [mysql]> SELECT
    ->   CONVERT_TZ('2021-01-01 2:00:00','Asia/Tokyo','US/Central') AS Normal_Time,
    ->   CONVERT_TZ('2021-08-01 2:00:00','Asia/Tokyo','US/Central') AS Summer_Time;
+---------------------+---------------------+
| Normal_Time         | Summer_Time         |
+---------------------+---------------------+
| 2020-12-31 11:00:00 | 2021-07-31 12:00:00 |
+---------------------+---------------------+
1 row in set (0.00 sec)

root@localhost [mysql]> SELECT
    -> CONVERT_TZ('2021-01-01 2:00:00','Asia/Tokyo','US/Eastern') AS Normal_Time,
    -> CONVERT_TZ('2021-08-01 2:00:00','Asia/Tokyo','US/Eastern') AS Summer_Time;
+---------------------+---------------------+
| Normal_Time         | Summer_Time         |
+---------------------+---------------------+
| 2020-12-31 12:00:00 | 2021-07-31 13:00:00 |
+---------------------+---------------------+
1 row in set (0.00 sec)

root@localhost [mysql]>

5.1.15 MySQL Server でのタイムゾーンのサポート

留意：名前付きゾーンが使えない場合にファイルをダウンロードして利用出来ますが以下の点に留意して下さい。

システムに zoneinfo データベースがある場合は、ダウンロード可能なタイムゾーンパッケージを使用しないでください。代わりに、mysql_tzinfo_to_sql ユーティリティーを使用してください。そうしないと、MySQL とシステム上のほかのアプリケーション間で日時処理に違いが生じることがあります。

生成列を活用して自動変換

root@localhost [confirm]> CREATE TABLE `time_zone_generated_column` (
    ->   `id` int unsigned NOT NULL AUTO_INCREMENT,
    ->   `JST` timestamp NULL DEFAULT NULL,
    ->   `US_Est` timestamp GENERATED ALWAYS AS (CONVERT_TZ(`JST`,'Asia/Tokyo','US/Eastern')) VIRTUAL,
    ->   `US_Central` timestamp GENERATED ALWAYS AS (CONVERT_TZ(`JST`,'Asia/Tokyo','US/Central')) VIRTUAL,
    ->   `US_West` timestamp GENERATED ALWAYS AS (CONVERT_TZ(`JST`,'Asia/Tokyo','US/Pacific')) VIRTUAL,
    ->    PRIMARY KEY (`id`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
    -> ;
Query OK, 0 rows affected (0.06 sec)

root@localhost [confirm]> insert into time_zone_generated_column(JST) values(now());
Query OK, 1 row affected (0.02 sec)

root@localhost [confirm]> select * from time_zone_generated_column\G
*************************** 1. row ***************************
        id: 1
       JST: 2021-12-06 01:09:40
    US_Est: 2021-12-05 11:09:40
US_Central: 2021-12-05 10:09:40
   US_West: 2021-12-05 08:09:40
1 row in set (0.00 sec)

root@localhost [confirm]>

PostgreSQLにおけるTimeZone変換

AT TIME ZONE

タイムゾーン間での変換

postgres=# select * from pg_timezone_names limit 5;
       name        | abbrev | utc_offset | is_dst 
-------------------+--------+------------+--------
 Africa/Accra      | GMT    | 00:00:00   | f
 Africa/Algiers    | CET    | 01:00:00   | f
 Africa/Bissau     | GMT    | 00:00:00   | f
 Africa/Casablanca | +01    | 01:00:00   | t
 Africa/Ceuta      | CET    | 01:00:00   | f
(5 rows)

postgres=# SELECT ((timestamp '2022-01-01 00:00:01') AT TIME ZONE 'UTC');
        timezone        
------------------------
 2022-01-01 00:00:01+00
(1 row)

postgres=# SELECT ((now() AT TIME ZONE 'Asia/Tokyo') AT TIME ZONE 'UTC') ;
           timezone            
-------------------------------
 2021-12-04 21:41:04.633874+00
(1 row)

postgres=# SELECT ((now() AT TIME ZONE 'Asia/Tokyo') AT TIME ZONE 'EST') ;
           timezone            
-------------------------------
 2021-12-05 02:41:44.597034+00
(1 row)

postgres=# SELECT ((now() AT TIME ZONE 'Asia/Tokyo') AT TIME ZONE 'US/Central') ;
           timezone            
-------------------------------
 2021-12-05 03:42:32.244945+00
(1 row)

Summer Time 変換等

postgres=> SELECT now() JST,
postgres-> ((now() AT TIME ZONE 'Asia/Tokyo') AT TIME ZONE 'Europe/Rome') EU_Rome,
postgres-> ((now() AT TIME ZONE 'Asia/Tokyo') AT TIME ZONE 'US/Central')US_Central,
postgres-> ((timestamp '2021-01-01 2:00:00') AT TIME ZONE 'US/Central') US_Normal,
postgres-> ((timestamp '2021-08-01 2:00:00') AT TIME ZONE 'US/Central') US_Summer_Time,
postgres-> ((now() AT TIME ZONE 'Asia/Tokyo') AT TIME ZONE 'UTC') UTC ;
-[ RECORD 1 ]--+------------------------------
jst            | 2021-12-06 09:49:41.967618+09
eu_rome        | 2021-12-06 17:49:41.967618+09
us_central     | 2021-12-07 00:49:41.967618+09
us_normal      | 2021-01-01 17:00:00+09
us_summer_time | 2021-08-01 16:00:00+09
utc            | 2021-12-06 18:49:41.967618+09

参照：9.9. 日付/時刻関数と演算子

カテゴリー:

RDBMS Tech

タグ:

AT TIME ZONE CONVERT_TZ Generated Column MySQL PostgreSQL summer time timezone

最近の投稿

最近のコメント