从日期范围生成天数

Question

我想要运行一个查询，例如

select ... as days where `date` is between '2010-01-20' and '2010-01-24'

并返回像这样的数据：

天数
----------
2010年01月20日
2010年01月21日
2010年01月22日
2010年01月23日
2010年01月24日

- Pentium10

---

这个问题没有其他附属问题。上述问题是关于掌握SQL课程的问题。 - Pentium10

你只需要根据所选日期范围获取一个日期数组吗？ - Derek Adair

我在思考一个用法，为了找到你的问题... 如果你得到一个任务，在你的表中填写一些缺失的记录。而且你必须为每一天运行一个查询，我在想类似这样的东西 insert into table select ... as days date between '' and '' - Pentium10

一个使用示例是生成统计数据，并包括没有数据的日期行。如果要进行分组，实际上在SQL中生成所有信息并将其添加到所需的任何格式中会比将数据原样转储到语言中，并开始循环和添加空值更快。 - Nanne

@Nanne 这正是我保存这个问题的原因。我需要将上述内容 LEFT JOIN 到可能不存在某些日期数据中。 - Josh Diehl

涵盖了“获取两个日期之间的日期列表”（尽管这个问题的焦点更窄，具体而言，另一个问题是与关系型数据库无关的）。 - outis

Answer 1

这种解决方案不使用循环，过程或临时表。子查询生成最近10,000天的日期，并可以扩展到您希望向前或向后的范围。

