技術系TIPS
PR

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

saratogax
記事内に商品プロモーションを含む場合があります

MySQL の日付フォーマットを使いたい時によく忘れてそうな関数。

そんな関数を備忘録として残しておきたいと思います。

日付フォーマット

下記のような 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 |
+---------+---+

日付関数

もちろん、データベースからそのまま datetime 型の値を取得して、アプリ側で整形してもいいのですが、日付部分がハイフン区切りで欲しいとか、ある程度仕様がガチっと決まっている時はデータベース側でフォーマット指定した方が楽です。

あとは、datetime 型を日付毎にグルーピングして集計したい場合にも活用できます。

datetimeでミリ秒を扱う

よく、datetime 型なのに時間部分は固定で 00:00:00 しか入らないシステムに遭遇しますが、日付を持つカラムの型は確かに悩ましい部分もありますね。

YYYYMMDD を想定した char(8) や YYYY-MM-DD を文字列で持つような varchar(10) など、プロジェクトによって様々ですが、プロジェクト内で統一されていれば問題ないと思います。

いっそのこと date 型でいいような気もしますが、意外に date 型を使っているプロジェクトに遭遇する確率が低いのですよね。

日付が入らない場合に、空文字にしたいか NULL にしたいかなど、案件の要求が異なるのも致し方ないことですが。

ABOUT ME
saratoga
saratoga
フリーランスエンジニア
仕事にも趣味にも IT を駆使するフリーランスエンジニア。技術的な TIPS や日々の生活の中で深堀りしてみたくなったことを備忘録として残していきます。
記事URLをコピーしました