こんにちは、辻野です。

現在、Oracle MASTER Silver SQL 2019の資格取得を目指して勉強しております。
覚えたことの理解を深めるためのアウトプットとして記事を作成しました。
皆様のご参考になれば幸いです。

今回は日時のデータ型と関数について、説明していきたいと思います。

日時データ型の種類

Oracle Databaseでは下記表の通り、日時データ型の指定方法がいくつかあります。

データ型説明初期化パラメータの現在の値デフォルトの日付書式（英語環境）
DATE日付と時刻（小数秒を含まない）の情報を格納NLS_DATE_FORMATDD-MON-RR
TIMESTAMP日付と時刻（小数秒を含む）の情報を格納NLS_TIMESTAMP_FORMATDD-MON-RR HH.MI.SSXFF AM
TIMESTAMP WITH TIME ZONE日付と時刻（小数秒を含む）、タイムゾーンの情報を格納NLS_TIMESTAMP_TZ_FORMATDD-MON-RR HH.MI.SSXFF AM TZR
TIMESTAMP WITH LOCAL TIME ZONE正規化された日付と時刻（小数秒を含む）の情報を格納NLS_TIMESTAMP_FORMATDD-MON-RR HH.MI.SSXFF AM

※初期化パラメータの現在の値：TO_CHAR関数、TO_DATE関数、TO_TIMESTAMP関数等で使用するデフォルトの日付書式を指定しています。
※デフォルトの日付書式：資格試験では英語環境で問われるため、英語環境で記載しています。

DATE型、TIMESTAMP型について

日付と時刻の情報を格納する時に使用します。

DATE型は年月日時分秒の情報を格納します。
TIMESTAMP型は年月日時分秒に加え、DATE型では格納されない小数秒も格納します。

いずれの型も、タイムゾーンに対応していません。

TIMESTAMP WITH TIME ZONE型について

日付と時刻（小数秒を含む）、タイムゾーンの情報を格納する時に使用します。

データベースに日付、時刻、タイムゾーンの情報が登録されます。
タイムゾーンの情報とは、タイム・ゾーン・リージョン名（Asia/Tokyo）またはタイム・ゾーン・オフセット（+09:00）のことです。
ユーザーがデータを取得する際、ユーザーセッションのタイムゾーンによらず、データベースに登録したタイムゾーンで検索されます。

例えば、データベースに「2024/12/31 19:00:00 US/Pacific」の日付データを登録した後、ユーザーセッションのタイムゾーンが日本（+09:00）でも、その行はアメリカ太平洋の標準時間が戻されます。

TIMESTAMP WITH LOCAL TIME ZONE型について

ユーザーセッションのタイムゾーンで戻される日付と時刻（小数秒を含む）を格納する時に使用します。

データベースに格納される日付データはデータベースのタイムゾーンに対して正規化されて登録されます。
そして、ユーザーがデータを検索する時、そのユーザーセッションのタイムゾーンで日付データが戻されます。

例えば、ユーザーセッションのタイムゾーンが日本(+09:00)の場合、検索すると日本のタイムゾーンに対応する日付と時刻に変換され取得されます。

現在日時を取得する関数

Oracle Databaseでは下記表の通り、現在の日時を取得できる関数がいくつかあります。

関数基準とするタイムゾーン戻すデータ型
sysdateデータベースサーバーのOSのタイムゾーンDATE
systimestampデータベースサーバーのOSのタイムゾーンTIMESTAMP WITH TIME ZONE
current_dateユーザーセッションのタイムゾーンDATE
current_timestampユーザーセッションのタイムゾーンTIMESTAMP WITH TIME ZONE
localtimestampユーザーセッションのタイムゾーンTIMESTAMP

sysdate、systimestampについて

データベースサーバーのOSのタイムゾーンを基準とします。

注意点として、データベースサーバーのOSのタイムゾーンとユーザーセッションのタイムゾーンが違う場合、異なる日時が戻されます。

例えば、データベースサーバーのOSのタイムゾーンが「UTC」で、ユーザーセッションのタイムゾーンが「Asia/Tokyo」の場合、想定より9時間遅れた日時が戻されることになります。
※ユーザーが日本のタイムゾーン設定でクライアントアプリケーションを使用した時、日本の日時が2025年12時00分だった場合でも、タイムゾーンが「UTC」のため2025年03時00分が戻されます。

sysdateはDATE型、systimestampはTIMESTAMP WITH TIME ZONE型が戻されます。

current_date、current_timestamp、localtimestampについて

ユーザーセッションのタイムゾーンを基準とします。

例えば、ユーザーセッションのタイムゾーンが「Asia/Tokyo」の場合、日本の標準時間が戻されます。
ユーザーセッションのタイムゾーンが「US/Pacific」の場合、アメリカ太平洋側の標準時間が戻されます。

current_dateはDATE型、current_timestampはTIMESTAMP WITH TIME ZONE型、localtimestampはTIMESTAMP型が戻されます。

最後に

最後まで読んでいただきありがとうございました。
今回の記事を作成するにあたり、参考にした資料は下記の通りです。

参考書籍

オラクルマスター教科書 Silver SQL Oracle Database SQL
[著者] 株式会社コーソル 企画&マーケティング部 渡部亮太、桝井智行、峯岸隆一
[監修] 日本オラクル株式会社
[発行人] 佐々木幹夫 [発行所] 株式会社翔泳社 [発行日] 2021年9月13日(初版)
[参考ページ] p.45，504-509

参照サイト

Oracle Database データベース・リファレンス, 19c
[項目] 1 初期化パラメーター (1.212 NLS_DATE_FORMAT, 1.222 NLS_TIMESTAMP_FORMAT, 1.223 NLS_TIMESTAMP_TZ_FORMAT)
[URL] https://docs.oracle.com/cd/F19136_01/refrn/initialization-parameters-2.html#GUID-FD266F6F-D047-4EBB-8D96-B51B1DCA2D61
[閲覧日] 2021-12-02

Oracle Database SQL言語リファレンス, 19c
[項目] 7 ファンクション (SYSDATE, SYSTIMESTAMP, CURRENT_DATE, CURRENT_TIMESTAMP)
[URL] https://docs.oracle.com/cd/F19136_01/sqlrf/Functions.html#GUID-D079EFD3-C683-441F-977E-2C9503089982
[閲覧日] 2021-12-02