LoginSignup
5
4

More than 5 years have passed since last update.

@ora_gonsuke777in日本オラクル株式会社

サマータイム(夏時間, Daylight Saving)を理解するためにTIMESTAMP WITH TIME ZONE型と戯れてみる。(Oracle Database)

Last updated at Posted at 2018-08-13

サマータイムが話題ですやね彡(ﾟ)(ﾟ) ワイら日本人には馴染みの薄いサマータイムですが、これを理解するために Oracle Database のTIMESTAMP WITH TIME ZONE型と戯れてみます。

1. TZR書式/TZD書式に使用可能な文字列を確認して、サマータイムを使用するタイムゾーンを選択

TZR書式/TZD書式に使用可能な文字列を確認します。V$TIMEZONE_NAMESビューから確認可能です。

-- TZR書式/TZD書式に使用可能な文字列を確認
SET LINESIZE 300;
SET PAGESIZE 1000;
SELECT * FROM V$TIMEZONE_NAMES;

TZNAME                             TZABBREV        CON_ID
---------------------------------- ----------- ----------
Africa/Abidjan                     LMT                  0
Africa/Abidjan                     GMT                  0
Africa/Accra                       LMT                  0
Africa/Accra                       GMT                  0
Africa/Accra                       GHST                 0
：
US/Mountain                        MPT                  0
US/Pacific ★                      LMT                  0
US/Pacific ★                      PST ★               0
US/Pacific ★                      PDT ★               0
US/Pacific ★                      PWT                  0
US/Pacific ★                      PPT                  0
US/Pacific-New                     LMT                  0
：

本エントリでは、サマータイムを使用するタイムゾーンのTZR書式として'US/Pacific'、TZD書式としてPSTとPDTを使ってを検証しますやで彡(ﾟ)(ﾟ)

2. サマータイム開始近辺の日付を生成

サマータイム開始近辺の日付を生成してみます。UTCと比較したいので【1.UTC(TZR)】【2.US/Pacific(TZR)】【3.US/Pacific(TZD)】【4.US/Pacific(TZR)をDATE型にキャスト】の4種類の値を生成してみます。 

-- 日付書式の設定
ALTER SESSION SET NLS_DATE_FORMAT         = 'YYYY-MM-DD HH24:MI:SS';
ALTER SESSION SET NLS_TIMESTAMP_FORMAT    = 'YYYY-MM-DD HH24:MI:SS';
ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT = 'YYYY-MM-DD HH24:MI:SS TZR';

-- 日付リテラルを格納する変数
VAR v_ts_char VARCHAR2(100);

-- 表示設定
SET LINESIZE 200;
SET PAGESIZE 100;
COLUMN UTC_TZR         FORMAT A26;
COLUMN US_Pacific_TZR  FORMAT A36;
COLUMN US_Pacific_TZD  FORMAT A28;
COLUMN US_Pacific_DATE FORMAT A20;

-- 日付リテラル代入（サマータイム開始近辺）
EXEC :v_ts_char := '2018-03-11 00:00:00';

-- 日付生成
WITH TS_TMP AS (
   SELECT TO_TIMESTAMP_TZ(TO_TIMESTAMP(:v_ts_char) + (LEVEL-1)/48 || ' UTC')                           AS UTC_TZR
        , TO_TIMESTAMP_TZ(TO_TIMESTAMP(:v_ts_char) + (LEVEL-1)/48 || ' UTC') AT TIME ZONE 'US/Pacific' AS US_Pacific_TZR
     FROM DUAL
  CONNECT BY LEVEL <= 30
)
SELECT TS_TMP.*
     , TO_CHAR(TS_TMP.US_Pacific_TZR, 'YYYY-MM-DD HH24:MI:SS TZD') AS US_Pacific_TZD
     , CAST(TS_TMP.US_Pacific_TZR AS DATE)                         AS US_Pacific_DATE
  FROM TS_TMP;

