获取两个日期之间的日期列表

使用标准的mysql函数有一种方法来编写一个查询,该查询将返回两个日期之间的天数列表.

例如,给定2009-01-01和2009-01-13,它将返回一个包含值的列表:

 2009-01-01 
 2009-01-02 
 2009-01-03
 2009-01-04 
 2009-01-05
 2009-01-06
 2009-01-07
 2009-01-08 
 2009-01-09
 2009-01-10
 2009-01-11
 2009-01-12
 2009-01-13

编辑:看来我还不清楚.我想生成这个列表.我有值存储在数据库中(按日期时间),但是希望它们在左外连接上聚合到上面的日期列表中(我希望在某些连接的右侧有一段时间为null并且会处理这个).

我将使用此存储过程在名为time_intervals的临时表中生成所需的时间间隔,然后使用temp time_intervals表JOIN并聚合数据表.

该过程可以生成您在其中指定的所有不同类型的间隔:

call make_intervals('2009-01-01 00:00:00','2009-01-10 00:00:00',1,'DAY')
.
select * from time_intervals  
.
interval_start      interval_end        
------------------- ------------------- 
2009-01-01 00:00:00 2009-01-01 23:59:59 
2009-01-02 00:00:00 2009-01-02 23:59:59 
2009-01-03 00:00:00 2009-01-03 23:59:59 
2009-01-04 00:00:00 2009-01-04 23:59:59 
2009-01-05 00:00:00 2009-01-05 23:59:59 
2009-01-06 00:00:00 2009-01-06 23:59:59 
2009-01-07 00:00:00 2009-01-07 23:59:59 
2009-01-08 00:00:00 2009-01-08 23:59:59 
2009-01-09 00:00:00 2009-01-09 23:59:59 
.
call make_intervals('2009-01-01 00:00:00','2009-01-01 02:00:00',10,'MINUTE')
. 
select * from time_intervals
.  
interval_start      interval_end        
------------------- ------------------- 
2009-01-01 00:00:00 2009-01-01 00:09:59 
2009-01-01 00:10:00 2009-01-01 00:19:59 
2009-01-01 00:20:00 2009-01-01 00:29:59 
2009-01-01 00:30:00 2009-01-01 00:39:59 
2009-01-01 00:40:00 2009-01-01 00:49:59 
2009-01-01 00:50:00 2009-01-01 00:59:59 
2009-01-01 01:00:00 2009-01-01 01:09:59 
2009-01-01 01:10:00 2009-01-01 01:19:59 
2009-01-01 01:20:00 2009-01-01 01:29:59 
2009-01-01 01:30:00 2009-01-01 01:39:59 
2009-01-01 01:40:00 2009-01-01 01:49:59 
2009-01-01 01:50:00 2009-01-01 01:59:59 
.
I specified an interval_start and interval_end so you can aggregate the 
data timestamps with a "between interval_start and interval_end" type of JOIN.
.
Code for the proc:
.
-- drop procedure make_intervals
.
CREATE PROCEDURE make_intervals(startdate timestamp, enddate timestamp, intval integer, unitval varchar(10))
BEGIN
-- *************************************************************************
-- Procedure: make_intervals()
--    Author: Ron Savage
--      Date: 02/03/2009
--
-- Description:
-- This procedure creates a temporary table named time_intervals with the
-- interval_start and interval_end fields specifed from the startdate and
-- enddate arguments, at intervals of intval (unitval) size.
-- *************************************************************************
   declare thisDate timestamp;
   declare nextDate timestamp;
   set thisDate = startdate;

   -- *************************************************************************
   -- Drop / create the temp table
   -- *************************************************************************
   drop temporary table if exists time_intervals;
   create temporary table if not exists time_intervals
      (
      interval_start timestamp,
      interval_end timestamp
      );

   -- *************************************************************************
   -- Loop through the startdate adding each intval interval until enddate
   -- *************************************************************************
   repeat
      select
         case unitval
            when 'MICROSECOND' then timestampadd(MICROSECOND, intval, thisDate)
            when 'SECOND'      then timestampadd(SECOND, intval, thisDate)
            when 'MINUTE'      then timestampadd(MINUTE, intval, thisDate)
            when 'HOUR'        then timestampadd(HOUR, intval, thisDate)
            when 'DAY'         then timestampadd(DAY, intval, thisDate)
            when 'WEEK'        then timestampadd(WEEK, intval, thisDate)
            when 'MONTH'       then timestampadd(MONTH, intval, thisDate)
            when 'QUARTER'     then timestampadd(QUARTER, intval, thisDate)
            when 'YEAR'        then timestampadd(YEAR, intval, thisDate)
         end into nextDate;

      insert into time_intervals select thisDate, timestampadd(MICROSECOND, -1, nextDate);
      set thisDate = nextDate;
   until thisDate >= enddate
   end repeat;

 END;

