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

Question

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

101

使用标准的MySQL函数，有没有一种方式可以编写一个查询来返回两个日期之间的天数列表。

例如，给定2009-01-01和2009-01-13，它将返回一个具有以下值的单列表：

编辑：看起来我没有表达清楚。我想要生成这个列表。我在数据库中存储了值（按日期时间），但希望将它们聚合到上面的日期列表的左外连接中（我希望在某些连接的右侧获取 null，并会处理这些 null）。

- Gilgad

2

我认为最好的解决方案在这个答案中描述：https://dev59.com/tXI95IYBdhLWcg3wyBCc#2157776 - Marek Gregor

1

可能是从日期范围生成天数的重复问题。 - Cees Timmerman

23个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- DevWL · Answer 1

没有函数，没有过程

SET @s := '2020-01-01';
SET @e := @s + INTERVAL 1 YEAR - INTERVAL 1 DAY; -- set end date to select

SELECT CAST((DATE(@s)+INTERVAL (H+T+U) DAY) AS DATE) d -- generate a list of 400 days starting from @s date so @e can not be more then @s + 
FROM ( SELECT 0 H
    UNION ALL SELECT 100 UNION ALL SELECT 200 UNION ALL SELECT 300
  ) H CROSS JOIN ( SELECT 0 T
    UNION ALL SELECT  10 UNION ALL SELECT  20 UNION ALL SELECT  30
    UNION ALL SELECT  40 UNION ALL SELECT  50 UNION ALL SELECT  60
    UNION ALL SELECT  70 UNION ALL SELECT  80 UNION ALL SELECT  90
  ) T CROSS JOIN ( SELECT 0 U
    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
  ) U
 -- generated lenght of date list can be calculated as fallow 1 * H(3 + 1) * T(9 + 1) * U(9 + 1) = 400
 -- it is crucial to preserve the numbering convention as 1, 2 ... 20, 30..., 100, 200, ... to retrieve chronological date selection
 -- add more UNION 400, 500, 600, ... H(6 + 1) if you want to select from more than 700 days!
WHERE
  (DATE(@s+INTERVAL (H+T+U) DAY)) <= DATE((@e))
ORDER BY d;

联合定义了可选日期的长度和范围，因此请务必确保其足够长。

SELECT 1 * (3 + 1) * (9 + 1) * (9 + 1); -- 400 days starting from from @s

基于这篇文章
测试并且按预期工作

- eed3si9n · Answer 2

-1

创建一个存储过程，它接受两个参数 a_begin 和 a_end。在其中创建一个名为 t 的临时表，声明一个变量 d，将 a_begin 赋值给 d，并运行一个 WHILE 循环，将 d 插入到 t 中并调用 ADDDATE 函数来递增 d 的值。最后执行 SELECT * FROM t。

- eed3si9n

实际上，我认为永久表在执行速度方面更好（存储要求最小）。将日期预先计算到表中并在需要时提取比每次需要时创建范围更快。 - paxdiablo

@Pax，这取决于获得的速度提升是否值得进行表维护。例如，如果报告生成需要3秒钟，而运行WHILE循环以生成日期只需要0.001秒钟，我认为性能提升是可以忽略不计的。Knuth：过早优化是万恶之源。 - Eugene Yokota

@eed3si9n，过早优化是不好的，但并不是所有的优化都不好 :-) 如果你的例子是正确的，那么我会同意你的观点，但需要进行确切的衡量。按我所建议的方式，如果你将表维护推迟1000年（一次性成本），那么这个成本就不存在了，但这些时间会累积起来。 - paxdiablo

- ArifMustafa · Answer 3

我正在使用服务器版本：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)

希望这能帮助到很多人。