UTC_TZR                    US_PACIFIC_TZR                       US_PACIFIC_TZD               US_PACIFIC_DATE
-------------------------- ------------------------------------ ---------------------------- --------------------
2018-03-11 00:00:00 UTC    2018-03-10 16:00:00 US/PACIFIC       2018-03-10 16:00:00 PST      2018-03-10 16:00:00
2018-03-11 00:30:00 UTC    2018-03-10 16:30:00 US/PACIFIC       2018-03-10 16:30:00 PST      2018-03-10 16:30:00
:
2018-03-11 08:30:00 UTC    2018-03-11 00:30:00 US/PACIFIC       2018-03-11 00:30:00 PST      2018-03-11 00:30:00
2018-03-11 09:00:00 UTC    2018-03-11 01:00:00 US/PACIFIC       2018-03-11 01:00:00 PST      2018-03-11 01:00:00
2018-03-11 09:30:00 UTC    2018-03-11 01:30:00 US/PACIFIC ★    2018-03-11 01:30:00 PST ★   2018-03-11 01:30:00 ★
2018-03-11 10:00:00 UTC    2018-03-11 03:00:00 US/PACIFIC ★    2018-03-11 03:00:00 PDT ★   2018-03-11 03:00:00 ★
2018-03-11 10:30:00 UTC    2018-03-11 03:30:00 US/PACIFIC       2018-03-11 03:30:00 PDT      2018-03-11 03:30:00
2018-03-11 11:00:00 UTC    2018-03-11 04:00:00 US/PACIFIC       2018-03-11 04:00:00 PDT      2018-03-11 04:00:00
:

UTC の '2018-03-11 10:00:00 UTC' のタイミングで、US/Pacificの日付については1時間進んでるのが解りますやね彡(ﾟ)(ﾟ)

3. サマータイム終了近辺の日付を生成

同様にサマータイム終了近辺の日付を生成してみます。

-- 日付リテラル代入（サマータイム終了近辺）
EXEC :v_ts_char := '2018-11-04 00:00:00';

-- 日付生成
WITH TS_TMP AS (
   SELECT TO_TIMESTAMP_TZ(TO_TIMESTAMP(:v_ts_char) + (LEVEL-1)/48 || ' UTC')                           AS UTC_TZR
        , TO_TIMESTAMP_TZ(TO_TIMESTAMP(:v_ts_char) + (LEVEL-1)/48 || ' UTC') AT TIME ZONE 'US/Pacific' AS US_Pacific_TZR
     FROM DUAL
  CONNECT BY LEVEL <= 30
)
SELECT TS_TMP.*
     , TO_CHAR(TS_TMP.US_Pacific_TZR, 'YYYY-MM-DD HH24:MI:SS TZD') AS US_Pacific_TZD
     , CAST(TS_TMP.US_Pacific_TZR AS DATE)                         AS US_Pacific_DATE
  FROM TS_TMP;

UTC_TZR                    US_PACIFIC_TZR                       US_PACIFIC_TZD               US_PACIFIC_DATE
-------------------------- ------------------------------------ ---------------------------- --------------------
2018-11-04 00:00:00 UTC    2018-11-03 17:00:00 US/PACIFIC       2018-11-03 17:00:00 PDT      2018-11-03 17:00:00
2018-11-04 00:30:00 UTC    2018-11-03 17:30:00 US/PACIFIC       2018-11-03 17:30:00 PDT      2018-11-03 17:30:00
:
2018-11-04 07:30:00 UTC    2018-11-04 00:30:00 US/PACIFIC       2018-11-04 00:30:00 PDT      2018-11-04 00:30:00
2018-11-04 08:00:00 UTC    2018-11-04 01:00:00 US/PACIFIC ★    2018-11-04 01:00:00 PDT ★   2018-11-04 01:00:00 ★
2018-11-04 08:30:00 UTC    2018-11-04 01:30:00 US/PACIFIC ★    2018-11-04 01:30:00 PDT ★   2018-11-04 01:30:00 ★
2018-11-04 09:00:00 UTC    2018-11-04 01:00:00 US/PACIFIC ★    2018-11-04 01:00:00 PST ★   2018-11-04 01:00:00 ★
2018-11-04 09:30:00 UTC    2018-11-04 01:30:00 US/PACIFIC ★    2018-11-04 01:30:00 PST ★   2018-11-04 01:30:00 ★
2018-11-04 10:00:00 UTC    2018-11-04 02:00:00 US/PACIFIC       2018-11-04 02:00:00 PST      2018-11-04 02:00:00
:

UTC の '2018-11-04 09:00:00 UTC'から1時間については、US/Pacificの日付で同じ時間が現れているのが解りますやね彡(ﾟ)(ﾟ)

4. サマータイム終了日付のデータを、現地時間の時刻で絞り込むとどうなるか？

