MySQLにてdatetime型のカラムを整形し「年月日のみ形式」または「秒のみ省いた形式」で表示する方法

MySQLにてdatetime型のカラムに登録日時等を格納しており、管理画面側では「秒のみ省いた形式」で表示、サイト側では「年月日のみ形式」で表示のように表示形式を変えたい場合がよくある。このような場合PHPで対応していたがMySQLで対応した方がスマートに記述できそう。以下に対応方法をメモ。

テーブル構造とデータ内容

mysql> SHOW COLUMNS FROM `test_table`;
+-------+----------+------+-----+---------+----------------+
| Field | Type     | Null | Key | Default | Extra          |
+-------+----------+------+-----+---------+----------------+
| id    | int      | NO   | PRI | NULL    | auto_increment |
| ymd   | datetime | NO   |     | NULL    |                |
+-------+----------+------+-----+---------+----------------+

mysql> SELECT * FROM `test_table`;
+----+---------------------+
| id | ymd                 |
+----+---------------------+
|  1 | 2024-01-01 01:01:01 |
|  2 | 2024-01-02 02:02:02 |
|  3 | 2024-01-03 03:03:03 |
|  4 | 2024-01-04 04:04:04 |
|  5 | 2024-01-05 05:05:05 |
+----+---------------------+

対応方法

年月日のみ形式

SELECT DATE_FORMAT(`ymd`, '%Y-%m-%d') AS ymd 
FROM `test_table`;

+------------+
| ymd        |
+------------+
| 2024-01-01 |
| 2024-01-02 |
| 2024-01-03 |
| 2024-01-04 |
| 2024-01-05 |
+------------+

秒のみ省いた形式

SELECT DATE_FORMAT(`ymd`, '%Y-%m-%d %H:%i') AS ymd 
FROM `test_table`;

+------------------+
| ymd              |
+------------------+
| 2024-01-01 01:01 |
| 2024-01-02 02:02 |
| 2024-01-03 03:03 |
| 2024-01-04 04:04 |
| 2024-01-05 05:05 |
+------------------+

リファレンス

DATE_FORMAT

https://dev.mysql.com/doc/refman/8.0/ja/date-and-time-functions.html#function_date-format

所感

DATE_FORMATってなんか見たことあるなと思ったら過去にdatetime型のカラムから秒は省いて検索する方法というメモにて使用していた。

その時はWHERE句で使っていたけど、SELECT時のカラム指定でも使えて便利な関数なので覚えておきたい。