[Oracle] データベースのタイムゾーン

2020年7月28日

 異なるタイムゾーンにいるそれぞれの Oracle Database に接続されるプログラム間で、時刻をやり取りするにはどうすればよいか?について調べてみました。

  1. 前提となるシステム

     前提となるシステム構成は下図のような感じです。

  2. 検討前の対策案
    1. データベースサーバーのタイムゾーン(=DBTIMEZONE)を UTC にして、ローカルタイムはアプリケーション側で対処する。
    2. Oracle がもつ何かしらの時刻システムを利用する。
    3. 時刻型は使用せず文字列型で頑張る。(外道?)
  3. 検討後の個人的な結論
    • DBTIMEZONE の設定は変えない。
    • Timestamp with local time zone 型が扱いやすそう。
    • Date 型や Timestamp 型は使わないほうがよさそう。
  4. 結論に至るまでの考察。
    1. Date 型や Timestamp 型について。
      • Date 型や Timestamp 型は、そもそもタイムゾーン情報を持ち合わせていないので、その記録時刻がどのタイムゾーンに属するのかを判定することができない。
      • 同様に、よく使われる sysdate, systimestamp が戻す型は Date 型, Timestamp 型であるためタイムゾーンを持たない。
      • sysdate, systimestamp が戻す時刻は database のマシンの locale 情報に基づいている。このため ORA_SDTZ の設定(*1) を替えても、あくまで database が動作している PC の timezone での時刻が戻される。

      (*1) 環境変数 ORA_SDTZ にタイムゾーンを指定することで、セッション(=クライアント)のタイムゾーンを変えることができる。

    2. Timestamp with time zone 型について。
      • タイムゾーン情報を記録する。
      • 記録されたタイムゾーンは insert したセッションのタイムゾーンが記録される。このため UTC で同じ時刻であっても違う時刻として記録される。たとえば 09:00 +09:00 と 19:00 -05:00 はともに UTC 0:00 だが違うものとして扱われる。
      • 記録したデータを、他のタイムゾーンから読みだした場合、記録時のタイムゾーンの表記で読みだされる。+09:00 のセッションから -05:00 で記録したデータを読みだした場合、09:00 ではなく 19:00 -05:00 が得られる。
    3. Timestamp with local time zone 型について。
      • データベースのタイムゾーンに変換して記録される。
      • このため、insert したセッションのタイムゾーンに関わりなく、読みだし時にそのクライアント側のタイムゾーンの時刻に変換される。
  5. ここまでの要点
    • DATE型 / TIMESTAMP型はタイムゾーン情報が失われる。
    • Timestamp with time zone 型は Insert したクライアントのタイムゾーン情報も記録される。
    • Timestamp with local time zone 型は読み出し時にクライアントのタイムゾーンに変換されて表示される。
    • SYSDATE / SYSTIMESTAMP はクライアントのタイムゾーンが何であれ、常にデータベースサーバーのタイムゾーンの値で記録され、かつタイムゾーン情報は失われる。
  6. 挙動の確認

    1. 準備とか

      • テスト用のテーブルを作成する
        create table TIME (
        ID       number(2),
        DT       date,
        TS       timestamp,
        TSL      timestamp with local time zone,
        TSTZ     timestamp with time zone
        );
        
      • 表示を見易くするための設定など
        ALTER SESSION SET NLS_DATE_FORMAT = 'RR-MM-DD HH24:MI:SS';
        set TAB off
        set LINESIZE 300
        col SYSTIMESTAMP for A32
        col CURRENT_TIMESTAMP for A32
        col TS   for A25
        col TSL  for A25
        col TSTZ for A32
        col SESSIONTIMEZONE for A17
        
    2. それぞれのクライアントでデータベースタイムゾーン/セッションタイムゾーンを確認
      1. +09:00 側クライアントで確認
        SQL> select DBTIMEZONE,SESSIONTIMEZONE from dual;
        
        DBTIMEZONE   SESSIONTIMEZONE
        ------------ -----------------
        +00:00       +09:00
        
        SQL> select SYSDATE, CURRENT_DATE, SYSTIMESTAMP, CURRENT_TIMESTAMP from DUAL;
        
        SYSDATE           CURRENT_DATE      SYSTIMESTAMP                     CURRENT_TIMESTAMP
        ----------------- ----------------- -------------------------------- --------------------------------
        15-01-06 00:35:01 15-01-06 00:35:01 15-01-06 00:35:01.865000 +09:00  15-01-06 00:35:01.865000 +09:00
        
      2. -05:00 側クライアントで確認

         SYSDATE, SYSTIMESTAMP はクライアント側のタイムゾーンではなく、Database 側のタイムゾーンの時刻になっていることに注目します。

        SQL> select DBTIMEZONE,SESSIONTIMEZONE from dual;
        
        DBTIMEZONE   SESSIONTIMEZONE
        ------------ -----------------
        +00:00       -05:00
        
        SQL> select SYSDATE, CURRENT_DATE, SYSTIMESTAMP, CURRENT_TIMESTAMP from DUAL;
        
        SYSDATE           CURRENT_DATE      SYSTIMESTAMP                     CURRENT_TIMESTAMP
        ----------------- ----------------- -------------------------------- --------------------------------
        15-01-06 00:19:46 15-01-05 10:19:46 15-01-06 00:19:46.649000 +09:00  15-01-05 10:19:46.649000 -05:00
        
    3. CURRENT_TIMESTAMP を INSERT

       TSTZ 列は UTC としては同一時刻でも表記が異なる点に注目します。TSL 列はクライアント側のローカル時刻として表示されます。DT 列と TS 列は Insert したクライアントのローカル時刻で表示されますが、どのクライアントから Insert したかの情報(=タイムゾーン情報)は記録されていないため、時系列,発生順にソートすることができない記録になっていることに注目します。

      1. +09:00 側クライアントで操作
        SQL> insert into TIME values (1, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
        SQL> commit;
        
      2. -05:00 側クライアントで操作
        SQL> insert into TIME values (2, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
        SQL> commit;
        
      3. +09:00 側クライアントで確認
        SQL> select * from TIME;
        
        ID DT                TS                        TSL                       TSTZ
        -- ----------------- ------------------------- ------------------------- --------------------------------
         1 15-01-06 00:29:53 15-01-06 00:29:53.006000  15-01-06 00:29:53.006000  15-01-06 00:29:53.006000 +09:00
         2 15-01-05 10:29:53 15-01-05 10:29:53.488000  15-01-06 00:29:53.488000  15-01-05 10:29:53.488000 -05:00
        
      4. -05:00 側クライアントで確認
        SQL> select * from TIME;
        
        ID DT                TS                        TSL                       TSTZ
        -- ----------------- ------------------------- ------------------------- --------------------------------
         1 15-01-06 00:29:53 15-01-06 00:29:53.006000  15-01-05 10:29:53.006000  15-01-06 00:29:53.006000 +09:00
         2 15-01-05 10:29:53 15-01-05 10:29:53.488000  15-01-05 10:29:53.488000  15-01-05 10:29:53.488000 -05:00
        
    4. SYSTIMESTAMP を INSERT

       TSL 列はローカル時刻が表示されます。DT 列と TS 列は常に +09:00 (=DBTIMEZONE) で表示されます。TSTZ 列は SYSTIMESTAMP のタイムゾーン(=+9:00)で表示されます。

      1. +09:00 側クライアントで操作
        SQL> insert into TIME values (3, SYSTIMESTAMP, SYSTIMESTAMP, SYSTIMESTAMP, SYSTIMESTAMP);
        SQL> commit;
        
      2. -05:00 側クライアントで操作
        SQL> insert into TIME values (4, SYSTIMESTAMP, SYSTIMESTAMP, SYSTIMESTAMP, SYSTIMESTAMP);
        SQL> commit;
        
      3. +09:00 側クライアントで確認
        SQL> select TIME.*, CURRENT_TIMESTAMP from TIME;
        
        ID DT                TS                        TSL                       TSTZ                             CURRENT_TIMESTAMP
        -- ----------------- ------------------------- ------------------------- -------------------------------- --------------------------------
         3 15-01-06 00:45:17 15-01-06 00:45:17.809000  15-01-06 00:45:17.809000  15-01-06 00:45:17.809000 +09:00  15-01-06 00:54:34.766000 +09:00
         4 15-01-06 00:45:18 15-01-06 00:45:18.349000  15-01-06 00:45:18.349000  15-01-06 00:45:18.349000 +09:00  15-01-06 00:54:34.766000 +09:00
        
      4. -05:00 側クライアントで確認
        SQL> select TIME.*, CURRENT_TIMESTAMP from TIME;
        
        ID DT                TS                        TSL                       TSTZ                             CURRENT_TIMESTAMP
        -- ----------------- ------------------------- ------------------------- -------------------------------- --------------------------------
         3 15-01-06 00:45:17 15-01-06 00:45:17.809000  15-01-05 10:45:17.809000  15-01-06 00:45:17.809000 +09:00  15-01-05 10:55:14.351000 -05:00
         4 15-01-06 00:45:18 15-01-06 00:45:18.349000  15-01-05 10:45:18.349000  15-01-06 00:45:18.349000 +09:00  15-01-05 10:55:14.351000 -05:00
        
  7. 総論

     結論的にはシステムで強くタイムゾーンを意識する必要がある場合は Timestamp with time zone 型を使用します。そうではなく、システム内では一貫した時刻を管理するが、クライアント側の表示はローカル時刻で表示したい、という場合は Timestamp with local time zone 型を使うのが手軽である気がします。

  8. その他、時刻関連の注意事項

    1. 時刻データ型のサイズ

       それぞれのデータ型のサイズは以下の通りです。

      データ型 サイズ
      Date 型 7 Byte
      TimeStamp 型 7 Byte or 11 Byte
      Timestamp with time zone 型 13 Byte
      Timestamp with local time zone 型 7 Byte or 11 Byte

       公式ドキュメントは https://docs.oracle.com/cd/E16338_01/server.112/b56299/sql_elements001.htm などを参照。
       また更新ドキュメントを参照すると、タイムスタンプ型は TIMESTAMP [(fractional_seconds_precision)] という書き方ができ、精度の指定とそれによりサイズが変わる模様です。

    2. データベースのタイムゾーン(=DBTIMEZONE)が異なるサーバー間の Export/Import

       ここではテーマにしていませんが、DATE 型や TIMESTAMP 型の列は、Export したデータベースのタイムゾーンと Import するデータベースのタイムゾーンが異なると、時刻の意味が変わります。(Timezone の時刻差だけズレてしまう)
       そのようなデータ交換をする可能性があるシステムの場合は imestamp with local time zone 型または Timestamp with local time zone 型を使ったほうがよさそうです。






カテゴリー: Oracle, 時刻

Follow comments via the RSS Feed | Leave a comment | Trackback URL

コメントを投稿する

日本語が含まれない投稿は無視されますのでご注意ください。(スパム対策)


«   »
 
Powered by Wordpress and MySQL. Theme by Shlomi Noach, openark.org