More than 5 years have passed since last update.

【MySQL】timestamp型・datetime型のデータを使って、日付ごとにgroup byする【substring_index】

Last updated at 2018-02-02Posted at 2017-11-20

2018/02/02 追記：substring_indexの内部の動きがものすごく間違っていたので修正

先日、「2017-11-19 21:18:52」というようなtimestamp型のデータを使って、日付ごとにカウントを取ってほしいと言われ、思わず「そんな無茶な」と言った私を救ってくれた関数「substring_index」の使い方についてです。

やりたいこと

以下のようなテーブルがあったとします。

日付ごとのレコード数のカウントを取りたいので、以下のような結果を出したい

「2017-11-19」に作成されたデータは、IDが１・４・６の３つです。
日付ごとにカウントしたいので、こんなselect文を書いてみました

select createDate, count(*) from テーブル名 group by createDate;

このselect文で、createDateごとにカウントが取れるはず
しかし、createDateはtimestamp型なので、このような結果になってしまいます
これでは、やりたいことと違いますね

内部の動きはこんな感じ

第２引数で指定された文字を使って、 ~~第１引数で指定した文字列全体を区切る。~~ 第１引数の中で、１個目の第２引数が出てくるところまで、２個目の第２引数が出てくるところまで・・・と少しずつ長い文字列を取得していく。
~~先頭から~~ 最初に取得した順にindexを振る（先頭はですよー）。
第３引数で指定したindexの文字を取得する。

なので、substring_index('2017-11-19 05:10:04', ' ', 1)ってやると、

再度SQLを書いてみる（｀・ω・´）b

select substring_index(createDate, ' ', 1), count(*)
from テーブル名
group by substring_index(createDate, ' ', 1);

無事、想定通りの結果が返ってきました

めでたし、めでたし