Tips11. Oracleにおける日付型の扱い方について

RDBにおける日付型

データベースを操作するための言語であるSQLは標準化されており、概ねどのデータベースエンジンでも同じようなSQLが実行可能です。
データ型についても、大きくは数値(int,float,deciaml等)、文字列型、日付型と分かれているところも考え方は同じです。

数値型、文字列型についてはどのデータベースエンジンでも扱いはそれほど大きく変わりません。
しかし、日付型については データベースエンジンによって考え方が異なります。
（名前の上では、Oracleで最もスタンダードなNUMBER型やVARCHAR2型も他のDBにはないのですが。あくまでも考え方の違いということです）

データベースエンジン毎の日付型の違い

Oracleの場合

日付型は7バイトで保持するとありますが、実際には数値型に近い考え方ですので日付計算が考えやすくなっています。

  • 1日＝1.0
  • 1時間は1/24≒0.04166667
  • 1分は1/(24*60)≒0.000694444…
    となります。

Oracleの日付型には下記のような種類とデータサイズになっています。
「Oracle C++ Call Interfaceプログラマーズ・ガイド 11g リリース1（11.1）」
https://docs.oracle.com/cd/E15817_01/appdev.111/e05674/types.htm

  • DATE → 7バイト
  • TIMESTAMP → 11バイト
  • TIMESTAMP → WITH LOCAL TIME ZONE 7バイト
  • TIMESTAMP → WITH TIME ZONE 13バイト

日付型の内訳は、2000年6月1日午後3時17分の場合、下記のように保持されます。

バイト1234567
意味世紀年月日時分秒
–1201006116181

※世紀は1が西暦を意味し、20が2000年を表します。バイト2の100が「00」年を表します。
※時分秒は1からの値で表されるため、時の16が15時(＝午後3時)、分の18が17分を表します。

MySQLの場合

日付型の種類とデータサイズは下記のようになっているそうです。
https://dev.mysql.com/doc/refman/5.6/ja/storage-requirements.html

データ型MySQL 5.6.4 より前MySQL 5.6.4 以降
YEAR1 バイト1 バイト
DATE3 バイト3 バイト
TIME3 バイト3 バイト + 小数秒ストレージ
DATETIME8 バイト5 バイト + 小数秒ストレージ
TIMESTAMP4 バイト4 バイト + 小数秒ストレージ

PostgreSQLの場合

PostgreSQLは下記の通りです。
https://www.postgresql.jp/document/8.0/html/datatype-datetime.html

型名格納サイズ説明最小値最大値精度
timestamp [ (p) ] [ without time zone ]8 バイト日付と時刻両方4713 BC5874897 AD1μ秒 / 14桁
timestamp [ (p) ] with time zone8 バイト日付と時刻両方、時間帯付き4713 BC5874897 AD1μ秒 / 14桁
interval [ (p) ]12 バイト時間間隔-178,000,000 年178,000,000 年1μ秒、14桁
date4 バイト日付のみ4713 BC32767 AD1日
time [ (p) ] [ without time zone ]8 バイトその日の時刻のみ00:00:00.0023:59:59.991μ秒、14桁
time [ (p) ] with time zone12 バイトその日の時刻のみ、時間帯付き00:00:00.00+1223:59:59.99-121μ秒、14桁

随分と異なる考え方でデータを保持しているのかがわかると思います。

Oracleの日付型計算

Oracleの日付型が前述のような構成になっていることで、日付計算がシンプルになっています。
他のデータベースは、様々な関数が提供されているようですが、Oracleは少ない関数の組み合わせで記述できます。
様々な計算例を記述してみました。

日付計算の例

