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

Question

提问by Gilgad

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).

编辑：看来我还不清楚。我想生成这个列表。我将值存储在数据库中（按日期时间），但希望它们在左外连接上聚合到上述日期列表（我希望从某些连接的右侧获得空值几天，并将处理此问题）。

Answer 1

采纳答案by Ron Savage

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_intervalstable.

我将使用此存储过程将您需要的间隔生成到名为time_intervals的临时表中，然后将您的数据表与临时time_intervals表连接并聚合。

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 构建了一个类似的函数。

Answer 2

回答by Richard

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.

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

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

Answer 3

回答by paxdiablo

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 reallythink it's a hassle to keep the all_datestable 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 年而不会出现任何严重的数据存储问题 - 365,000 个 16 字节（例如）日期加上复制日期的索引加上 20% 的安全开销，我粗略估计为大约 14M [365,000 * 16 * 2 * 1.2 = 14,016,000 字节]），这是一个很小的表。

Answer 4

回答by Andrew Vit

You can use MySQL's user variableslike 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，因为您在第一次使用它时添加了它。此外，您不能使用“HAVING date_sequence”，因为这会使用户变量每行增加两次。

Answer 5

回答by Logan5

Borrowing an idea from thisanswer, 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 个日期的列表。如果需要更大，可以向内部查询添加另一个交叉联接。

Answer 6

回答by Nathan Wood

For Access (or any SQL language)

对于 Access（或任何 SQL 语言）

Create one table that has 2 fields, we'll call this table tempRunDates:
--Fields fromDateand toDate
--Then insert only 1 record, that has the start date and the end date.
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.

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;

创建一个有 2 个字段的表，我们称这个表为tempRunDates：--
FieldsfromDate和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;

Easy!

简单！

Answer 7

回答by Todd Dickerson

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

Answer 8

回答by Cade Roux

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.

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

Answer 9

回答by Ekta

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 服务器中找到两个给定日期之间的日期在http://ektaraval.blogspot.com/2010/09/writing-recursive-query-to-find-out-all.html上有说明

Answer 10

回答by Anas

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

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

提问by Gilgad

采纳答案by Ron Savage

回答by Richard

回答by paxdiablo

回答by Andrew Vit

回答by Logan5

回答by Nathan Wood

回答by Todd Dickerson

回答by Cade Roux

回答by Ekta

回答by Anas

相关推荐

最近更新

标签

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

提问by Gilgad

采纳答案by Ron Savage

回答by Richard

回答by paxdiablo

回答by Andrew Vit

回答by Logan5

回答by Nathan Wood

回答by Todd Dickerson

回答by Cade Roux

回答by Ekta

回答by Anas

相关推荐

MySQL 如何使用 SQLite 正确执行 CREATE INDEX

从物理文件恢复 MySQL 数据库

如果条件为真，则在 MySQL 存储过程中运行查询

MySQL 插入 Where 查询

相关推荐

最近更新

标签