異なるタイムゾーンにいるそれぞれの Oracle Database に接続されるプログラム間で、時刻をやり取りするにはどうすればよいか？について調べてみました。
- 前提となるシステム
前提となるシステム構成は下図のような感じです。
- 検討前の対策案
- データベースサーバーのタイムゾーン（=DBTIMEZONE）を UTC にして、ローカルタイムはアプリケーション側で対処する。
- Oracle がもつ何かしらの時刻システムを利用する。
- 時刻型は使用せず文字列型で頑張る。（外道？）
- 検討後の個人的な結論
- DBTIMEZONE の設定は変えない。
- Timestamp with local time zone 型が扱いやすそう。
- Date 型や Timestamp 型は使わないほうがよさそう。
- 結論に至るまでの考察。
- Date 型や Timestamp 型について。
- Date 型や Timestamp 型は、そもそもタイムゾーン情報を持ち合わせていないので、その記録時刻がどのタイムゾーンに属するのかを判定することができない。
- 同様に、よく使われる sysdate, systimestamp が戻す型は Date 型, Timestamp 型であるためタイムゾーンを持たない。
- sysdate, systimestamp が戻す時刻は database のマシンの locale 情報に基づいている。このため ORA_SDTZ の設定(*1) を替えても、あくまで database が動作している PC の timezone での時刻が戻される。
(*1) 環境変数 ORA_SDTZ にタイムゾーンを指定することで、セッション（=クライアント）のタイムゾーンを変えることができる。
- 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 が得られる。
- Timestamp with local time zone 型について。
- データベースのタイムゾーンに変換して記録される。
- このため、insert したセッションのタイムゾーンに関わりなく、読みだし時にそのクライアント側のタイムゾーンの時刻に変換される。
- ここまでの要点
- DATE型 / TIMESTAMP型はタイムゾーン情報が失われる。
- Timestamp with time zone 型は Insert したクライアントのタイムゾーン情報も記録される。
- Timestamp with local time zone 型は読み出し時にクライアントのタイムゾーンに変換されて表示される。
- SYSDATE / SYSTIMESTAMP はクライアントのタイムゾーンが何であれ、常にデータベースサーバーのタイムゾーンの値で記録され、かつタイムゾーン情報は失われる。
- 挙動の確認
-
準備とか
- テスト用のテーブルを作成する
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
- テスト用のテーブルを作成する
- それぞれのクライアントでデータベースタイムゾーン／セッションタイムゾーンを確認
- +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
- -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
- +09:00 側クライアントで確認
- CURRENT_TIMESTAMP を INSERT
TSTZ 列は UTC としては同一時刻でも表記が異なる点に注目します。TSL 列はクライアント側のローカル時刻として表示されます。DT 列と TS 列は Insert したクライアントのローカル時刻で表示されますが、どのクライアントから Insert したかの情報（＝タイムゾーン情報）は記録されていないため、時系列,発生順にソートすることができない記録になっていることに注目します。
- +09:00 側クライアントで操作
SQL> insert into TIME values (1, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP); SQL> commit;
- -05:00 側クライアントで操作
SQL> insert into TIME values (2, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP); SQL> commit;
- +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
- -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
- +09:00 側クライアントで操作
- SYSTIMESTAMP を INSERT
TSL 列はローカル時刻が表示されます。DT 列と TS 列は常に +09:00 (=DBTIMEZONE) で表示されます。TSTZ 列は SYSTIMESTAMP のタイムゾーン（=+9:00）で表示されます。
- +09:00 側クライアントで操作
SQL> insert into TIME values (3, SYSTIMESTAMP, SYSTIMESTAMP, SYSTIMESTAMP, SYSTIMESTAMP); SQL> commit;
- -05:00 側クライアントで操作
SQL> insert into TIME values (4, SYSTIMESTAMP, SYSTIMESTAMP, SYSTIMESTAMP, SYSTIMESTAMP); SQL> commit;
- +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
- -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
- +09:00 側クライアントで操作
- 総論
結論的にはシステムで強くタイムゾーンを意識する必要がある場合は Timestamp with time zone 型を使用します。そうではなく、システム内では一貫した時刻を管理するが、クライアント側の表示はローカル時刻で表示したい、という場合は Timestamp with local time zone 型を使うのが手軽である気がします。
- その他、時刻関連の注意事項
- 時刻データ型のサイズ
それぞれのデータ型のサイズは以下の通りです。
データ型 サイズ 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)] という書き方ができ、精度の指定とそれによりサイズが変わる模様です。 - データベースのタイムゾーン（=DBTIMEZONE）が異なるサーバー間の Export/Import
ここではテーマにしていませんが、DATE 型や TIMESTAMP 型の列は、Export したデータベースのタイムゾーンと Import するデータベースのタイムゾーンが異なると、時刻の意味が変わります。（Timezone の時刻差だけズレてしまう）
そのようなデータ交換をする可能性があるシステムの場合は imestamp with local time zone 型または Timestamp with local time zone 型を使ったほうがよさそうです。
コメントを投稿する