使用标准的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）。
我会使用这个存储过程来生成你需要的时间间隔，并将数据表与临时表 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构建了一个类似的函数。
interval_start timestamp NULL 和 interval_end timestamp NULL - Chaki_Black对于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
2021/01 编辑（Dr. V）：我喜欢这个解决方案，并将其适用于我的SQL V8。 这是代码，将其封装成一个过程：
DELIMITER //
CREATE PROCEDURE dates_between (IN from_date DATETIME,
IN to_date DATETIME) BEGIN
WITH RECURSIVE dates(Date) AS
(
SELECT from_date as Date
UNION ALL
SELECT DATE_ADD(Date, INTERVAL 1 day) FROM dates WHERE Date < to_date
)
SELECT DATE(Date) FROM dates;
END//
DELIMITER ;
您可以像这样使用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”，因为这会使用户变量在每行中增加两次。
我们曾经在BIRT报表中遇到类似问题，即希望报告那些没有数据的日期。由于这些日期没有任何条目，我们最简单的解决方案是创建一个简单的表格来存储所有日期，并使用该表格获取范围或连接以获取该日期的零值。
我们有一个工作任务每个月运行一次，以确保该表格向未来5年填充。表格的创建方式如下：
create table all_dates (
dt date primary key
);
使用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;
cte_max_recursion_depth来增加它。 - at54321借鉴这个答案的想法，您可以设置一个包含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';
date;则不会返回那部分内容。 - Bimal Poudel对于 Access（或任何 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.
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;
很容易！
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
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;
(SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) xcn，有人能解释一下吗？ - Kunal Awasthi这个解决方案适用于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';
(mytable中的行数)*(mytable中的行数) = 10*10 = 100。您可以通过更改SQL中的form部分来增加此范围：
从mytable x，mytable y，mytable z
因此，范围为10*10*10 = 1000等等。