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

Question

获取两个日期之间的日期列表使用标准的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为NULL，并将处理此值)。

Answer 1

我将使用这个存储过程将所需的时间间隔生成到名为时间间隔，然后使用temp连接和聚合数据表。时间间隔桌子。

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

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_i
ntervals('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-0
1 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_st
art 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 (un
 
 itval) 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;

的底部有类似的示例数据场景。这个职位，其中我为SQLServer构建了一个类似的函数。

Answer 2

对于MSSQL，可以使用此方法。非常快。

您可以将其封装在表值函数或存储的proc中，并以变量的形式解析开始日期和结束日期。

DECLARE @startDate DATETIMEDECLARE @endDate DATETIMESET @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 DateFROM datesOPTION (MAXRECURSION 0)GO

Answer 3

我们对BIRT报告也有类似的问题，因为我们想报告那些没有数据的日子。由于这些日期没有条目，所以最简单的解决方案是创建一个简单的表，存储所有日期，并使用该表获取范围或联接以获得该日期的零值。

我们有一个每个月运行的任务，以确保表在未来5年内被填充。表是这样创建的：

create table all_dates (
    dt date primary key);

毫无疑问，对于不同的DBMS，有一些不可思议的方法可以实现这一点，但是我们总是选择最简单的解决方案。表的存储需求很小，这使得查询更加简单和可移植。从性能角度看，这种解决方案几乎总是更好，因为它不需要对数据进行逐行计算。

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

如果你真的我觉得这是个麻烦all_dates表填充后，存储过程将返回包含这些日期的数据集。这几乎肯定会慢一些，因为每次调用它时都要计算范围，而不是仅仅从表中提取预先计算出来的数据。

但是，老实说，您可以在1000年内填充该表，而不会出现任何严重的数据存储问题-比如365，000个16字节(例如)日期-再加上一个与日期重复的索引加上20%的安全开销，我估计大约为1400万(365，000*16*2*1.2=14，016，000字节)，这是事物方案中的一个极小的表。