下記にUS/Pacificの現地時間で2018/11/04の日付のデータを抽出した結果を出してみる。

-- 日付リテラル代入（サマータイム終了近辺）
EXEC :v_ts_char := '2018-11-03 00:00:00';

-- 日付生成
WITH TS_TMP AS (
   SELECT TO_TIMESTAMP_TZ(TO_TIMESTAMP(:v_ts_char) + (LEVEL-1)/48 || ' UTC')                           AS UTC_TZR
        , TO_TIMESTAMP_TZ(TO_TIMESTAMP(:v_ts_char) + (LEVEL-1)/48 || ' UTC') AT TIME ZONE 'US/Pacific' AS US_Pacific_TZR
     FROM DUAL
  CONNECT BY LEVEL <= 200
)
SELECT TS_TMP.*
     , TO_CHAR(TS_TMP.US_Pacific_TZR, 'YYYY-MM-DD HH24:MI:SS TZD') AS US_Pacific_TZD
     , CAST(TS_TMP.US_Pacific_TZR AS DATE)                         AS US_Pacific_DATE
  FROM TS_TMP
 WHERE UTC_TZR >= TO_TIMESTAMP_TZ('2018-11-04 00:00:00 US/Pacific', 'YYYY-MM-DD HH24:MI:SS TZR') AT TIME ZONE 'UTC'
   AND UTC_TZR <  TO_TIMESTAMP_TZ('2018-11-05 00:00:00 US/Pacific', 'YYYY-MM-DD HH24:MI:SS TZR') AT TIME ZONE 'UTC';

UTC_TZR                    US_PACIFIC_TZR                       US_PACIFIC_TZD               US_PACIFIC_DATE
-------------------------- ------------------------------------ ---------------------------- --------------------
2018-11-04 07:00:00 UTC    2018-11-04 00:00:00 US/PACIFIC       2018-11-04 00:00:00 PDT      2018-11-04 00:00:00
2018-11-04 07:30:00 UTC    2018-11-04 00:30:00 US/PACIFIC       2018-11-04 00:30:00 PDT      2018-11-04 00:30:00
2018-11-04 08:00:00 UTC    2018-11-04 01:00:00 US/PACIFIC ★    2018-11-04 01:00:00 PDT ★   2018-11-04 01:00:00
2018-11-04 08:30:00 UTC    2018-11-04 01:30:00 US/PACIFIC ★    2018-11-04 01:30:00 PDT ★   2018-11-04 01:30:00
2018-11-04 09:00:00 UTC    2018-11-04 01:00:00 US/PACIFIC ★    2018-11-04 01:00:00 PST ★   2018-11-04 01:00:00
2018-11-04 09:30:00 UTC    2018-11-04 01:30:00 US/PACIFIC ★    2018-11-04 01:30:00 PST ★   2018-11-04 01:30:00
2018-11-04 10:00:00 UTC    2018-11-04 02:00:00 US/PACIFIC       2018-11-04 02:00:00 PST      2018-11-04 02:00:00
：
2018-11-05 07:00:00 UTC    2018-11-04 23:00:00 US/PACIFIC       2018-11-04 23:00:00 PST      2018-11-04 23:00:00
2018-11-05 07:30:00 UTC    2018-11-04 23:30:00 US/PACIFIC       2018-11-04 23:30:00 PST      2018-11-04 23:30:00

50 rows selected.

うーん、UTC違いの同じ時刻が2重に取れてくるやね彡(ﾟ)(ﾟ)

5. 日本式サマータイムに関する所感

Oracle Database としてのサマータイムの挙動はざっくりとは理解したので、
今話題の日本式サマータイムでの対応を考察してみる。
日本式サマータイムを Oracle Database できっちり対応しようとすると

  • 日付データについてはTIMESTAMP WITH TIME ZONE型の列に
    UTC形式 または TZR/TZD併用形式で記述した時刻を変換して格納
    ※最初は「UTC形式で格納」と表記したが「TZR/TZD併用形式」でも夏時間境界を判別可能なため。
  • 該当列を表示する時には【AT TIME ZONE 'Japan'】を付与して日本時間に変換
  • 絞り込み条件は日本時刻をUTC形式に変換して、UTC形式の列に絞込みを掛ける。 ただしサマータイム開始日や終了日については、1日の長さが22時間 or 26時間で変動するため、 24時間決め打ちでロジックを組むことはできない。