select a.Date 
from (
    select curdate() - INTERVAL (a.a + (10 * b.a) + (100 * c.a) + (1000 * d.a) ) DAY as Date
    from (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as a
    cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as b
    cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as c
    cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as d
) a
where a.Date between '2010-01-20' and '2010-01-24' 

输出:

Date
----------
2010-01-24
2010-01-23
2010-01-22
2010-01-21
2010-01-20

性能注意事项

在这里测试时，性能出奇地好：上面的查询只需要0.0009秒。

如果我们扩展子查询以生成大约100,000个数字（因此大约相当于274年的日期），它将在0.0458秒内运行。

顺便说一句，这是一种非常可移植的技术，只需进行微小的调整即可在大多数数据库中使用。

SQL Fiddle示例返回1,000天

- D'Arcy Rittich

---

如果将 UNION 改为 UNION ALL，性能将会更好 - 这样就不会浪费时间去检查不存在的重复项了。但在我看来，这个做法过于复杂了 - 如果你要使用 UNION 构建结果集，为什么不直接指定日期呢？ - OMG Ponies

为什么不直接指定日期就行了呢？-因为上述方法允许你创建任意大的数字（和日期）集合，无需创建表格，在你所建议的方式中硬编码这些数据将会很麻烦。显然对于五个日期来说这种方法过于复杂；但即使是在这种情况下，如果你要连接的表格中事先不知道日期，只知道潜在的最小值和最大值，这种方法也是有意义的。 - D'Arcy Rittich

看到问题得到了答案，而不是一堆无休止的评论说它做不到或不应该这样做，真的很好。大多数事情都是可以做到的，“应该”只有在特定的背景下才有意义，而这些背景对每个人来说都是不同的。尽管我很清楚在大多数情况下还有更好的方法，但这个答案对我有所帮助。 - joe

@xjshiya，按照当前查询语句的写法，它是从当前日期(curdate())开始往前查询的。将curdate()改为'2014-01-01'，那么查询就会从该日期开始往前查询。 - D'Arcy Rittich

如果你不能让这个查询工作，请扇自己一巴掌，然后重新阅读原帖中关于这个查询生成1000个日期的评论。由于2010年已经超过1000天了，所以你需要相应地调整查询。 - Noel Baron

Answer 2

这里是使用视图的另一种变体：

CREATE VIEW digits AS
  SELECT 0 AS digit UNION ALL
  SELECT 1 UNION ALL
  SELECT 2 UNION ALL
  SELECT 3 UNION ALL
  SELECT 4 UNION ALL
  SELECT 5 UNION ALL
  SELECT 6 UNION ALL
  SELECT 7 UNION ALL
  SELECT 8 UNION ALL
  SELECT 9;

CREATE VIEW numbers AS
  SELECT
    ones.digit + tens.digit * 10 + hundreds.digit * 100 + thousands.digit * 1000 AS number
  FROM
    digits as ones,
    digits as tens,
    digits as hundreds,
    digits as thousands;

CREATE VIEW dates AS
  SELECT
    SUBDATE(CURRENT_DATE(), number) AS date
  FROM
    numbers;

然后你只需要简单地执行以下操作即可（看看它有多优雅？）：

SELECT
  date
FROM
  dates
WHERE
  date BETWEEN '2010-01-20' AND '2010-01-24'
ORDER BY
  date

更新

值得注意的是，您只能生成从当前日期开始的过去日期。如果您想要生成任何类型的日期范围（过去、未来和中间），您将需要使用此视图：

CREATE VIEW dates AS
  SELECT
    SUBDATE(CURRENT_DATE(), number) AS date
  FROM
    numbers
  UNION ALL
  SELECT
    ADDDATE(CURRENT_DATE(), number + 1) AS date
  FROM
    numbers;

- Stéphane

---

这不适用于所有情况。选择日期 从日期表中 其中日期在'2014-12-01'和'2014-12-28'之间 排序日期。 - vasanth

很好的决定 @user927258。这是因为上面提到的第一个视图 dates 是从当前日期开始计算日期，这就是为什么您无法检索设置在未来的日期。@RedFilter 的回答也存在相同的设计缺陷。尽管如此，我已经在我的回答中添加了一种解决方法。 - Stéphane

使用一些视图确实简化了查询，并使其可重用。虽然它们本质上做着相同的事情，但所有这些 UNION 子句在单个 SQL 语句中看起来很奇怪。 - Stewart

想知道在“日期”视图中进行联合消除是否会带来一些性能提升。 - mpapec

Answer 3

已接受的答案在 PostgreSQL 中不起作用（出现“a”附近的语法错误）。

在 PostgreSQL 中，您可以使用 generate_series 函数来执行此操作，即：

SELECT day::date
FROM generate_series('2010-01-20', '2010-01-24', INTERVAL '1 day') day;

    day
------------
 2010-01-20
 2010-01-21
 2010-01-22
 2010-01-23
 2010-01-24
(5 rows)

- Dmitry Gusev

Answer 4

使用递归的公用表达式（CTE），您可以生成日期列表，然后从中选择。显然，通常您不会想创建三百万个日期，因此这只是说明了可能性。您可以在CTE内简单地限制日期范围，并且在使用CTE的选择语句中省略where子句。

with [dates] as (
    select convert(datetime, '1753-01-01') as [date] --start
    union all
    select dateadd(day, 1, [date])
    from [dates]
    where [date] < '9999-12-31' --end
)
select [date]
from [dates]
where [date] between '2013-01-01' and '2013-12-31'
option (maxrecursion 0)

在 Microsoft SQL Server 2005 上，生成所有可能日期的CTE列表需要1分08秒。生成100年只需要不到一秒钟。

- Joshua

Answer 5

MSSQL查询

select datetable.Date 
from (
    select DATEADD(day,-(a.a + (10 * b.a) + (100 * c.a)),getdate()) AS Date
    from (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4
     union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as a

    cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4
     union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as b

    cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4
     union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as c
) datetable
where datetable.Date between '2014-01-20' and '2014-01-24' 
order by datetable.Date DESC

输出

Date
-----
2014-01-23 12:35:25.250
2014-01-22 12:35:25.250
2014-01-21 12:35:25.250
2014-01-20 12:35:25.250

- SUHAIL AG

---

如果我能再往下滚一点就好了...唉。不管怎样，谢谢你。我添加了一个CAST( <expression> AS DATE)来删除我的版本中的时间。还使用了where a.Date between GETDATE() - 365 AND GETDATE() ...如果你今天运行查询，如果你没有注意到WHERE中的日期，它将不会返回任何行=P - Ricardo C

Answer 6

在不使用循环/游标的情况下执行此操作的传统方法是创建一个NUMBERS表，该表具有一个整数列，其值从1开始。

CREATE TABLE  `example`.`numbers` (
  `id` int(10) unsigned NOT NULL auto_increment,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

您需要填充足够的记录以满足您的需求：

INSERT INTO NUMBERS (id) VALUES (NULL);

一旦您拥有了NUMBERS表，您可以使用以下内容：

SELECT x.start_date + INTERVAL n.id-1 DAY
  FROM NUMBERS n
  JOIN (SELECT STR_TO_DATE('2010-01-20', '%Y-%m-%d') AS start_date 
          FROM DUAL) x
 WHERE x.start_date + INTERVAL n.id-1 DAY <= '2010-01-24'

绝对的低技术解决方案是：

SELECT STR_TO_DATE('2010-01-20', '%Y-%m-%d')
 FROM DUAL
UNION ALL
SELECT STR_TO_DATE('2010-01-21', '%Y-%m-%d')
 FROM DUAL
UNION ALL
SELECT STR_TO_DATE('2010-01-22', '%Y-%m-%d')
 FROM DUAL
UNION ALL
SELECT STR_TO_DATE('2010-01-23', '%Y-%m-%d')
 FROM DUAL
UNION ALL
SELECT STR_TO_DATE('2010-01-24', '%Y-%m-%d')
 FROM DUAL

你会用它做什么？

---

生成按顺序排列的日期或数字列表，以便进行LEFT JOIN。 这样做是为了查看数据中存在的空缺，因为您正在将其LEFT JOIN到一组连续数据 - 空值将使间隙明显。

- OMG Ponies

---

DUAL 表在 Oracle 和 MySQL 中都被支持，可以作为 FROM 子句中的替代表使用。该表并不存在，从中选择值将返回任意值。这个想法是为了提供一个替代，因为一个 SELECT 查询需要至少指定一个表作为 FROM 子句的一部分。 - OMG Ponies

创建一个永久数字表，而不是每次需要查询时让关系型数据库管理系统构建它，这样做是正确的。辅助表并不邪恶，人们应该明白！ - Bacon Bits

Answer 7

对于Access 2010 - 需要多个步骤; 我按照上面发布的相同模式进行操作，但认为我可以帮助使用Access的人员。 对我非常有用，我不必保留种子日期表。

创建一个名为DUAL的表(类似于Oracle DUAL表的工作方式)

- ID (自动编号) - DummyColumn (文本) - 添加一行值(1，“DummyRow”)

创建一个名为“ZeroThru9Q”的查询； 手动输入以下语法:

SELECT 0 AS a
FROM dual
UNION ALL
SELECT 1
FROM dual
UNION ALL
SELECT 2
FROM dual
UNION ALL
SELECT 3
FROM dual
UNION ALL
SELECT 4
FROM dual
UNION ALL
SELECT 5
FROM dual
UNION ALL
SELECT 6
FROM dual
UNION ALL
SELECT 7
FROM dual
UNION ALL
SELECT 8
FROM dual
UNION ALL
SELECT 9
FROM dual;

创建名为“TodayMinus1KQ”的查询（用于查询今天之前的日期）；手动输入以下语法：

SELECT date() - (a.a + (10 * b.a) + (100 * c.a)) AS MyDate
FROM
  (SELECT *
   FROM ZeroThru9Q) AS a,

  (SELECT *
   FROM ZeroThru9Q) AS b,

  (SELECT *
   FROM ZeroThru9Q) AS c

创建一个名为“TodayPlus1KQ”的查询（用于今天之后的日期）；手动输入以下语法：

SELECT date() + (a.a + (10 * b.a) + (100 * c.a)) AS MyDate
FROM
  (SELECT *
   FROM ZeroThru9Q) AS a,

  (SELECT *
   FROM ZeroThru9Q) AS b,

  (SELECT *
   FROM ZeroThru9Q) AS c;

创建一个名为"TodayPlusMinus1KQ"的联合查询（日期范围为+/- 1000天）：

SELECT MyDate
FROM TodayMinus1KQ
UNION
SELECT MyDate
FROM TodayPlus1KQ;

现在您可以使用以下查询：

SELECT MyDate
FROM TodayPlusMinus1KQ
WHERE MyDate BETWEEN #05/01/2014# and #05/30/2014#

- Travis

Answer 8

在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'之间的日期表格。速度也相当快。在我的机器上，返回1000年的日期（约365,000天）大约需要400毫秒。

- Brad

Answer 9

谢谢 Pentium10 - 是你让我成为了 stackoverflow 的一员 :) - 这是我将代码迁移到 msaccess 的版本 - 我想它可以在任何版本上运行:

SELECT date_value
FROM (SELECT a.espr1+(10*b.espr1)+(100*c.espr1) AS integer_value,
dateadd("d",integer_value,dateserial([start_year], [start_month], [start_day])) as date_value
FROM (select * from 
    (
    select top 1 "0" as espr1 from MSysObjects
    union all
    select top 1 "1" as espr2 from MSysObjects
    union all
    select top 1 "2" as espr3 from MSysObjects
    union all
    select top 1 "3" as espr4 from MSysObjects
    union all
    select top 1 "4" as espr5 from MSysObjects
    union all
    select top 1 "5" as espr6 from MSysObjects
    union all
    select top 1 "6" as espr7 from MSysObjects
    union all
    select top 1 "7" as espr8 from MSysObjects
    union all
    select top 1 "8" as espr9 from MSysObjects
    union all
    select top 1 "9" as espr9 from MSysObjects
    ) as a,
    (
    select top 1 "0" as espr1 from MSysObjects
    union all
    select top 1 "1" as espr2 from MSysObjects
    union all
    select top 1 "2" as espr3 from MSysObjects
    union all
    select top 1 "3" as espr4 from MSysObjects
    union all
    select top 1 "4" as espr5 from MSysObjects
    union all
    select top 1 "5" as espr6 from MSysObjects
    union all
    select top 1 "6" as espr7 from MSysObjects
    union all
    select top 1 "7" as espr8 from MSysObjects
    union all
    select top 1 "8" as espr9 from MSysObjects
    union all
    select top 1 "9" as espr9 from MSysObjects
    ) as b,
    (
    select top 1 "0" as espr1 from MSysObjects
    union all
    select top 1 "1" as espr2 from MSysObjects
    union all
    select top 1 "2" as espr3 from MSysObjects
    union all
    select top 1 "3" as espr4 from MSysObjects
    union all
    select top 1 "4" as espr5 from MSysObjects
    union all
    select top 1 "5" as espr6 from MSysObjects
    union all
    select top 1 "6" as espr7 from MSysObjects
    union all
    select top 1 "7" as espr8 from MSysObjects
    union all
    select top 1 "8" as espr9 from MSysObjects
    union all
    select top 1 "9" as espr9 from MSysObjects
    ) as c   
)  as d) 
WHERE date_value 
between dateserial([start_year], [start_month], [start_day]) 
and dateserial([end_year], [end_month], [end_day]);

MSysObjects是指引用的表，因为Access需要在from子句中至少统计一个记录数 - 任何有至少1个记录的表都可以做到。

- user3780177

Answer 10

存储过程 + 临时表：

DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `days`(IN dateStart DATE, IN dateEnd DATE)
BEGIN

    CREATE TEMPORARY TABLE IF NOT EXISTS date_range (day DATE);

    WHILE dateStart <= dateEnd DO
      INSERT INTO date_range VALUES (dateStart);
      SET dateStart = DATE_ADD(dateStart, INTERVAL 1 DAY);
    END WHILE;

    SELECT * FROM date_range;
    DROP TEMPORARY TABLE IF EXISTS date_range;

END

- Marcio J