MySQLで日をまたぐデータを日別にグループ化するにはどうすればいいですか？

Question

通常、00:00:00 ～ 23:59:59 までを1日として扱うところを、
05:00:00 ～ 翌04:59:59 までを1日として扱いたいです。

例えば、2019/10/31 05:00:00 ～ 2019/11/01 04:59:59 までは 2019/10/30 のデータとして扱い、
2019/11/01 05:00:00 ～ 2019/11/02 04:59:59 までを 2019/11/01 のデータとして扱うという具合です。

データベースには次のようなデータが入っています。

> SELECT id, name, display_at FROM programs ORDER BY id DESC LIMIT 10;
+------+--------+---------------------+
| id   | name   | display_at          |
+------+--------+---------------------+
| 8300 | aaaaaa | 2019-11-01 04:37:00 |
| 8299 | aaaaaa | 2019-11-01 04:07:00 |
| 8298 | aaaaaa | 2019-11-01 03:37:00 |
| 8297 | aaaaaa | 2019-11-01 03:34:00 |
| 8296 | aaaaaa | 2019-11-01 02:29:00 |
| 8295 | aaaaaa | 2019-11-01 01:59:00 |
| 8294 | aaaaaa | 2019-11-01 01:29:00 |
| 8293 | aaaaaa | 2019-11-01 00:54:00 |
| 8292 | aaaaaa | 2019-10-31 23:59:00 |
| 8291 | aaaaaa | 2019-10-31 23:00:00 |
| 8290 | aaaaaa | 2019-10-31 22:00:00 |
+------+--------+---------------------+

普通に日別でグループ化すると、次のようなSQLになると思います。

SELECT
  DATE_FORMAT(display_at, '%Y') AS d_year,
  DATE_FORMAT(display_at, '%c') AS d_month,
  DATE_FORMAT(display_at, '%e') AS d_day
FROM
  programs
  GROUP BY DATE_FORMAT(display_at, '%Y-%m-%d')
;
+---------+----------+--------+
| d_year  | d_month  | d_day  |
+---------+----------+--------+
| 2019    | 10       | 31     |
| 2019    | 11       | 1      |
+---------+----------+--------+

このように、日をまたいだデータをグループ化するにはどのようなSQLを組めば良いのでしょうか？
非常に困っています。皆様どうかよろしくお願いいたします。

Answer 1

かんたんにやるならdisplay_atをDATE_SUBで5時間引いた値でグループ化する

Answer 2

生成列を利用するのが賢明です
(5.7からの機能なので5.6日本語マニュアルには記述がない)

create table tbl(
id int,
name varchar(10),
display_at datetime,
d date generated always as (date_format(display_at - interval 5 hour,'%Y-%m-%d')),
index (d)
);
insert into tbl(id,name,display_at) values
(8300,"aaaaaa","2019-11-01 04:37:00"),
(8299,"aaaaaa","2019-11-01 04:07:00"),
(8298,"aaaaaa","2019-11-01 03:37:00"),
(8297,"aaaaaa","2019-11-01 03:34:00"),
(8296,"aaaaaa","2019-11-01 02:29:00"),
(8295,"aaaaaa","2019-11-01 01:59:00"),
(8294,"aaaaaa","2019-11-01 01:29:00"),
(8293,"aaaaaa","2019-11-01 00:54:00"),
(8292,"aaaaaa","2019-10-31 23:59:00"),
(8291,"aaaaaa","2019-10-31 23:00:00"),
(8290,"aaaaaa","2019-10-31 22:00:00");

• 集計

select d,count(*) from tbl group by d;

※上記dにはインデックスが貼ってあるので高速に集計が可能です

Comments

[qwe001]

yambejp様
大変具体的なご回答、誠にありがとうございます。
言い忘れていましたが、私の環境はMySQL 5.5.44という化石みたいな環境ですので、
生成列という便利機能は使えませんでした。
ですが、頂いた回答内容を参考にして、次のSQL文で期待した通りの結果が得られました。

```
SELECT
DATE_FORMAT(display_at - INTERVAL 5 HOUR, '%Y') AS d_year,
DATE_FORMAT(display_at - INTERVAL 5 HOUR, '%c') AS d_month,
DATE_FORMAT(display_at - INTERVAL 5 HOUR, '%e') AS d_day
FROM
programs
GROUP BY (DATE_FORMAT(display_at - INTERVAL 5 HOUR, '%Y-%m-%d'))
;
```

回答ありがとうございます。またよろしくお願いいたします。

Answer 3

会計年度などもそうですが、本来とはずらした扱いを日時に対して行いたい場合は、ずらす期間分を一律演算で差し引いた結果を扱うようにすれば良い事になります。

日時に関しては、閏年や大小月などがありますので、日時専用の演算を用います。

ADDDATEやintervalを直接用いた演算等で行います。

Answer 4

ヒントは INTERVAL