Oracleの日時データ型とタイムゾーンについて

Oracleデータベースは、さまざまな日時データ型を提供しています。これらのデータ型を適切に使用することで、日時データの管理が効率的に行えます。本記事では、DATE、TIMESTAMP、TIMESTAMP WITH TIME ZONE、TIMESTAMP WITH LOCAL TIME ZONEについて詳しく解説し、それぞれの適切な使用方法や注意点を説明します。

1. DATEデータ型

DATEデータ型は、日時情報を保存する基本的なデータ型です。

• 保存できる情報：
  • 年 (YEAR)
  • 月 (MONTH)
  • 日 (DAY)
  • 時 (HOUR)
  • 分 (MINUTE)
  • 秒 (SECOND)

使用例

以下は、DATE型のカラムを持つテーブルを作成し、データを挿入する例です。

CREATE TABLE date_example (
    id NUMBER,
    created_date DATE
);

INSERT INTO date_example (id, created_date)
VALUES (1, TO_DATE('2025-01-25 15:30:00', 'YYYY-MM-DD HH24:MI:SS'));

SQL> alter session set nls_date_format='YYYY/MM/DD HH24:MI:SS';

セッションが変更されました。

SQL> CREATE TABLE date_example (
  2      id NUMBER,
  3      created_date DATE
  4  );

表が作成されました。

SQL> INSERT INTO date_example (id, created_date)
  2  VALUES (1, TO_DATE('2025-01-25 15:30:00', 'YYYY-MM-DD HH24:MI:SS'));

1行が作成されました。

SQL> select * from date_example;

        ID CREATED_DATE
---------- -------------------
         1 2025/01/25 15:30:00

特徴と注意点

• 特徴: タイムゾーンの情報は保存されません。
• 注意点: 保存される時間はサーバーのデフォルトタイムゾーンに依存します。そのため、グローバルなアプリケーションでは注意が必要です。

---

2. TIMESTAMPデータ型

TIMESTAMPデータ型は、DATEにミリ秒単位の精度を追加したデータ型です。

• 保存できる情報：
  • 年、月、日、時、分、秒
  • ミリ秒 (Fractional Seconds)

使用例

以下は、TIMESTAMP型のカラムを持つテーブルを作成し、データを挿入する例です。

CREATE TABLE timestamp_example (
    id NUMBER,
    event_time TIMESTAMP
);

INSERT INTO timestamp_example (id, event_time)
VALUES (1, TO_TIMESTAMP('2025-01-25 15:30:00.123', 'YYYY-MM-DD HH24:MI:SS.FF3'));

SQL> CREATE TABLE timestamp_example (
  2      id NUMBER,
  3      event_time TIMESTAMP
  4  );

表が作成されました。

SQL> INSERT INTO timestamp_example (id, event_time)
  2  VALUES (1, TO_TIMESTAMP('2025-01-25 15:30:00.123', 'YYYY-MM-DD HH24:MI:SS.FF3'));

1行が作成されました。

SQL> select * from timestamp_example;

        ID EVENT_TIME
---------- ----------------------------------------------------
         1 25-01-25 15:30:00.123000

特徴と注意点

• 特徴: ミリ秒の精度が必要な場合に適しています。
• 注意点: タイムゾーン情報は含まれないため、時間を正確に管理したい場合はタイムゾーン対応型を検討する必要があります。

---

3. TIMESTAMP WITH TIME ZONEデータ型

TIMESTAMP WITH TIME ZONEは、タイムゾーン情報を含むデータ型です。タイムゾーンオフセットまたは地域名を含む日時データを管理できます。

• 保存できる情報：
  • 年、月、日、時、分、秒、ミリ秒
  • タイムゾーン (Time Zone Offset)

使用例

以下は、TIMESTAMP WITH TIME ZONE型のカラムを持つテーブルを作成し、データを挿入する例です。

CREATE TABLE timestamp_tz_example (
    id NUMBER,
    meeting_time TIMESTAMP WITH TIME ZONE
);

INSERT INTO timestamp_tz_example (id, meeting_time)
VALUES (1, TO_TIMESTAMP_TZ('2025-01-25 15:30:00 +09:00', 'YYYY-MM-DD HH24:MI:SS TZH:TZM'));

