Oracle Databaseで日付から年・月・日だけを取り出したい場合は、EXTRACT関数を使用します。
例えば、注文日時から「2030年の注文だけを取得する」「12月の注文だけを取得する」といった使い方ができます。
この記事では、Oracle SQLのEXTRACT関数を使って、日付から年・月・日を取得する方法を初心者向けに紹介します。
前提条件
この記事では、注文履歴を管理しているordersテーブルを使用します。
テーブルの内容は次のとおりです。
ID USER_ID AMOUNT ORDER_TIME -- ------- ------ ------------------- 1 2 6000 2030-12-31 11:00:00 2 6 2000 2030-12-25 18:30:00 3 1 3000 2030-12-10 10:00:00 4 4 2500 2030-11-01 20:15:00 5 3 4000 2030-11-30 09:45:00 6 5 3600 2030-10-15 23:30:00
各カラムには、次の情報が入っています。
| カラム名 | 保存している情報 |
|---|---|
| ID | 注文を識別する番号 |
| USER_ID | 注文した利用者の番号 |
| AMOUNT | 購入金額 |
| ORDER_TIME | 購入した日時 |
今回は、購入日時を保存しているORDER_TIMEカラムから、指定した年・月・日に該当するデータを取得します。
なお、日時の表示形式はOracle Databaseの設定や、使用しているSQL実行ツールによって異なる場合があります。
EXTRACT関数とは
EXTRACT関数は、日付や時刻を保存している値から、指定した部分だけを取り出す関数です。
Oracle DatabaseのDATE型からは、主に次の値を取得できます。
| 指定する値 | 取得できる内容 | 取得例 |
|---|---|---|
| YEAR | 年 | 2030 |
| MONTH | 月 | 12 |
| DAY | 日 | 31 |
例えば、2030-12-31という日付に対してYEARを指定すると、年を表す2030が取得されます。
同じ日付にMONTHを指定した場合は12、DAYを指定した場合は31が取得されます。
EXTRACT関数の書き方
EXTRACT関数は、次のように記述します。
EXTRACT(取得する部分 FROM 日付を保存しているカラム)
日付から年を取得する場合は、次のように記述します。
EXTRACT(YEAR FROM ORDER_TIME)
FROMの左側には、取得したい日付の部分を指定します。
FROMの右側には、日付や時刻を保存しているカラムを指定します。
Oracle DatabaseでDATE型から年・月・日を取得する場合は、次のいずれかを使用します。
- 年を取得する：
YEAR - 月を取得する：
MONTH - 日を取得する：
DAY
ここからは、ordersテーブルを使って実際のSQLを確認していきます。
指定した年のデータを取得する
2030年の注文だけを取得してみましょう。
ORDER_TIMEカラムから年を取り出すため、EXTRACT関数にYEARを指定します。
SELECT * FROM orders WHERE EXTRACT(YEAR FROM order_time) = 2030;
実行結果は次のとおりです。
ID USER_ID AMOUNT ORDER_TIME -- ------- ------ ------------------- 1 2 6000 2030-12-31 11:00:00 2 6 2000 2030-12-25 18:30:00 3 1 3000 2030-12-10 10:00:00 4 4 2500 2030-11-01 20:15:00 5 3 4000 2030-11-30 09:45:00 6 5 3600 2030-10-15 23:30:00
EXTRACT(YEAR FROM order_time)によって、ORDER_TIMEから年だけが取り出されます。
取り出した年が2030と一致するデータだけを、WHERE句で取得しています。
指定した月のデータを取得する
次に、12月の注文だけを取得してみましょう。
月を取り出す場合は、EXTRACT関数にMONTHを指定します。
SELECT * FROM orders WHERE EXTRACT(MONTH FROM order_time) = 12;
実行結果は次のとおりです。
ID USER_ID AMOUNT ORDER_TIME -- ------- ------ ------------------- 1 2 6000 2030-12-31 11:00:00 2 6 2000 2030-12-25 18:30:00 3 1 3000 2030-12-10 10:00:00
EXTRACT(MONTH FROM order_time)によって、ORDER_TIMEから月だけを取り出しています。
ただし、このSQLでは年を指定していません。
テーブルに2029年12月や2031年12月のデータが保存されている場合は、それらのデータも取得されます。
特定の年の12月だけを取得したい場合は、年と月の両方を条件に指定しましょう。
指定した年月のデータを取得する
Oracle DatabaseのEXTRACT関数には、年と月をまとめて取得するYEAR_MONTHという指定方法はありません。
特定の年月に該当するデータを取得する場合は、年と月をそれぞれ条件に指定します。
次のSQLでは、2030年12月の注文だけを取得しています。
SELECT * FROM orders WHERE EXTRACT(YEAR FROM order_time) = 2030 AND EXTRACT(MONTH FROM order_time) = 12;
実行結果は次のとおりです。
ID USER_ID AMOUNT ORDER_TIME -- ------- ------ ------------------- 1 2 6000 2030-12-31 11:00:00 2 6 2000 2030-12-25 18:30:00 3 1 3000 2030-12-10 10:00:00
1つ目の条件では年が2030であることを確認し、2つ目の条件では月が12であることを確認しています。
2つの条件をANDでつなぐことで、両方の条件に一致するデータだけを取得できます。
MySQLではYEAR_MONTHを指定できる場合がありますが、Oracle Databaseでは使用できません。
別のデータベースで使ったSQLをOracle Databaseで実行する場合は、書き方の違いに注意しましょう。
指定した日のデータを取得する
日を取得する場合は、EXTRACT関数にDAYを指定します。
次のSQLでは、日付が31日の注文を取得しています。
SELECT * FROM orders WHERE EXTRACT(DAY FROM order_time) = 31;
実行結果は次のとおりです。
ID USER_ID AMOUNT ORDER_TIME -- ------- ------ ------------------- 1 2 6000 2030-12-31 11:00:00
このSQLでは、年と月を指定していません。
そのため、別の年月に31日のデータが保存されている場合は、そのデータも取得されます。
2030年12月31日のデータだけを取得する場合は、次のように年・月・日のすべてを条件に指定できます。
SELECT * FROM orders WHERE EXTRACT(YEAR FROM order_time) = 2030 AND EXTRACT(MONTH FROM order_time) = 12 AND EXTRACT(DAY FROM order_time) = 31;
年・月・日を別々の列として表示する
EXTRACT関数は、WHERE句の条件だけでなく、取得結果へ年・月・日を表示するときにも使用できます。
SELECT
order_time,
EXTRACT(YEAR FROM order_time) AS order_year,
EXTRACT(MONTH FROM order_time) AS order_month,
EXTRACT(DAY FROM order_time) AS order_day
FROM orders;
実行結果は次のようになります。
ORDER_TIME ORDER_YEAR ORDER_MONTH ORDER_DAY ------------------- ---------- ----------- --------- 2030-12-31 11:00:00 2030 12 31 2030-12-25 18:30:00 2030 12 25 2030-12-10 10:00:00 2030 12 10 2030-11-01 20:15:00 2030 11 1 2030-11-30 09:45:00 2030 11 30 2030-10-15 23:30:00 2030 10 15
ASの後ろに指定しているORDER_YEAR、ORDER_MONTH、ORDER_DAYは、取得結果に表示する列の名前です。
元のORDER_TIMEカラムを変更しているわけではありません。
EXTRACT関数で時・分・秒を取得するときの注意点
Oracle DatabaseのEXTRACT関数では、HOUR、MINUTE、SECONDを指定して時・分・秒を取得することもできます。
ただし、時・分・秒を取得する場合は、基本的にTIMESTAMP型の値が必要です。
Oracle DatabaseのDATE型には時刻も保存できますが、EXTRACT関数ではDATE型からHOUR、MINUTE、SECONDを直接取得できません。
例えば、ORDER_TIMEがTIMESTAMP型であれば、次のように時を取得できます。
SELECT EXTRACT(HOUR FROM order_time) FROM orders;
ORDER_TIMEがDATE型の場合は、このSQLをそのまま実行しないように注意しましょう。
日付や時刻を保存しているカラムのデータ型が分からない場合は、先にテーブルの定義を確認してください。
詳しい仕様については、Oracle公式の「EXTRACT（日時）」でも確認できます。
EXTRACT（日時）｜Oracle Database SQL言語リファレンス
まとめ
Oracle Databaseで日付から年・月・日を取り出す場合は、EXTRACT関数を使用します。
基本的な書き方は次のとおりです。
EXTRACT(取得する部分 FROM 日付を保存しているカラム)
年・月・日を取得するときは、それぞれ次の値を指定します。
- 年：
YEAR - 月：
MONTH - 日：
DAY
Oracle Databaseでは、MySQLのようにYEAR_MONTHを指定して年と月をまとめて取得することはできません。
特定の年月を条件にする場合は、YEARとMONTHをそれぞれ指定し、ANDでつなぎましょう。
また、月や日だけを条件にすると、異なる年や月のデータも取得される場合があります。
どの範囲のデータを取得したいのか確認して、必要な条件を組み合わせてください。
