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

Using standard mysql functions is there a way to write a query that will return a list of days between two dates.

使用标准的mysql函数可以编写一个查询，在两个日期之间返回天数列表。

eg given 2009-01-01 and 2009-01-13 it would return a one column table with the values:

给定2009-01-01和2009-01-13，它将返回一个列表，其中的值为:

Edit: It appears I have not been clear. I want to GENERATE this list. I have values stored in the database (by datetime) but want them to be aggregated on a left outer join to a list of dates as above (I am expecting null from the right side of some of this join for some days and will handle this).

编辑:看来我还不清楚。我想生成这个列表。我将值存储在数据库中(通过datetime)，但希望它们聚合到上面所示的日期列表的左外连接上(我希望这个连接的右端在几天内为null，并将处理它)。

18 个解决方案

#1

I would use this stored procedure to generate the intervals you need into the temp table named time_intervals, then JOIN and aggregate your data table with the temp time_intervals table.

我将使用这个存储过程来生成您需要到temp表中指定的time_interval的时间间隔，然后使用temp time_interval表连接和聚合数据表。

The procedure can generate intervals of all the different types you see specified in it:

该程序可以生成您在其中看到的所有不同类型的间隔:

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;

Similar example data scenario at the bottom of this post, where I built a similar function for SQL Server.

本文末尾的示例数据场景类似，我在其中为SQL Server构建了一个类似的函数。

#2

For MSSQL you can use this. It is VERY quick.

对于MSSQL可以使用这个。它非常快速。

You can wrap this up in a table valued function or stored proc and parse in the start and end dates as variables.

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

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

#3

You can use MySQL's user variables like this:

你可以使用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 is -1 because you add to it the first time you use it. Also, you can't use "HAVING date_sequence" because that makes the user variable increment twice for each row.

@num是-1，因为您在第一次使用它时添加了它。另外，您不能使用“拥有date_sequence”，因为这会使每一行的用户变量增加两次。

#4

We had a similar problem with BIRT reports in that we wanted to report on those days that had no data. Since there were no entries for those dates, the easiest solution for us was to create a simple table that stored all dates and use that to get ranges or join to get zero values for that date.

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

We have a job that runs every month to ensure that the table is populated 5 years out into the future. The table is created thus:

我们每个月都有一份工作，以确保在未来5年里，这个表格将被填满。该表是这样创建的:

create table all_dates (
    dt date primary key
);

No doubt there are magical tricky ways to do this with different DBMS' but we always opt for the simplest solution. The storage requirements for the table are minimal and it makes the queries so much simpler and portable. This sort of solution is almost always better from a performance point-of-view since it doesn't require per-row calculations on the data.

毫无疑问，在使用不同的DBMS时，有一些神奇的技巧，但我们总是选择最简单的解决方案。表的存储需求是最小的，它使查询更加简单和可移植性。从性能角度来看，这种解决方案几乎总是更好的，因为它不需要对数据进行每一行的计算。

The other option (and we've used this before) is to ensure there's an entry in the table for every date. We swept the table periodically and added zero entries for dates and/or times that didn't exist. This may not be an option in your case, it depends on the data stored.

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

If you really think it's a hassle to keep the all_dates table populated, a stored procedure is the way to go which will return a dataset containing those dates. This will almost certainly be slower since you have to calculate the range every time it's called rather than just pulling pre-calculated data from a table.

如果您真的认为填充all_dates表是一件麻烦事，那么存储过程将返回包含这些日期的数据集。这几乎肯定会比较慢，因为每次调用时都要计算范围，而不是从表中提取预先计算的数据。

But, to be honest, you could populate the table out for 1000 years without any serious data storage problems - 365,000 16-byte (for example) dates plus an index duplicating the date plus 20% overhead for safety, I'd roughly estimate at about 14M [365,000 * 16 * 2 * 1.2 = 14,016,000 bytes]), a minuscule table in the scheme of things.

但是,老实说,你可以填充表1000年来没有任何严重的数据存储问题- 365000 16字节(例如)日期+复制索引日期+ 20%的开销为安全起见,我粗略估计约为14米(365000 * 16 * 2 * 1.2 = 14016000字节)),一个极小的表中计划的事情。

#5

Borrowing an idea from this answer, you can set up a table with 0 through 9 and use that to generate your list of dates.

借用这个答案的思想，您可以设置一个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';

This will allow you to generate a list of up to 1000 dates. If you need to go larger, you can add another cross join to the inner query.

这将允许您生成最多1000个日期的列表。如果需要放大，可以向内部查询添加另一个交叉连接。

#6

For Access (or any SQL language)

用于访问(或任何SQL语言)

Create one table that has 2 fields, we'll call this table tempRunDates:
--Fields fromDate and toDate
--Then insert only 1 record, that has the start date and the end date.

创建一个有两个字段的表，我们将调用这个表tempRunDates:——fields fromDate和toDate——然后只插入一条记录，其中包含开始日期和结束日期。
Create another table: Time_Day_Ref
--Import a list of dates (make list in excel is easy) into this table.
--The field name in my case is Greg_Dt, for Gregorian Date
--I made my list from jan 1 2009 through jan 1 2020.

创建另一个表:Time_Day_Ref——将日期列表(在excel中创建列表很容易)导入到这个表中。——我的字段名是Greg_Dt，表示公历日期——我从2009年1月1日到2020年1月1日列出了我的列表。

Run the query:

运行查询:

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

Easy!

简单!

#7

Typically one would use an auxiliary numbers table you usually keep around for just this purpose with some variation on this:

通常情况下，你会用一个辅助的数字表你通常只是为了这个目的而保留一些变化:

SELECT *
FROM (
    SELECT DATEADD(d, number - 1, '2009-01-01') AS dt
    FROM Numbers
    WHERE number BETWEEN 1 AND DATEDIFF(d, '2009-01-01', '2009-01-13') + 1
) AS DateRange
LEFT JOIN YourStuff
    ON DateRange.dt = YourStuff.DateColumn

I've seen variations with table-valued functions, etc.

我见过表值函数的变化，等等。

You can also keep a permanent list of dates. We have that in our data warehouse as well as a list of times of day.

你也可以保留一个永久的日期列表。我们在数据仓库中有它，还有一天的时间列表。

#8

Well how to find dates between two given date in SQL server is explain on http://ektaraval.blogspot.com/2010/09/writing-recursive-query-to-find-out-all.html

如何在SQL server中找到两个给定日期之间的日期，请参阅http://ektaraval.blogspot.com/2010/09/writing- recursiveto -find-out-all.html

#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

We used this in our HRMS System you will find it useful

我们在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

#11

This solution is working with MySQL 5.0
Create a table - mytable.
The schema does not material. What matters is the number of rows in it.
So, you can keep just one column of type INT with 10 rows, values - 1 to 10.

这个解决方案是使用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';

Limitation: The maximum number of dates returned by above query will be
(rows in mytable)*(rows in mytable) = 10*10 = 100.

限制:以上查询返回的最大日期数为(mytable中的行)*(mytable中的行)= 10*10 = 100。

You can increase this range by changing form part in sql:
from mytable x,mytable y, mytable z
So, the range be 10*10*10 =1000 and so on.

可以通过改变sql中的form部分来增加这个范围:从mytable x、mytable y、mytable z等等，范围是10*10*10 =1000，以此类推。

#12

Create a stored procedure which takes two parameters a_begin and a_end. Create a temporary table within it called t, declare a variable d, assign a_begin to d, and run a WHILE loop INSERTing d into t and calling ADDDATE function to increment the value d. Finally SELECT * FROM t.

创建一个包含两个参数a_begin和a_end的存储过程。在其中创建一个名为t的临时表，声明一个变量d，分配a_begin to d，运行一个WHILE循环，将d插入到t中，并调用ADDDATE函数来增加d的值。

#13

I would use something similar to this:

我会使用类似的东西:

DECLARE @DATEFROM AS DATETIME
DECLARE @DATETO AS DATETIME
DECLARE @HOLDER TABLE(DATE DATETIME)

SET @DATEFROM = '2010-08-10'
SET @DATETO = '2010-09-11'

INSERT INTO
    @HOLDER
        (DATE)
VALUES
    (@DATEFROM)

WHILE @DATEFROM < @DATETO
BEGIN

    SELECT @DATEFROM = DATEADD(D, 1, @DATEFROM)
    INSERT 
    INTO
        @HOLDER
            (DATE)
    VALUES
        (@DATEFROM)
END

SELECT 
    DATE
FROM
    @HOLDER

Then the @HOLDER Variable table holds all the dates incremented by day between those two dates, ready to join at your hearts content.

然后，@HOLDER变量表保存这两个日期之间每天递增的所有日期，准备在您的内心内容中加入。

#14

I've been fighting with this for quite a while. Since this is the first hit on Google when I searched for the solution, let me post where I've gotten so far.

我已经和这个斗争了很长一段时间了。既然这是谷歌上的第一次点击，当我搜索解决方案时，让我发布一下我到目前为止的进展。

SET @d := '2011-09-01';
SELECT @d AS d, cast( @d := DATE_ADD( @d , INTERVAL 1 DAY ) AS DATE ) AS new_d
  FROM [yourTable]
  WHERE @d <= '2012-05-01';

Replace [yourTable] with a table from your database. The trick is that the number of rows in the table you select must be >= the number of dates you want to be returned. I tried using the table placeholder DUAL, but it would only return one single row.

用数据库中的表替换[yourTable]。诀窍在于，您选择的表中的行数必须是>=您希望返回的日期数。我尝试使用表占位符对偶，但它只返回一行。

#15

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 $$

this procedure will insert all dates from the beginning of the year till now, just substitue the days of the "start" and "end", and you are ready to go!

此程序将插入从年初到现在的所有日期，只需替换“开始”和“结束”的日期，您就可以开始了!

#16

I needed a list with all months between 2 dates for statistics. The 2 dates are the start and enddate from a subscription. So the list shows all months and the amount of subscriptions per month.

我需要一份包含两个日期之间的所有月份的统计清单。这两个日期是订阅的开始和开始。这个列表显示了每个月的订阅量。

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

#17

I am using Server version: 5.7.11-log MySQL Community Server (GPL)

我正在使用服务器版本:5.7.11-log MySQL社区服务器(GPL)

Now we will solve this in a simple way.

现在我们用简单的方法来解决这个问题。

I have created a table named "datetable"

我创建了一个名为“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)

now, wee will see the inserted records within.

现在，wee将看到插入的记录。

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)

and here our query to fetch records within two dates rather than those dates.

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

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)

hope this would help many ones.

希望这能帮助很多人。

#18

-1

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

Here, first add a day to the current endDate, it will be 2011-02-28 00:00:00, then you subtract one second to make the end date 2011-02-27 23:59:59. By doing this, you can get all the dates between the given intervals.

在这里，先给当前的日期加上一天，将是2011-02-28 00:00，然后减去一秒，将2011-02-27 23:59:59结束。通过这样做，您可以获得给定间隔之间的所有日期。

output:
2011/02/25
2011/02/26
2011/02/27

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

#1