求めたい日付Oracle実装方法考え方
今日SYSDATEnowではありません。
今日の00:00TRUNC(SYSDATE)1日が1なので時刻部分は小数点以下。それを切り捨てます
明日の00:00TRUNC(SYSDATE+1)1日足して、時刻部分を切り捨てます
1週間後SYSDATE+77日後
今月1日TRUNC(SYSDATE,’MONTH’)TRUNC関数のオプションを使用します
今月末日LAST_DAY(TRUNC(SYSDATE))LAST_DAY関数を使用します
1ヶ月後ADD_MONTHS(SYSDATE,1)ADD_MONTHS関数を使用します
前月1日ADD_MONTHS(TRUNC(SYSDATE,’MONTH’),-1)今月1日の1ヶ月前です
前月末日(1)TRUNC(SYSDATE,’MONTH’)-1当月1日の1日前
前月末日(2)LAST_DAY(TRUNC(ADD_MONTHS(SYSDATE,-1)))1ヶ月前の月の末日(前者がオススメです)
今週の初日(日曜)TRUNC(SYSDATE, 'D’)TRUNC関数で取得可能です。NLS_TERRITORYにより異なる場合があります
今月20日TRUNC(SYSDATE,’MONTH’)+19当月1日に19日を足します
1年後ADD_MONTHS(SYSDATE,12)1年後＝12ヶ月後です
今年の1月1日TRUNC(SYSDATE,’YEAR’)TRUNC関数のオプションを使用します

時刻計算の例

求めたい時間Oracle実装方法考え方
1時間後SYSDATE+1/241日＝1なので1時間は1/24です
1分後SYSDATE+1/(24*60)1日＝1なので1分は1/(24*60)です
1秒後SYSDATE+1/(24*60*60)1日＝1なので1秒は1/(24*60*60)です
12時間前SYSDATE-0.51日＝1なので12時間は0.5です

覚える関数は「SYSDATE」「TRUNC」「ADD_MONTHS」「LAST_DAY」の4つだけ。
MySQLのように多数の日付計算関数を覚えて使い分ける必要はありません。

また、日付の差（引き算）も次のように求められます。

-- 日付間の引き算
SELECT TO_DATE('2020-03-31') - TO_DATE('2020-03-10') FROM dual
--------
21

DATEDIFF()関数とかを覚える必要はありません。

Oracleの日付書式

日付型・文字列型の変換を行う場合の書式指定も、データベースエンジン毎に異なります。
Oracleの場合は、下記のような書式になっています。

<参考サイト>
Oracle® Database SQL言語リファレンス11gリリース2 (11.2) 日時書式モデル
https://docs.oracle.com/cd/E16338_01/server.112/b56299/sql_elements004.htm

日時書式要素

求めたい値指定値備考
年YYYYYの数だけ表示。2018年でYYを指定すると18が取得される
和暦EE'Japanese Imperial’を指定すると取得可能
月MM
日DD
時間HH12時間表示の場合
時間HH2424時間表示
分MIMMではないので注意
秒SS
ミリ秒FF3タイムスタンプ型で使用可能

以下、様々な日付・時刻表示の例を示してみました。

日付書式の例

式結果備考
TO_CHAR(SYSDATE, 'YYYY-MM-DD’)2020-03-14一般的な書式
TO_CHAR(SYSDATE, 'YYYY/MM/DD’)2020/03/14区切りはいくつか指定できます
TO_CHAR(SYSDATE, 'YYYY"年"MM"月"DD"日"')2020年03月14日任意の区切りは""で括ります
TO_CHAR(SYSDATE, 'YY-MM-DD’)20-03-14年を2桁で指定する場合
TO_CHAR(SYSDATE, 'RR-MM-DD’)20-03-14※詳細は次章参照
TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS’)2020-03-14 13:30:33時分秒表示する場合
TO_CHAR(SYSDATE, 'DD-MON-YYYY’)14-3月 -2020欧米風の書式
TO_CHAR(SYSDATE, 'YYYY"年"MM"月"DD"日"(DAY)’)2020年03月14日(土曜日)曜日も表示可能
TO_CHAR(SYSDATE, 'YYYY"年"MM"月"DD"日"(DY)’)2020年03月14日(土)短い曜日表示
TO_CHAR(SYSDATE, 'DL’)2020年3月14日 土曜日NLS_TERRITORYおよびNLS_LANGUAGEに依存させた長い書式
TO_CHAR(SYSDATE, 'DS’)2020/03/14NLS_TERRITORYおよびNLS_LANGUAGEに依存させた短い書式
TO_CHAR(SYSDATE, 'YEAR-MONTH-DD’)TWENTY TWENTY-3月 -14使わないと思います・・

時刻書式の例

式結果備考
TO_CHAR(SYSDATE, 'HH24:MI:SS’)13:43:1024時間表示の時分秒表示
TO_CHAR(SYSDATE, 'HH:MI:SS’)01:43:1012時間表示
TO_CHAR(SYSDATE, 'HH12:MI:SS’)01:43:10明示的に12時間表示
TO_CHAR(SYSTIMESTAMP, 'HH24:MI:SS.FF3’)13:43:10.262ミリ秒表示する場合。SYSTIMESTAMPに注意
TO_CHAR(SYSTIMESTAMP, 'HH24:MI:SS.FF6’)13:43:10.969621ミリ秒6桁まで表示する場合
TO_CHAR(SYSTIMESTAMP, 'HH24:MI:SS.FF9’)13:43:10.9696210009桁まで指定可能ですが結果は000です

年書式の"Y"と"R"の違いについて

年を表す書式には通常"Y"を使用しますが、2000年問題対応で使用されるようになったのが"R"の書式です。
今では常に4桁で考慮しているとは思いますが、2桁で西暦の年を表す場合、"R"と"Y"では挙動が異なります。
実行してみると、

下2桁が50年未満の場合、

SELECT TO_CHAR(TO_DATE('49-01-01','RR-MM-DD'), 'YYYY-MM-DD') FROM dual
UNION ALL
SELECT TO_CHAR(TO_DATE('49-01-01','YY-MM-DD'), 'YYYY-MM-DD') FROM dual
  
2049-01-01
2049-01-01

下2桁が50年以降の場合、

SELECT TO_CHAR(TO_DATE('50-01-01','RR-MM-DD'), 'YYYY-MM-DD') FROM dual
UNION ALL
SELECT TO_CHAR(TO_DATE('50-01-01','YY-MM-DD'), 'YYYY-MM-DD') FROM dual
  
1950-01-01
2050-01-01

リファレンス「RR日時書式要素」には次のような記述があります。

指定された2桁の年が00から49の場合
・現在の年の下2桁が00から49であれば、戻される年は、現在の年と同じ上2桁を持ちます。
・現在の年の下2桁が50から99であれば、戻される年の上2桁は、現在の年の上2桁より1多くなります。

指定された2桁の年が50から99の場合
・現在の年の下2桁が00から49であれば、戻される年の上2桁は、現在の年の上2桁より1少なくなります。
・現在の年の下2桁が50から99であれば、戻される年は、現在の年と同じ上2桁を持ちます。

https://docs.oracle.com/cd/E16338_01/server.112/b56299/sql_elements004.htm#i116004

Oracleでの和暦変換について

日付型<->文字列型での和暦変換も可能です。
書式に「EE」とNLSパラメータ「NLS_CALENDAR」を指定することで可能となります。
（https://apex.oracle.com/ で実行）
「’Japanese Imperial’」はシングルクォートも含めて指定しますので、下記のような指定方法になります。

SELECT TO_CHAR(SYSDATE, 'EEYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=''Japanese Imperial''') FROM DUAL;
---------------------
平成32-03-14 11:52:11
  
SELECT TO_CHAR(SYSDATE, 'EYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=''Japanese Imperial''') FROM DUAL;
------------------
H32-03-14 11:52:45

・・・おや。年号パッチが当たっていないようです（＾＾；

2020-03-15Oracle,PL/SQLdate,Oracle,SYSDATE,SYSTIMESTAMP,time,TO_CHAR,TO_DATE,日付

Posted by tfurukaw