SQL> CREATE TABLE timestamp_tz_example (
  2      id NUMBER,
  3      meeting_time TIMESTAMP WITH TIME ZONE
  4  );

表が作成されました。

SQL> INSERT INTO timestamp_tz_example (id, meeting_time)
  2  VALUES (1, TO_TIMESTAMP_TZ('2025-01-25 15:30:00 +09:00', 'YYYY-MM-DD HH24:MI:SS TZH:TZM'));

1行が作成されました。

SQL> select * from timestamp_tz_example;

        ID MEETING_TIME
---------- ----------------------------------------------------
         1 25-01-25 15:30:00.000000 +09:00

特徴と注意点

• 特徴: タイムゾーン情報を含むため、異なる地域間での日時データの比較が容易です。
• 注意点: タイムゾーンオフセットは固定値として保存されるため、地域の夏時間 (DST) の変更を反映しません。

---

4. TIMESTAMP WITH LOCAL TIME ZONEデータ型

TIMESTAMP WITH LOCAL TIME ZONEは、タイムゾーン情報を含むデータ型ですが、データベース内部ではUTCに変換して保存されます。ユーザーがデータを取得する際には、セッションのタイムゾーンに変換されます。

• 保存できる情報：
  • 年、月、日、時、分、秒、ミリ秒
  • データベース内部ではUTCとして保存

使用例

以下は、TIMESTAMP WITH LOCAL TIME ZONE型のカラムを持つテーブルを作成し、データを挿入する例です。

CREATE TABLE timestamp_ltz_example (
    id NUMBER,
    login_time TIMESTAMP WITH LOCAL TIME ZONE
);

INSERT INTO timestamp_ltz_example (id, login_time)
VALUES (1, TO_TIMESTAMP('2025-01-25 15:30:00', 'YYYY-MM-DD HH24:MI:SS'));

SQL> CREATE TABLE timestamp_ltz_example (
  2      id NUMBER,
  3      login_time TIMESTAMP WITH LOCAL TIME ZONE
  4  );

表が作成されました。

SQL> INSERT INTO timestamp_ltz_example (id, login_time)
  2  VALUES (1, TO_TIMESTAMP('2025-01-25 15:30:00', 'YYYY-MM-DD HH24:MI:SS'));

1行が作成されました。

SQL> select * from timestamp_ltz_example;

        ID LOGIN_TIME
---------- ---------------------------------------------------------------------------
         1 25-01-25 15:30:00.000000

特徴と注意点

• 特徴: 異なるタイムゾーンを持つアプリケーション間でのデータ共有が容易です。
• 注意点: セッションのタイムゾーンに依存するため、複数のタイムゾーンを跨る環境では動作を理解して設定する必要があります。

---

各データ型の比較

以下の表は、各データ型の特徴を比較したものです。

データ型	ミリ秒	タイムゾーン	主な用途
DATE	なし	なし	基本的な日時データの保存
TIMESTAMP	あり	なし	ミリ秒の精度が必要な場合
TIMESTAMP WITH TIME ZONE	あり	あり	グローバルな日時データの管理
TIMESTAMP WITH LOCAL TIME ZONE	あり	セッション依存	アプリケーション間のタイムゾーン変換を自動で行う場合

---

まとめ

Oracleの日時データ型は、用途や必要な精度に応じて選択することが重要です。たとえば、

• タイムゾーンを無視する場合はDATEまたはTIMESTAMPを使用します。
• タイムゾーンを考慮する場合はTIMESTAMP WITH TIME ZONEまたはTIMESTAMP WITH LOCAL TIME ZONEを使用します。

また、グローバルなアプリケーションでは、タイムゾーンの違いや地域ごとの夏時間を考慮して適切にデータ型を選ぶことが推奨されます。適切なデータ型を選ぶことで、日時データ管理が効率的かつ信頼性の高いものになります。

[参考]
Oracle Databaseデータベース管理者ガイド 19c

Oracleのデータ型を徹底解説！数値・文字列・日付・LOBの違いと使い分け

Oracleデータベースでテーブルを作成する際には、各列に対して「どのようなデータを格納するか」を定義する必要があります。この定義に使われるのが「データ型（Data Type）」です。この記事では、Oracleで使用される主なデータ型につい...

oracle-master00000.com

2024.10.06