てな感じになるのかと彡(ﾟ)(ﾟ)

Oracle Database はサマータイムに対応しているものの、日付データを
TIMESTAMP WITH TIME ZONE型で夏時間を意識して格納しているシステムは、
少なくとも日本企業がスクラッチで組んだシステムでは相当レアなんじゃなかろうかと。

大半のシステムはDATE型やTIMESTAMP型で、あるいは文字列型日付データを保持していると
認識していて、これをデータの持ち方を変えて、更にプログラムまで変更して対応するのは、
まあ事実上不可能なんやろうなぁ……と考えざるを得ない彡(-)(-)

6. マニュアル、ほか追加検証

マニュアルは下記の通り彡(ﾟ)(ﾟ)

Oracle Database SQL言語リファレンス
18cバージョン 18.1 E93948-01
日時書式モデル
https://docs.oracle.com/cd/E96517_01/sqlrf/Format-Models.html#GUID-49B32A81-0904-433E-B7FE-51606672183A

TIMESTAMP WITH TIME ZONEデータ型
https://docs.oracle.com/cd/E96517_01/sqlrf/Data-Types.html#GUID-BE23545B-469A-4A57-8D13-505F2F5DB706

TO_TIMESTAMP_TZ
https://docs.oracle.com/cd/E96517_01/sqlrf/TO_TIMESTAMP_TZ.html#GUID-3999303B-89CA-4AA3-9817-458F36ADC9DC

CAST
https://docs.oracle.com/cd/E96517_01/sqlrf/CAST.html#GUID-5A70235E-1209-4281-8521-B94497AAEF75

Oracle Database
Databaseグローバリゼーション・サポート・ガイド 18c E94646-01
4.12 夏時間のサポート
https://docs.oracle.com/cd/E96517_01/nlspg/datetime-data-types-and-time-zone-support.html#GUID-E5171DEF-74D7-482A-B4E9-1EE3403E18BC
Oracle Databaseは、指定のタイム・ゾーンで夏時間が有効かどうかを自動的に判別し、
対応するローカル時間を返します。Oracle Databaseで夏時間が特定のタイム・ゾーンで有効かどうかを判別するには、
通常、日時値で十分です。夏時間の開始期間または終了期間は境界事例です。たとえば、米国東部では、
夏時間が有効になる時点で、時刻が午前1時59分59秒から午前3時0分0秒に変わります。
午前2時0分0秒～午前2時59分59秒の時間隔は存在しません。その時間隔に該当する値は無効です。
夏時間の終了時には、時刻が午前2時0分0秒から午前1時0分1秒に変わります。
午前1時0分1秒～午前2時0分0秒の時間隔が繰り返されます。この時間隔に含まれる値は、2回発生するため不明確となります。

このような境界事例を解決するために、Oracle DatabaseではTZRおよびTZD書式要素を使用します。
TZRは、日時入力文字列でのタイム・ゾーン・リージョンを表します。たとえば、'Australia/North'、'UTC'および'Singapore'などです。
TZDは、夏時間情報を含むタイム・ゾーン・リージョンの略称を表します。たとえば、米国太平洋標準時間の場合は'PST'、
米国太平洋夏時間の場合は'PDT'です。TZRおよびTZD書式要素に対して有効な値のリストを表示するには、
V$TIMEZONE_NAMES動的パフォーマンス・ビューのTZNAME列とTZABBREV列を問い合せます。

4.2.1.3 TIMESTAMP WITH TIME ZONEデータ型
https://docs.oracle.com/cd/E96517_01/nlspg/datetime-data-types-and-time-zone-support.html#GUID-5BC5D2C1-6506-49BE-8177-F743A46FDC09
標準時間から夏時間への切替時に境界を明確にするには、TZR書式要素および対応するTZD書式要素の両方を使用します。
TZD書式要素は、夏時間情報を含むタイム・ゾーン・リージョンの略称です。たとえば、米国太平洋標準時間の場合はPST、
米国太平洋夏時間の場合はPDTです。次のように指定すると、確実に夏時間の値が戻されます。

上記マニュアルの記述に基づいて、夏時間終了時の現地時間について
繰り返される現地時刻をUTCに変換した結果を下記に示します。

-- TZR書式の時間をUTCに変換
SELECT TIMESTAMP '2018-11-04 01:30:00 US/Pacific'     AT TIME ZONE 'UTC' AS TZR_FORMAT FROM DUAL;

