【MySQL】DATE_FORMATの基本的な使い方と応用をわかりやすく解説。

MySQLなどデータベースを扱う上で日付を指定しデータを取得することは度々あります。

MySQLではDATEなどの時間型をより簡単に扱うためにDATE_FORMATやTIME_FORMATというものが用意されています。

今回はこの中で「DATE_FORMAT」について解説していきたいと思います。

フォーマット形式

DATE_FORMATで使うフォーマット形式には様々なものがあり、これを指定子と呼びます。

指定子を以下に簡単にまとめてみました。

DATE_FORMATの使い方

それでは具体的な使い方について見ていきましょう。

DATE_FORMATの基本構文は以下の通りです。

DATE_FORMAT(date,format)

実際のコードでフォーマットをそれぞれ確認してみましょう。

// 簡略曜日名
SELECT DATE_FORMAT('2019-12-30', '%a');
+--------------------------+
| DATE_FORMAT(now(), '%a') |
+--------------------------+
| Mon                      |
+--------------------------+

// 年間通算日
SELECT DATE_FORMAT('2019-12-30', '%j');
+--------------------------+
| DATE_FORMAT(now(), '%j') |
+--------------------------+
| 364                      |
+--------------------------+

// 現在日時時間から現在時刻だけを抽出
SELECT DATE_FORMAT(now(), '%H:%i:%s');
+--------------------------------+
| DATE_FORMAT(now(), '%H:%i:%s') |
+--------------------------------+
| 05:01:12                       |
+--------------------------------+

// 2018年1月1日は月曜日のため、フォーマット次第では以下のようにズレが生じる
SELECT DATE_FORMAT('2018-01-01', '%X %V');
+------------------------------------+
| DATE_FORMAT('2018-01-01', '%X %V') |
+------------------------------------+
| 2017 53                            |
+------------------------------------+

// カラムを区切って表示させるには以下のように記述する
SELECT DATE_FORMAT(now(), '%Y年%m月%d日') AS '年月日',
    -> DATE_FORMAT(now(), '%T') AS '現在時刻(24時間)';
+----------------+------------------+
| 年月日              | 現在時刻(24時間)            |
+----------------+------------------+
| 2019年12月30日      | 05:13:46         |
+----------------+------------------+

DATE_FORMATの応用

DATE_FORMATを使って年間カレンダーを作る方法をご紹介します。

CREATE TABLE calendar_table(
    id INT(11) AUTO_INCREMENT NOT NULL PRIMARY KEY,
    date DATE,
    day_of_week VARCHAR(20)
);
Query OK, 0 rows affected (0.024 sec)

SHOW COLUMNS FROM calendar_table;
+-------------+-------------+------+-----+---------+----------------+
| Field       | Type        | Null | Key | Default | Extra          |
+-------------+-------------+------+-----+---------+----------------+
| id          | int(11)     | NO   | PRI | NULL    | auto_increment |
| date        | date        | YES  |     | NULL    |                |
| day_of_week | varchar(20) | YES  |     | NULL    |                |
+-------------+-------------+------+-----+---------+----------------+
3 rows in set (0.018 sec)

INSERT INTO calendar_table
	(date, day_of_week)
VALUES(
	@f := DATE_FORMAT(CURDATE(), '2019-01-01'),
	@W := DATE_FORMAT(@f, '%W')
)
UNION ALL
SELECT
	@f := DATE_FORMAT(DATE_ADD(@f, INTERVAL 1 DAY), '%Y-%m-%d'),
	@w := DATE_FORMAT(@f, '%W')
FROM
	information_schema.COLUMNS
WHERE
	@f < LAST_DAY(DATE_FORMAT(CURDATE(), '2019-12-01'))
;
Query OK, 365 rows affected (0.098 sec)
Records: 365  Duplicates: 0  Warnings: 0

INSERT INTOで予め用意しておいたテーブルに基点となるデータを追加し、WHERE句を使ってループ処理させています。

UNION ALLを使用することによって、”INSERT INTO”とその次のクエリである”SELECT”を結合させることが出来るようになります。

この時、基点は”INSERT INTO”となっているため、SELECT + WHEREでループする数だけINSERTする仕組みとなります。

まとめ

いかがでしたか？

今回はDATE_FORMATについて解説してきましたが、意外にも使い方に幅があることがお判りいただけたと思います。

データベースの設計をする場合にはMySQL内で全てのクエリを実行させるため、基本を確実に覚えるようにしましょう。