SQLを学んだ4回目

こんにちは。今日はSQLを学ぶ記事の第４回目です。前回の記事では単一行関数をいくつか取り上げました。今回はその中でも日付データを見ていきたいと思います。

すでにemployeesテーブルで利用しており、その度に11月がSEPとなったり、YY-MM-DDと指定があったりちょっと面白そうなので色々試してみたいと思います。

使用するのはOracle Database 18cです。

日付データ
おわりに

日付データ

日付データではemployeesテーブルのhiredateで利用しました。まずこれをみてみます。

SELECT hiredate FROM employees;


HIREDATE 
---------
12-12月-70
12-11月-90
05-12月-98
12-10月-80
17-11月-87
07-4月 -89
31-8月 -91
01-6月 -94
21-11月-98
11-7月 -98
08-11月-98

データが少し追加されていますが、気にしないでください。このDATE型ではDD-MM-YYとなっていることがわかります（yearはYYではなくRRと表現する方が主流？だそうです）。Yearは西暦の下二桁を表示しています。

日付関数

日付周りの関数を見ていきます。まずシステムの日付を取得する関数あるSYSDATEです。ちなみに本日は2020/04/29です。

SELECT SYSDATE FROM dual;

SYSDATE  
---------
29-4月 -20

MONTHS_BETWEEN関数は二つの日付データの差を返します。12月から10月を引くと2ヶ月となるのを確認します。一つ目の日付データから2つ目のデータを引くので、反対にしたらマイナスになるそうです。

SELECT MONTHS_BETWEEN('05-12月-98', '05-10月-98') output FROM dual;

    OUTPUT
----------
         2

SELECT MONTHS_BETWEEN('05-10月-98', '05-12月-98') output FROM dual;

    OUTPUT
----------
        -2

ADD_MONTHS関数は日付データに指定月プラスする関数です。どんな時に使うんでしょうか。NEXT_DAYは次の指定した曜日を返し、LAST_DAYは月の最終日を返す関数です。

SELECT '05-10月-98' output, ADD_MONTHS('05-10月-98', 2) output FROM dual;

OUTPUT    OUTPUT   
--------- ---------
05-10月-98 05-12月-98


SELECT NEXT_DAY('31-8月-04', 2) next_mon FROM dual;

NEXT_MON 
---------
06-9月 -04

SELECT LAST_DAY('03-8月-04') FROM dual;

LAST_DAY(
---------
31-8月 -04

ROUND関数は日付を四捨五入します。日付を四捨五入とは余り聞き慣れませんが、月を指定したら日付が四捨五入します。年を指定したら月が四捨五入されるのでしょうか。

SELECT ROUND(SYSDATE, 'MONTH') FROM dual;

ROUND(SYS
---------
01-5月 -20

SELECT ROUND(SYSDATE, 'YEAR') FROM dual;

ROUND(SYS
---------
01-1月 -20

正解！月が四捨五入されました。

変換関数

Oracle DatabaseはOracleサーバが自動的に行う”暗黙的データ変換”とユーザーが関数を指定して行う”明示的データ変換”があります。明示的データ変換をみてみます。

TO_DATEは過去に使いましたね。文字データを日付データに変換します。

SELECT TO_DATE('01-11-19', 'DD-MM-YY') output FROM dual;

OUTPUT   
---------
01-11月-19

SELECT TO_DATE('01-11-19', 'YY-MM-DD') output FROM dual;

OUTPUT   
---------
19-11月-01

YYとDDを入れ替えたら、ちゃんと19を年と識別して変換されました。

日付書式モデル

日付書式モデルは指定した日付の形式を返します。西暦を４桁で返すものや月を返すものなどがあります。変換関数と組み合わせて使うことが多い様です。色々試してみましょう。

//年号を指定桁で返す
SELECT SYSDATE, TO_CHAR(SYSDATE, 'YYYY') output FROM dual;

SYSDATE   OUTP
--------- ----
29-4月 -20 2020

SELECT SYSDATE, TO_CHAR(SYSDATE, 'YYY') output FROM dual;

SYSDATE   OUT
--------- ---
29-4月 -20 020


//月を指定桁で返す
SELECT SYSDATE, TO_CHAR(SYSDATE, 'MM') output FROM dual;

SYSDATE   OU
--------- --
29-4月 -20 04

//月を名前で返す
SELECT SYSDATE, TO_CHAR(SYSDATE, 'Month') output FROM dual;

SYSDATE   OUTPUT  
--------- --------
29-4月 -20 4月   

//日付を2桁で返す
SELECT SYSDATE, TO_CHAR(SYSDATE, 'DD') output FROM dual;

SYSDATE   OU
--------- --
28-4月 -20 28

//日付を曜日で返す
SELECT SYSDATE, TO_CHAR(SYSDATE, 'DAY') output FROM dual;

SYSDATE   OUTPUT      
--------- ------------
29-4月 -20 水曜日   

SELECT SYSDATE, TO_CHAR(SYSDATE, 'DY') output FROM dual;

SYSDATE   OUTP
--------- ----
29-4月 -20 水

まとめて試してみましたがやはり面白いですね。言語が日本語だとDYは月火水木金になるんですね。

さらに書式を変更することもできます。これまで使ってきたのはDD-MM-YYと(-)でしたがスラッシュやドットで表すこともできます。

SELECT TO_CHAR(SYSDATE, 'DD-MM-YY') output1, TO_CHAR(SYSDATE, 'DD/MM/YY') output2, TO_CHAR(SYSDATE, 'DD.MM.YY') output2 FROM dual;

OUTPUT1  OUTPUT2  OUTPUT2 
-------- -------- --------
28-04-20 28/04/20 28.04.20

個人的には横線よりスラッシュの方がわかりやすいです。