这篇文章底部的类似示例数据场景,我为SQL Server构建了一个类似的函数.

对于MSSQL,您可以使用它.这非常快.

您可以将它包装在表值函数或存储过程中,并在开始日期和结束日期作为变量进行解析.

DECLARE @startDate DATETIME
DECLARE @endDate DATETIME

SET @startDate = '2011-01-01'
SET @endDate = '2011-01-31';

WITH dates(Date) AS 
(
    SELECT @startdate as Date
    UNION ALL
    SELECT DATEADD(d,1,[Date])
    FROM dates 
    WHERE DATE < @enddate
)

SELECT Date
FROM dates
OPTION (MAXRECURSION 0)
GO

我们在BIRT报告中遇到了类似的问题,因为我们想报告那些没有数据的日子.由于这些日期没有条目,对我们来说最简单的解决方案是创建一个存储所有日期的简单表,并使用它来获取范围或连接以获得该日期的零值.

我们每个月都有一份工作,以确保该表在未来5年内填充.因此创建表:

create table all_dates (
    dt date primary key
);

毫无疑问,使用不同的DBMS有一些神奇的棘手方法,但我们总是选择最简单的解决方案.该表的存储要求很小,它使查询更加简单和便携.从性能的角度来看,这种解决方案几乎总是更好,因为它不需要对数据进行每行计算.

另一个选项(我们以前使用过它)是为了确保每个日期在表格中都有一个条目.我们定期清扫表格,并为不存在的日期和/或时间添加零条目.在您的情况下,这可能不是一个选项,它取决于存储的数据.

如果您真的认为保持all_dates表填充是一件麻烦事,那么存储过程就是返回包含这些日期的数据集的方法.这几乎肯定会变慢,因为每次调用时都必须计算范围,而不是仅仅从表中提取预先计算的数据.

但是,说实话,你可以填写表1000年没有任何严重的数据存储问题 - 365,000个16字节(例如)日期加上一个索引重复日期加上20%的安全开销,我粗略估计约14M [365,000*16*2*1.2 = 14,016,000字节]),这是事物计划中的一个小表.

您可以使用MySQL的用户变量,如下所示:

SET @num = -1;
SELECT DATE_ADD( '2009-01-01', interval @num := @num+1 day) AS date_sequence, 
your_table.* FROM your_table
WHERE your_table.other_column IS NOT NULL
HAVING DATE_ADD('2009-01-01', interval @num day) <= '2009-01-13'

@num为-1,因为您第一次使用它时会添加它.此外,您不能使用"HAVING date_sequence",因为这会使用户变量每行增加两次.

从这个答案借用一个想法,您可以设置一个0到9的表格,并使用它来生成您的日期列表.

CREATE TABLE num (i int);
INSERT INTO num (i) VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9);

select adddate('2009-01-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('2009-01-01', numlist.id) <= '2009-01-13';

这将允许您生成最多1000个日期的列表.如果需要更大,可以在内部查询中添加另一个交叉连接.