MySQLで(日付をまたぐ)24時間表記ごとのレコードを正しくORDER BYするには

はじめに

【MySQLでユーザー抽出をするときのこと】

• 基本的には日付ベースとかでソートすると思うけど、

  • プロモーションがはじまった
  • この機能をリリースした　 ときとかで速報に近いものを出したいときあるじゃん。(たぶん)

• hour(<datetime>なカラム)だと日付合わせられないし、日付をまたいじゃうと時間に統合されちゃう

やりたいこと

• SELECT文でデータをカウントする
• X日分のデータを24時間ごとに見たい
• それを1つのクエリで済ましたい
• スプレッドシートに展開するかもだけど、抽出時に昇順で並べておきたい

んじゃどうするの？

結論：
DATE_FORMATでデータを成形します。

※元々datetimeがyyyy-mm-dd hh:mm:ssで入っていると仮定し
(例：2016-05-16 23:12:47)
アウトプットとして欲しいのはmm/dd hh:00を複数日程分とします。
(例：05/16の23時台のデータ)

やること

SELECT DATE_FORMAT(<datetime>,"%m/%d %H:00")

を文頭に入れる。

重要:
ここの「H」は大文字でないといけません。
なぜかと言うと、小文字の「h」を使うと12h換算で返ってくるためです。

「H」のとき

05/16 00:00
05/16 01:00
05/16 02:00
05/16 03:00
05/16 04:00
05/16 05:00
05/16 06:00
05/16 07:00
05/16 08:00
05/16 09:00
05/16 10:00
05/16 11:00
05/16 12:00
05/16 13:00
05/16 14:00
05/16 15:00
05/16 16:00
05/16 17:00
05/16 18:00
05/16 19:00
05/16 20:00
05/16 21:00
05/16 22:00
05/16 23:00
05/17 00:00

きちんと 24hで返ってくる

「h」のとき

05/16 01:00
05/16 02:00
05/16 03:00
05/16 04:00
05/16 05:00
05/16 06:00
05/16 07:00
05/16 08:00
05/16 09:00
05/16 10:00
05/16 11:00
05/16 12:00

※AM1:00のデータもPM1:00(=13:00)のデータも01:00に統合されちゃいます。。

「k」のとき

ちなみに「k」でもいけません。

05/16 0:00
05/16 10:00
05/16 11:00
05/16 12:00
05/16 13:00
05/16 14:00
05/16 15:00
05/16 16:00
05/16 17:00
05/16 18:00
05/16 19:00
05/16 1:00
05/16 20:00
05/16 21:00
05/16 22:00
05/16 23:00
05/16 2:00
05/16 3:00
05/16 4:00
05/16 5:00
05/16 6:00
05/16 7:00
05/16 8:00
05/16 9:00

※24hで返ってくるけど、10の位を基準にソートされるので、順番になりません。

その他

当然だけど、文頭で

SELECT DATE_FORMAT(<datatime>,"%m/%d %H:00")

を指定しているので

WHERE以下の条件に

GROUP BY
    DATE_FORMAT(<datetime>,"%m/%d %H:00") 
ORDER BY
    DATE_FORMAT(<datetime>,"%m/%d %H:00")

をいれてくださいね。

相性が良さそうなもの

COUNT : その時間帯に登録したユーザー,送られたメッセージなどをカウントする機会は多そう。

BETWEEN <Aの期間>　AND　<Bの期間>：特定の期間を指定したい機会はあるでしょう。等号/不等号もしかり。

SUM(IF(条件,1,0)) : リリース後の影響を見る際などに、2パターンのデータとったりとかか使えそう。

実際に使えそうな形に成形

ユースケース：
・messagesというメッセージ情報を保存するテーブル
・createdというカラムにdatetimeの形(yyyy-mm-dd hh-mm-ss)でレコードが入る
・deletedというカラムで'0'は消えてない,'1'は消えている

⇒では、ここから特定期間の時間帯ごとの傾向を見てみる

SELECT
    DATE_FORMAT(created,"%m/%d %H:00") AS created_time,
    SUM(IF(deleted = "0",1,0)) AS valid_messages,
    SUM(IF(deleted = "1",1,0)) AS invalid_messages,
FROM 
    messages
WHERE 
    created BETWEEN '2014-05-01' AND '2014-05-07'
GROUP BY
    DATE_FORMAT(created,"%m/%d %H:00") 
ORDER BY
    DATE_FORMAT(created,"%m/%d %H:00")
     ;

全部適当に書いたｗ
なんかこんな感じで使えればm(_ _)m