-- TZR書式＋TZD書式(PDT)の時間をUTCに変換
SELECT TIMESTAMP '2018-11-04 01:30:00 US/Pacific PDT' AT TIME ZONE 'UTC' AS TZR_WITH_PDT FROM DUAL;

-- TZR書式＋TZD書式(PST)の時間をUTCに変換
SELECT TIMESTAMP '2018-11-04 01:30:00 US/Pacific PST' AT TIME ZONE 'UTC' AS TZR_WITH_PST FROM DUAL;

TZR_FORMAT
---------------------------------------------------------------------------
2018-11-04 09:30:00 UTC

TZR_WITH_PDT
---------------------------------------------------------------------------
2018-11-04 08:30:00 UTC

TZR_WITH_PST
---------------------------------------------------------------------------
2018-11-04 09:30:00 UTC

TZR書式とTZD書式を併用すると、同じ時刻でもUTC相当の時刻を判別できるんやね彡(ﾟ)(ﾟ)

TIMESTAMP WITH TIME ZONE型のデータをテーブルに格納した場合の検証結果も残しときます。

CREATE TABLE TBL_TZ (
    C1 NUMBER
  , C2 TIMESTAMP WITH TIME ZONE
);
INSERT INTO TBL_TZ VALUES(1, TO_TIMESTAMP_TZ('2018-11-04 08:30:00 UTC', 'YYYY-MM-DD HH24:MI:SS TZR'));
INSERT INTO TBL_TZ VALUES(2, TO_TIMESTAMP_TZ('2018-11-04 09:30:00 UTC', 'YYYY-MM-DD HH24:MI:SS TZR'));
INSERT INTO TBL_TZ VALUES(3, TO_TIMESTAMP_TZ('2018-11-04 01:30:00 US/Pacific PDT', 'YYYY-MM-DD HH24:MI:SS TZR TZD'));
INSERT INTO TBL_TZ VALUES(4, TO_TIMESTAMP_TZ('2018-11-04 01:30:00 US/Pacific PST', 'YYYY-MM-DD HH24:MI:SS TZR TZD'));
COMMIT;

ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT = 'YYYY-MM-DD HH24:MI:SS TZR:TZD';
SET LINESIZE 300;
SET PAGESIZE 100;
COLUMN C1            FORMAT 999;
COLUMN C2            FORMAT A36;
COLUMN C2_UTC        FORMAT A30;
COLUMN C2_US_Pacific FORMAT A36;
COLUMN C2_Japan      FORMAT A32;
SELECT C1
     , C2
     , C2 AT TIME ZONE 'UTC'        AS C2_UTC
     , C2 AT TIME ZONE 'US/Pacific' AS C2_US_Pacific
     , C2 AT TIME ZONE 'Japan'      AS C2_Japan
  FROM TBL_TZ;

  C1 C2                                   C2_UTC                         C2_US_PACIFIC                        C2_JAPAN
---- ------------------------------------ ------------------------------ ------------------------------------ --------------------------------
   1 2018-11-04 08:30:00 UTC:GMT          2018-11-04 08:30:00 UTC:GMT    2018-11-04 01:30:00 US/PACIFIC:PDT   2018-11-04 17:30:00 JAPAN:JST
   2 2018-11-04 09:30:00 UTC:GMT          2018-11-04 09:30:00 UTC:GMT    2018-11-04 01:30:00 US/PACIFIC:PST   2018-11-04 18:30:00 JAPAN:JST
   3 2018-11-04 01:30:00 US/PACIFIC:PDT   2018-11-04 08:30:00 UTC:GMT    2018-11-04 01:30:00 US/PACIFIC:PDT   2018-11-04 17:30:00 JAPAN:JST
   4 2018-11-04 01:30:00 US/PACIFIC:PST   2018-11-04 09:30:00 UTC:GMT    2018-11-04 01:30:00 US/PACIFIC:PST   2018-11-04 18:30:00 JAPAN:JST

なるほど、同じ時刻でもPDTとPSTとでUTC時刻やJST時刻が異なる。
あまり馴染みが無かったけど TIMESTAMP WITH TIME ZONE型 は
サマータイムを処理するための色々な機能を備えてるんやね。彡(ﾟ)(ﾟ)

5
4
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
5
4