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

 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

#1 楼

我将使用此存储过程将所需的间隔生成到名为time_intervals的临时表中，然后将其与临时表time_intervals表联接并聚合。您会在其中看到指定的内容：

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构建了类似的功能。

#2 楼

对于MSSQL，您可以使用它。这非常快。

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

#3 楼

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

每个月运行一次的工作，以确保在未来5年内填充该表。该表是这样创建的：

毫无疑问，对于不同的DBMS，存在魔术般的棘手方法，但是我们始终选择最简单的解决方案。该表的存储要求极少，这使查询变得更加简单和可移植。从性能的角度来看，这种解决方案几乎总是更好的，因为它不需要对数据进行逐行计算。

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

如果您真的认为填充all_dates表很麻烦，则可以使用存储过程返回包含这些日期的数据集。这几乎肯定会比较慢，因为您每次都必须计算范围而不是从表中提取预先计算的数据。

但是，老实说，您可以填充表1000年没有任何严重的数据存储问题-365,000个16字节（例如）的日期，再加上一个索引，加上日期的索引再加上20％的安全开销，我估计大约为1400万[365,000 * 16 * 2 * 1.2 = 14,016,000字节]），这是事物方案中的一个小表。

#4 楼

您可以像这样使用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”，因为这会使用户变量每行增加两次。

#5 楼

从此答案中借用一个想法，您可以设置一个0到9的表，并使用该表生成日期列表。最多1000个日期的列表。如果需要更大，可以向内部查询添加另一个交叉联接。

#6 楼

对于Access（或任何SQL语言）


创建一个具有2个字段的表，我们将此表称为tempRunDates：
-字段fromDate和toDate
-然后仅插入1条记录，其中包含开始日期和结束日期。
创建另一个表：Time_Day_Ref
-将日期列表（在excel中创建列表很容易）导入此表。
-对于我的情况，字段名称是Greg_Dt，表示公历日期
-我列出了从2009年1月1日到2020年1月1日的清单。

运行查询：

SELECT Time_Day_Ref.GREG_DT
FROM tempRunDates, Time_Day_Ref
WHERE Time_Day_Ref.greg_dt>=tempRunDates.fromDate And greg_dt<=tempRunDates.toDate;

简单！

#7 楼

通常，人们会使用一个辅助数字表，通常为此目的而保留一个辅助数字表，并对此进行一些更改：等。

您还可以保留永久的日期列表。我们将其存储在数据仓库中以及一天中的时间列表。

#8 楼

在http://ektaraval.blogspot.com/2010/09/writing-recursive-query-to-find-out-all.html上解释了如何在SQL Server中查找两个给定日期之间的日期。

#9 楼

CREATE FUNCTION [dbo].[_DATES]
(
    @startDate DATETIME,
    @endDate DATETIME
)
RETURNS 
@DATES TABLE(
    DATE1 DATETIME
)
AS
BEGIN
    WHILE @startDate <= @endDate
    BEGIN 
        INSERT INTO @DATES (DATE1)
            SELECT @startDate   
    SELECT @startDate = DATEADD(d,1,@startDate) 
    END
RETURN
END

#10 楼

优雅的解决方案，在MariaDB> = 10.3和MySQL> = 8.0中使用新的递归（公用表表达式）功能。

WITH RECURSIVE t as (
    select '2019-01-01' as dt
  UNION
    SELECT DATE_ADD(t.dt, INTERVAL 1 DAY) FROM t WHERE DATE_ADD(t.dt, INTERVAL 1 DAY) <= '2019-04-30'
)
select * FROM t;

上面的方法返回的是'2019- 01-01”和“ 2019-04-30”。

#11 楼

我们在HRMS系统中使用了此功能，您会发现它有用

SELECT CAST(DAYNAME(daydate) as CHAR) as dayname,daydate
    FROM
    (select CAST((date_add('20110101', interval H.i*100 + T.i*10 + U.i day) )as DATE) as daydate
      from erp_integers as H
    cross
      join erp_integers as T
    cross
      join erp_integers as U
     where date_add('20110101', interval H.i*100 + T.i*10 + U.i day ) <= '20110228'
    order
        by daydate ASC
        )Days

#12 楼

此解决方案适用于MySQL 5.0
创建表-mytable。
该架构不重要。重要的是其中的行数。
因此，您可以仅保留INT类型的一列，其中包含10行，值-1到10。

SQL：

set @tempDate=date('2011-07-01') - interval 1 day;
select
date(@tempDate := (date(@tempDate) + interval 1 day)) as theDate
from mytable x,mytable y
group by theDate
having theDate <= '2011-07-31';

限制：
上述查询返回的最大日期数将是(rows in mytable)*(rows in mytable) = 10*10 = 100.

您可以通过更改sql中的形式部分来增加此范围：
来自mytable x，mytable y，mytable z
所以范围是10*10*10 =1000，依此类推。

#13 楼

创建一个使用两个参数a_begin和a_end的存储过程。
在其中创建一个名为t的临时表，声明一个变量d，将a_begin分配给d，然后将WHILE循环INSERT ing到t中并调用ADDDATE函数来增加值d。终于SELECT * FROM t。

