SQLの日付フォーマットを把握しよう！データ型と変換方法について

データベースには日付に関する情報を登録出来ることはご存知の方も多いかと思いますが、登録出来るフォーマットにはいくつものタイプがあります。

本記事では、SQLで利用する日付フォーマットについて、格納するデータ型と取得時のフォーマット変換方法をご紹介していきたいと思います。

SQLの日付フォーマット【データ型】

まずはSQLの日付フォーマットとして、各種データ型についてご紹介していきたいと思います。

ポテパンダの一言メモ

今回はMySQLで利用出来る日付・時刻データ型の中でよく利用されるフォーマットについて掲載します。

DATE型

DATE型は日付を格納する際、最も一般的に利用される日付型のフォーマットです。

デフォルトの設定として「”YYYY-MM-DD”(2020-07-23)」のように格納されます。

サンプル

まずはDATE型のカラムを持ったテーブルを作成してみましょう。

CREATE TABLE test1(col_date DATE);

では作成したテーブルにNOW関数を利用して現在日時のデータを追加してみましょう。

INSERT INTO test1 VALUES(now());

実行したデータを取得してみると下記のように表示されます。

+------------+
| col_date   |
+------------+
| 2020-07-23 |
+------------+

DATE型で許容されている入力フォーマット

DATE型では「”YYYY-MM-DD”」以外の許容されたフォーマットで入力した場合にも、自動的に「”YYYY-MM-DD”」に変換してくれる機能が備わっています。

試しに2つのデータフォーマットを指定してDATE型に追加してみます。

INSERT INTO test1 VALUES("200723");
INSERT INTO test1 VALUES("2020/07/23");

実行した結果は下記の通りです。

+------------+
| col_date   |
+------------+
| 2020-07-23 |
| 2020-07-23 |
| 2020-07-23 |
+------------+

入力したフォーマットが自動的にDATE型デフォルトの「”YYYY-MM-DD”」に変換されていることをご確認頂けます。

DATETIME型

DATETIME型は、「日付+時刻」を格納する際に利用されるフォーマットです。

デフォルトの設定として、「”YYYY-MM-DD HH:MM:SS”」のフォーマットで格納されます。

サンプル

まずDATETIME型のテーブルを作成してみましょう。

CREATE TABLE test2(col_datetime DATETIME);

では作成したテーブルにNOW関数を利用して現在日時のデータを追加してみましょう。

INSERT INTO test2 VALUES(now());

実行した結果が下記の通りです。

+---------------------+
| col_datetime        |
+---------------------+
| 2020-07-23 13:54:51 |
+---------------------+

先程のDATE型の場合と異なり、日付だけでなく日時までデータとして追加されていることをご確認頂けます。

DATETIME型で許容されている入力フォーマット

DATETIME型もDATE型同様に、いくつかの入力フォーマットでデータを追加することが可能です。

では今回もいくつかのフォーマットで入力してみましょう。

INSERT INTO test2 VALUES("200723120010");
INSERT INTO test2 VALUES("2020/07/23 13*00*10");

実行した結果が下記の通りです。

+---------------------+
| col_datetime        |
+---------------------+
| 2020-07-23 13:54:51 |
| 2020-07-23 12:00:10 |
| 2020-07-23 13:00:10 |
+---------------------+

TIMESTAMP型

TIMESTAMP型は、フォーマット自体はDATETIME型と同じ日付と時刻を格納することが可能ですが、許容されているデータ範囲が異なります。

TIMESTAMP型の場合「’1970-01-01 00:00:01′ UTC ～ ‘2038-01-19 03:14:07′ UTC」の値が設定可能です。

一方、DATETIME型の場合には「’1000-01-01 00:00:00’ ～ ‘9999-12-31 23:59:59’」の値が許容されています。

サンプル

まずTIMESTAMP型のテーブルを作成してみましょう。

CREATE TABLE test3(col_timestamp TIMESTAMP);

では作成したテーブルにDATETIME型同様、NOW関数を利用して現在日時のデータを追加してみましょう。

INSERT INTO test3 VALUES(now());

実行した結果が下記の通りです。

+---------------------+
| col_timestamp       |
+---------------------+
| 2020-07-23 14:21:56 |
+---------------------+

DATETIME型と同じフォーマットでデータが追加されていることをご確認頂けます。

ポテパンダの一言メモ

今回の主旨と外れるため、DATETIME型とTIMESTAMP型の違いを詳しく解説することはしませんが、厳密には扱いが違います。
例えば、NULLを設定した場合や日付として相応しくない値を設定した場合などの挙動が異なるため、注意が必要です。

SQLの日付フォーマット【変換】

ここまでMySQLで利用可能な日付データ型について解説してきましたが、もうひとつ覚えておきたい日付フォーマットにSELECTしたデータのフォーマット変換が挙げられます。

DATE_FORMAT関数

MySQLでは日付フォーマットの変換用にDATE_FORMAT関数を利用することが可能です。

SELECT DATE_FORMAT(日付, 変換フォーマット)

変換フォーマットに利用出来る文字で頻繁に利用されるものを、下記にピックアップしています。

サンプル

では実際にいくつかのサンプルで確認してみましょう。

SELECT DATE_FORMAT(now(), '%Y');

現在時刻をnow関数で取得し、4桁の年にフォーマットするよう指定した結果が下記の通りです。

+--------------------------+
| DATE_FORMAT(now(), '%Y') |
+--------------------------+
| 2020                     |
+--------------------------+

続いて現在時刻を年月日まで取得してみたいと思います。

年に関しては2桁で取得するように指定しました。大文字と小文字で意味が異なるため注意が必要です。

SELECT DATE_FORMAT(now(), '%y%m%d');

実行した結果が下記の通りです。

+------------------------------+
| DATE_FORMAT(now(), '%y%m%d') |
+------------------------------+
| 200723                       |
+------------------------------+

次は、現在日時を読みやすいように「/」と「:」で区切って表示してみたいと思います。

SELECT DATE_FORMAT(now(), '%Y/%m/%d %k:%i:%s');

実行した結果が下記の通りです。

+-----------------------------------------+
| DATE_FORMAT(now(), '%Y/%m/%d %k:%i:%s') |
+-----------------------------------------+
| 2020/07/23 15:12:18                     |
+-----------------------------------------+

テーブルから抽出したデータにももちろん対応

DATE_FORMAT関数はテーブルに格納されたデータにももちろん適用可能です。

上記のTIMESTAMP関数のサンプルで追加したデータを取得してDATE_FORMAT関数でフォーマット変換してみましょう。

SELECT DATE_FORMAT(col_timestamp, '%y/%m/%d %k%i%s') from test3;

実行した結果が下記の通りです。

+-----------------------------------------------+
| DATE_FORMAT(col_timestamp, '%y/%m/%d %k%i%s') |
+-----------------------------------------------+
| 20/07/23 142156                               |
+-----------------------------------------------+

ポテパンダの一言メモ

サンプルでは年を4桁から2桁に変換し年月日の区切りを「/」に変換しています。
更に時刻の区切り文字の「:」を削除するようフォーマット指定しました。

さいごに：SQLの日付フォーマットはミスの起こりやすいポイント

本記事では、SQLの日付フォーマットについてご紹介してきました。

一見取得さえ出来ていればフォーマットはなんでも良いと思いがちですが、SQLのデータは取得して終わりではなくプログラムの処理として利用されることも少なくありません。

フォーマットにより後続処理に影響を与える可能性もありますので、ご自身の作成しているプログラムで利用するべきフォーマットは何かをしっかりと理解した上で使用するように心掛けましょう。