MySQLのdatetime型のカラムから日付部分のみを取得する

MySQLのdatetime型のカラムから日付部分のみを取得する

MySQLMySQLのdatetime型のカラムから日付部分のみを取得する

MySQL の日付フォーマットを使いたい時によく忘れてそうな関数。PHP でアプリ側を書いている時の date() 関数の引数とフォーマット指定の順番が違うので間違えがちです。もちろん、データベースからそのまま datetime 型の値を取得して、アプリ側で整形してもいいのですが、日付部分がハイフン区切りで欲しいとか、ある程度仕様がガチっと決まっている時はデータベース側でフォーマット指定した方が楽です。

あとは、datetime 型を日付毎にグルーピングして集計したい場合にも活用できます。よく、datetime 型なのに時間部分は固定で 00:00:00 しか入らないシステムに遭遇しますが、日付を持つカラムの型は確かに悩ましい部分もありますね。YYYYMMDD を想定した char(8) や YYYY-MM-DD を文字列で持つような varchar(10) など、プロジェクトによって様々ですが、プロジェクト内で統一されていれば問題ないと思います。いっそのこと date 型でいいような気もしますが、意外に date 型を使っているプロジェクトに遭遇する確率が低いのですよね。日付が入らない場合に、空文字にしたいか NULL にしたいかなど、案件の要求が異なるのも致し方ないことですが。

日付フォーマット備忘録

さて、本題へ。下記のような datetime 型のカラムを参照した SELECT 文があります。

SELECT hoge_date FROM hogehoge;

+---------------------+
| 2015-10-01 07:00:00 |
| 2015-10-02 18:00:00 |
+---------------------+

DATE_FORMAT 関数で年月日のみを取得するフォーマットを指定して参照してみます。下記のパターンは年月日をハイフン区切りにしたものです。

mysql> SELECT DATE_FORMAT(hoge_date, '%Y-%m-%d') AS tmp_date FROM hogehoge;

+------------+
| 2015-10-01 |
| 2015-10-02 |
+------------+

年月指定にすると下記の通り。

mysql> SELECT DATE_FORMAT(hoge_date, '%Y-%m') AS tmp_date FROM hogehoge;

+---------+
| 2015-10 |
| 2015-10 |
+---------+

今後は年月日を区切り文字なしで連結したもの。用途に合わせてフォーマットを簡単に変更できるのはいいですね。

mysql> SELECT DATE_FORMAT(hoge_date, '%Y%m%d') AS tmp_date FROM hogehoge;

+----------+
| 20151001 |
| 20151002 |
+----------+

mysql> SELECT DATE_FORMAT(hoge_date, '%Y%m%d%H') AS tmp_date FROM hogehoge;

+------------+
| 2015100107 |
| 2015100218 |
+------------+

年月単位でグルーピング化することで、同じ月のカウントや同じ月の別カラムの合計値など、日付フォーマットを揃えることでそういった集計にも使えます。

mysql> SELECT DATE_FORMAT(hoge_date, '%Y-%m') AS tmp_date, COUNT(*) AS tmp_count FROM hogehoge GROUP BY DATE_FORMAT(hoge_date, '%Y-%m');

+---------+---+
| 2015-10 | 2 |
+---------+---+

最終更新日:

関連記事

人気記事

新着情報