#14 楼

我会用类似的东西：

#15 楼

我已经为此奋斗了很长时间。由于这是我搜索解决方案时首次在Google上获得成功，因此，请让我发布到目前为止的内容。

数据库。诀窍在于，您选择的表中的行数必须> =您要返回的日期数。我尝试使用表占位符DUAL，但它只会返回一行。

#16 楼

select * from table_name where col_Date between '2011/02/25' AND DATEADD(s,-1,DATEADD(d,1,'2011/02/27'))

在这里，请先在当前endDate中添加一天，即2011-02-28 00:00:00，然后再减去一秒钟以作为结束日期2011-02-27 23 ：59：59。这样，您可以获取给定间隔之间的所有日期。

输出：
2011/02/25
2011/02/26
2011/02 / 27

#17 楼

DELIMITER $$  
CREATE PROCEDURE popula_calendario_controle()
   BEGIN
      DECLARE a INT Default 0;
      DECLARE first_day_of_year DATE;
      set first_day_of_year = CONCAT(DATE_FORMAT(curdate(),'%Y'),'-01-01');
      one_by_one: LOOP
         IF dayofweek(adddate(first_day_of_year,a)) <> 1 THEN
            INSERT INTO calendario.controle VALUES(null,150,adddate(first_day_of_year,a),adddate(first_day_of_year,a),1);
         END IF;
         SET a=a+1;
         IF a=365 THEN
            LEAVE one_by_one;
         END IF;
      END LOOP one_by_one;
END $$

此过程将插入从年初到现在的所有日期，只需替换“开始”和“结束”的日期，即可开始使用！

#18 楼

我需要一个列表，其中包含两个日期之间的所有月份，以进行统计。这两个日期是订阅的开始日期和结束日期。
因此列表显示了所有月份以及每月的订阅量。

MYSQL

CREATE PROCEDURE `get_amount_subscription_per_month`()
BEGIN
   -- Select the ultimate start and enddate from subscribers
   select @startdate := min(DATE_FORMAT(a.startdate, "%Y-%m-01")), 
          @enddate := max(DATE_FORMAT(a.enddate, "%Y-%m-01")) + interval 1 MONTH
   from subscription a;

   -- Tmp table with all months (dates), you can always format them with DATE_FORMAT) 
   DROP TABLE IF EXISTS tmp_months;
   create temporary table tmp_months (
      year_month date,
      PRIMARY KEY (year_month)
   );


   set @tempDate=@startdate;  #- interval 1 MONTH;

   -- Insert every month in tmp table
   WHILE @tempDate <= @enddate DO
     insert into tmp_months (year_month) values (@tempDate);
     set @tempDate = (date(@tempDate) + interval 1 MONTH);
   END WHILE;

   -- All months
   select year_month from tmp_months;

   -- If you want the amount of subscription per month else leave it out
   select mnd.year_month, sum(subscription.amount) as subscription_amount
   from tmp_months mnd
   LEFT JOIN subscription ON mnd.year_month >= DATE_FORMAT(subscription.startdate, "%Y-%m-01") and mnd.year_month <= DATE_FORMAT(subscription.enddate, "%Y-%m-01")
   GROUP BY mnd.year_month;

 END

#19 楼

您可以使用此

SELECT CAST(cal.date_list AS DATE) day_year
FROM (
  SELECT SUBDATE('2019-01-01', INTERVAL 1 YEAR) + INTERVAL xc DAY AS date_list
  FROM (
        SELECT @xi:=@xi+1 as xc from
        (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) xc1,
        (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) xc2,
        (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) xc3,
        (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) xc4,
        (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) xc5,
        (SELECT @xi:=-1) xc0
    ) xxc1
) cal
WHERE cal.date_list BETWEEN '2019-01-01' AND '2019-12-31'
ORDER BY cal.date_list DESC;

#20 楼

我正在使用Server version: 5.7.11-log MySQL Community Server (GPL)

现在我们将以一种简单的方式解决此问题。

我创建了一个名为“ datetable”的表。

mysql> describe datetable;
+---------+---------+------+-----+---------+-------+
| Field   | Type    | Null | Key | Default | Extra |
+---------+---------+------+-----+---------+-------+
| colid   | int(11) | NO   | PRI | NULL    |       |
| coldate | date    | YES  |     | NULL    |       |
+---------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)

/>
现在，我们将在其中看到插入的记录。

mysql> select * from datetable;
+-------+------------+
| colid | coldate    |
+-------+------------+
|   101 | 2015-01-01 |
|   102 | 2015-05-01 |
|   103 | 2016-01-01 |
+-------+------------+
3 rows in set (0.00 sec)

，这里我们的查询将获取两个日期而不是那些日期内的记录。

mysql> select * from datetable where coldate > '2015-01-01' and coldate < '2016-01-01';
+-------+------------+
| colid | coldate    |
+-------+------------+
|   102 | 2015-05-01 |
+-------+------------+
1 row in set (0.00 sec)

希望对很多人有帮助。