MySQL 动态获取每日的时间

最新推荐文章于 2022-03-17 17:18:07 发布

原创最新推荐文章于 2022-03-17 17:18:07 发布 · 438 阅读

0 ·

CC 4.0 BY-SA版权

MySQL 专栏收录该内容

18 篇文章

订阅专栏

本文介绍了如何利用动态时间戳（@startDate至@endDate）进行多表左连接，以实现按天汇总的数据分析，重点在于使用INTERVAL变量和FROM_UNIXTIME函数来生成日期序列。

动态获取每日时间常用于按时间汇总的时候使用，将时间作为左连接的条件进行多表左连接汇总


-- @startDate 这是开始时间戳
-- @endDate 这是结束时间戳
              SELECT ADDDATE( FROM_UNIXTIME(1612108800,'%Y-%m-%d'), INTERVAL @i:=@i+1 DAY) AS daytime
              FROM (
              SELECT a.a
              FROM (SELECT 0 AS a 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) AS a
              CROSS JOIN (SELECT 0 AS a 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) AS b
              CROSS JOIN (SELECT 0 AS a 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) AS c
              ) a
              JOIN (SELECT @i := -1) r1
              WHERE 
              --                         结束时间              ,               开始时间
              @i < DATEDIFF(FROM_UNIXTIME(1614528000,'%Y-%m-%d'), FROM_UNIXTIME(1612108800,'%Y-%m-%d'))