TO_DATE関数のフォーマット
TO_DATE関数とは、文字列で指定した日付をDATEデータ型に変換する関数のことです。
TO_DATE関数は、次のように記述して使います。
TO_DATE(char, format);
関数名は大文字・小文字の区別はしないため、「TO_DATE」と表現しても「to_date」としても問題ありません。
TO_DATE関数の引数
TO＿DATE関数の引数は「char」と「format」の2種類です。
それぞれ次のような意味をもっています。
- char：日付に当てはまる文字列です。
- format：charの「書式」が指定できる日時書式です。省略した場合はデフォルト書式が設定されます。
formatには、次の書式が使用可能です。
| 指定子 | 説明 |
|---|---|
| YYYY | 西暦4桁 |
| YY | 西暦年下2ケタ |
| MM | 月2桁 |
| DD | 日2桁 |
| HH | 時間2桁（01から12） |
| HH24 | 時間2桁 24時間表記（00～24） |
| MI | 分2桁 |
| SS | 秒2桁 |
PostgreSQLでもTO_DATE関数は使用可能
TO_DATE関数は「Oracle」で使われるSQL関数ですが、PostgreSQLでも使用可能です。
具体的な使い方については「TO_DATE関数の使用例」で見ていきましょう。
MySQLではSTR_TO_DATE関数を使用
MySQLではTO_DATE関数が使えませんが、代わりに「STR_TO_DATE関数」を使います。
MySQLで使用する書式は、次の通りです。
| 指定子 | 説明 |
|---|---|
| %a | 簡略曜日名 (Sun..Sat) |
| %b | 簡略月名 (Jan..Dec) |
| %c | 月、数字 (0..12) |
| %D | 英語のサフィクスを持つ日付 (0th, 1st, 2nd, 3rd, …) |
| %d | 日、数字 (00..31) |
| %e | 日、数字 (0..31) |
| %f | マイクロ秒 (000000..999999) |
| %H | 時間 (00..23) |
| %h | 時間 (01..12) |
| %I | 時間 (01..12) |
| %i | 分、数字 (00..59) |
| %j | 年間通算日 (001..366) |
| %k | 時 (0..23) |
| %l | 時 (1..12) |
| %M | 月名 (January..December) |
| %m | 月、数字 (00..12) |
| %p | AM または PM |
| %r | 時間、12 時間単位 (hh:mm:ss に AM または PM が続く) |
| %S | 秒 (00..59) |
| %s | 秒 (00..59) |
| %T | 時間、24 時間単位 (hh:mm:ss) |
| %U | 週 (00..53)、日曜日が週の初日、WEEK() モード 0 |
| %u | 週 (00..53)、日曜日が週の初日、WEEK() モード 1 |
| %V | 週 (01..53)、日曜日が週の初日、WEEK() モード 2、%X とともに使用 |
| %v | 週 (01..53)、月曜日が週の初日、WEEK() モード 3、%x とともに使用 |
| %W | 曜日名 (Sunday..Saturday) |
| %w | 曜日 (0=Sunday..6=Saturday) |
| %X | 年間の週、日曜日が週の初日、数字、4 桁、%V とともに使用 |
| %x | 年間の週、日曜日が週の初日、数字、4 桁、%v とともに使用 |
| %Y | 年、数字、4 桁 |
| %y | 年、数字 (2 桁) |
| %% | リテラル 「%」 文字 |
| %x | x (上記にないすべての 「x」) |
※参考：12.7 日付および時間関数
こちらも詳しい使い方は「TO_DATE関数の使用例」で解説します。
TO_DATE関数の使用例
ここでは、TO_DATE関数の使い方をサンプルを用いて確認していきましょう！
MySQLでは、STR_TO_DATE関数の使い方を見ていきます。
文字列を日付型(時間)に変換
任意の文字列を日付型に変換するには、次のように記述可能です。
mydb=# SELECT TO_DATE('20200811', 'YYYYMMDD');
to_date
------------
2020-08-11
(1 row)
「20200811」という文字列を、YYYYMMDDの書式で日付型に変換できました。
日付型に変換すると、「YYYY-MM-DD」で表現されます。
テーブルのカラムを日付型に変換したい場合も見てみましょう。
次のようなテーブルを作成し、データを追加しました。
mydb=# CREATE TABLE tb0811(no integer, name varchar(20), date varchar(20)); mydb=# SELECT * FROM tb0811; no | name | date ----+----------+------------ 1 | Satou | 1992/01/17 2 | Tanaka | 1995/05/22 3 | Yamamoto | 1996/10/04 (3 rows)
このテーブルの「date」カラムを日付型に変換してデータを取得してみます。
mydb=# SELECT no, name, TO_DATE(date, 'YYYY/MM/DD') FROM tb0811; no | name | to_date ----+----------+------------ 1 | Satou | 1992-01-17 2 | Tanaka | 1995-05-22 3 | Yamamoto | 1996-10-04 (3 rows)
「date」カラムがTO_DATEで日付型に変換できました！
では、MySQLでも同じように任意の文字列を日付型に変換してみます。
mysql> SELECT STR_TO_DATE('20200811', '%Y%m%d');
+-----------------------------------+
| STR_TO_DATE('20200811', '%Y%m%d') |
+-----------------------------------+
| 2020-08-11 |
+-----------------------------------+
1 row in set (0.00 sec)
このように、日付型に変換できました。
なお、任意の文字列が変換したい書式と異なっている場合や、区切り文字の有無によっては正しく変換できないので注意しましょう。
mysql> SELECT STR_TO_DATE('20200811', '%Y%M%d');
+-----------------------------------+
| STR_TO_DATE('20200811', '%Y%M%d') |
+-----------------------------------+
| NULL |
+-----------------------------------+
1 row in set, 2 warnings (0.00 sec)
mysql> SELECT STR_TO_DATE('20200811', '%Y/%m/%d');
+-------------------------------------+
| STR_TO_DATE('20200811', '%Y/%m/%d') |
+-------------------------------------+
| NULL |
+-------------------------------------+
1 row in set, 2 warnings (0.00 sec)
日付型(時間)を文字列に変換する
TO_DATE関数は、「文字列→日付型」に変換する関数でした。
逆に、「日付型→文字列」に変換したい場合は「TO_CHAR関数」を使いましょう。
mydb=# SELECT TO_CHAR(NOW(), 'YYYY/MM/DD HH24:MI:SS');
to_char
---------------------
2020/08/11 23:18:35
(1 row)
上記は、現在の日時を文字列型に変換しています。
このように、TO_CHAR関数を使えば日付型を文字列型に変換可能です。
時間・時刻に変換
TO_TIMESTAMP関数を使うことで、任意の文字列を日時に変換できます。
サンプルで表示を確認してみましょう。
mydb=# SELECT TO_TIMESTAMP('20200812125200', 'YYYYMMDDHH24MISS');
to_timestamp
------------------------
2020-08-12 12:52:00+09
(1 row)
上記のように、時間まで表示できているのがわかります。
和暦・西暦に変換
OracleDBでは、次のようにnls_paramを使うことで和暦を西暦に表示することも可能です。
TO_DATE('H31-01-01', 'EYY-MM-DD', 'NLS_CALENDAR=''JAPANESE IMPERIAL''')
また、TO_CHAR関数を使うことで西暦を和暦に変換できます。
詳しくは、次のサイトを参考にしてみてください。
TO_DATE関数で日付を計算する
TO_DATE関数で日付を計算するのは非常に簡単で、通常の演算のように「+ 値」を記述すればOKです。
サンプルで計算結果を確認してみましょう。
mydb=# SELECT to_date('20200812', 'YYYYMMDD') + 5 as to_date;
to_date
------------
2020-08-17
(1 row)
このように、2020年8月12日の5日後である2020年8月17日が取得できました。
365を足せば、1年後である2021年8月12日が取得できます。
mydb=# SELECT to_date('20200812', 'YYYYMMDD') + 365 as to_date;
to_date
------------
2021-08-12
(1 row)
また、値を引くことで日付を遡ることも可能です。
mydb=# SELECT TO_DATE('20200812', 'YYYYMMDD') - 10 AS to_date;
to_date
------------
2020-08-02
(1 row)
TO_DATE関数をWHERE句で使う
TO_DATE関数をWHERE句の比較で使う方法を見ていきましょう。
先ほど用意した「tb0811」テーブルを使い、データを取得してみます。
mydb=# SELECT * FROM tb0811 WHERE '1995-05-22' = TO_DATE(date, 'YYYY/MM/DD'); no | name | date ----+--------+------------ 2 | Tanaka | 1995/05/22 (1 row)
このように、取得したい日付とデータを比較して取得が可能です。
また、下記のように指定日時以降のデータだけを取得するといった比較方法もできます。
mydb=# SELECT * FROM tb0811 WHERE '19930101' <= TO_DATE(date, 'YYYY/MM/DD'); no | name | date ----+----------+------------ 2 | Tanaka | 1995/05/22 3 | Yamamoto | 1996/10/04 (2 rows)
特定の日時前後のデータを取得したい場合などに、活用できるでしょう。
まとめ
SQLのTO_DATE関数について解説しました。
TO_DATE関数は、任意の文字列を日付型に変換できる関数になっています。
MySQLではSTR_TO_DATE関数を使うなど、DBMSによって使用する関数は異なります。
ぜひ、この記事を参考にTO_DATE関数の使い方をマスターしてください。
