mysql查询时间段内所有日期 和 小时_mysql 查询对应小时段的数据

返回时间段内所有日期 天

select DATE_FORMAT(date_add('2022-06-01 00:00:00', interval row DAY),'%Y-%m-%d') date from
 ( 
    SELECT @row := @row + 1 as row FROM 
    (select 0 union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t,
    (select 0 union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t2, 
    (SELECT @row:=-1) r
 ) se
 where date_add('2022-06-01 00:00:00', interval row DAY) <= '2022-06-31 23:59:59'
1
2
3
4
5
6
7
8

返回时间段内所有日期 小时

 select DATE_FORMAT(date_add('2022-06-01 00:00:00', interval row HOUR),'%Y-%m-%d %H') date from
 ( 
    SELECT @row := @row + 1 as row FROM 
    (select 0 union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t,
    (select 0 union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t2, 
    (SELECT @row:=-1) r
 ) se
 where date_add('2022-06-01 00:00:00', interval row HOUR) <= '2022-06-01 23:59:59';
1
2
3
4
5
6
7
8