MYSQL查询两个时间之间的日期列表

原创已于 2024-08-12 09:51:21 修改 · 154 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#mysql #数据库

于 2024-04-28 17:08:38 首次发布

数据库专栏收录该内容

54 篇文章

订阅专栏

本文介绍四种不同的方法来查询MySQL中两个特定日期之间的所有日期。这些方法包括使用临时表、动态SQL、存储过程及多表联合生成日期序列。

时间：2016-11-21 13:04:11

这个一般用于统计，今天在开发一个拆线时用到了。需要把日期来显示在X轴上，直接百度了下，方法有好多，

找了一个最实用的（需要建一张临时表）

CREATE TABLE num (i INT);
 
INSERT INTO num (i)
VALUES
    (0),
    (1),
    (2),
    (3),
    (4),
    (5),
    (6),
    (7),
    (8),
    (9);
 
SELECT
    adddate('2012-09-01', numlist.id) AS 'date'
FROM
    (
        SELECT
            n1.i + n10.i * 10 + n100.i * 100 AS id
        FROM
            num n1
        CROSS JOIN num AS n10
        CROSS JOIN num AS n100
    ) AS numlist
WHERE
    adddate('2012-09-01', numlist.id) <= '2012-09-10';

QQ截图20161121125816.jpg

方法2：

-- 定义自增变量
set @i = -1;
-- 创建指定日期间记录数天数需要 加 1
set @sql = repeat(" select 1 union all",-datediff('2012-09-01','2012-09-10')+1);
-- 将最后一个union all 去掉，这里前边加1个空格，就不用在减1了
set @sql = left(@sql,length(@sql)-length(" union all"));
-- 拼接sql
set @sql = concat("select date_add('2012-09-01',interval @i:=@i+1 day) as date from (",@sql,") as tmp");
 
prepare stmt from @sql;
execute stmt;

方法三存储过程（没测试）：

DELIMITER $$
 
DROP PROCEDURE IF EXISTS DayRangeProc$$
 
CREATE PROCEDURE DayRangeProc(IN start_date datetime , IN end_date datetime )
      BEGIN
              DECLARE temp  INT;
              DECLARE range_day  INT;
 
              SET temp = 0;
              SET range_day = (SELECT DATEDIFF(end_date,start_date));
 
              WHILE temp  <= range_day DO
                    select ADDDATE(start_date, temp);
                    SET temp = temp + 1;
              END WHILE;
      END$$
 
  DELIMITER ;
 
Call DayRangeProc('2010-9-1','2010-9-10');

方法4：

SELECT date_series.date AS date
    FROM (
        SELECT ADDDATE('2012-09-01', INTERVAL t4.i*1000 + t3.i*100 + t2.i*10 + t1.i DAY) AS date
        FROM (SELECT 0 i 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) t1
        CROSS JOIN (SELECT 0 i 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
        CROSS JOIN (SELECT 0 i 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) t3
        CROSS JOIN (SELECT 0 i 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) t4
    ) date_series

    WHERE date_series.date BETWEEN '2012-09-01' AND '2012-09-10'
    GROUP BY date_series.date
    ORDER BY date_series.date