IBM DB2：生成两个日期之间的日期列表 [英] IBM DB2: Generate list of dates between two dates

问题描述

我需要一个查询，将输出两个给定日期之间的日期列表。

I need a query which will output a list of dates between two given dates.

例如，如果我的开始日期是23/02/2016和结束日期是02/03/2016，我期待以下输出：

For example, if my start date is 23/02/2016 and end date is 02/03/2016, I am expecting the following output:

Date
----
23/02/2016
24/02/2016
25/02/2016
26/02/2016
27/02/2016
28/02/2016
29/02/2016
01/03/2016
02/03/2016

另外，我需要上述使用SQL（不使用'WITH'语句或表）。请帮助。

Also, I need the above using SQL only (without the use of 'WITH' statement or tables). Please help.

推荐答案

我正在使用DB2 for iSeries，所以我会给你一个SQL的解决方案。目前我没有访问服务器，所以查询没有测试，但它应该工作。 编辑查询已经测试并正常工作

I am using ,ostly DB2 for iSeries, so I will give you an SQL only solution that works on it. Currently I don't have an access to the server, so the query is not tested but it should work. EDIT Query is already tested and working

SELECT
    d.min + num.n DAYS
FROM
    -- create inline table with min max date
    (VALUES(DATE('2015-02-28'), DATE('2016-03-01'))) AS d(min, max)
INNER JOIN
    -- create inline table with numbers from 0 to 999
    (
        SELECT
            n1.n + n10.n + n100.n AS n
        FROM
            (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) AS n1(n)
        CROSS JOIN
            (VALUES(0),(10),(20),(30),(40),(50),(60),(70),(80),(90)) AS n10(n)
        CROSS JOIN
            (VALUES(0),(100),(200),(300),(400),(500),(600),(700),(800),(900)) AS n100(n)
    ) AS num
ON
    d.min + num.n DAYS<= d.max
ORDER BY
    num.n;

如果您不想仅执行一次查询，则应考虑创建一个真实的表格循环的值：

if you don't want to execute the query only once, you should consider creating a real table with values for the loop:

CREATE TABLE dummy_loop AS (
    SELECT
        n1.n + n10.n + n100.n AS n
    FROM
        (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) AS n1(n)
    CROSS JOIN
        (VALUES(0),(10),(20),(30),(40),(50),(60),(70),(80),(90)) AS n10(n)
    CROSS JOIN
        (VALUES(0),(100),(200),(300),(400),(500),(600),(700),(800),(900)) AS n100(n)
) WITH DATA;

ALTER TABLE dummy_loop ADD PRIMARY KEY (dummy_loop.n);

这取决于您喜欢使用它的原因，但您甚至可以创建表说100年。只有100 * 365 = 36500行只有一个日期字段，所以表将是相当小和快速的联接。

It depends on the reason for which you like to use it, but you could even create table for lets say for 100 years. It will be only 100*365 = 36500 rows with just a date field, so the table will be quite small and fast for joins.

CREATE TABLE dummy_dates AS (
    SELECT
        DATE('1970-01-01') + (n1.n + n10.n + n100.n) DAYS AS date
    FROM
        (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) AS n1(n)
    CROSS JOIN
        (VALUES(0),(10),(20),(30),(40),(50),(60),(70),(80),(90)) AS n10(n)
    CROSS JOIN
        (VALUES(0),(100),(200),(300),(400),(500),(600),(700),(800),(900)) AS n100(n)
) WITH DATA;

ALTER TABLE dummy_dates ADD PRIMARY KEY (dummy_dates.date);

选择查询可能如下所示：

And the select query could look like:

SELECT
    *
FROM
    dummy_days
WHERE
    date BETWEEN(:startDate, :endDate);

编辑2 ：感谢@Lennart建议我已经更改了TABLE（VALUES （..，..，..））到VALES（..，..，..），因为他说TABLE是LATERAL的同义词，对我来说真是一个惊喜。

EDIT 2: Thanks to @Lennart suggestion I have changed TABLE(VALUES(..,..,..)) to VALES(..,..,..) because as he said TABLE is a synonym to LATERAL that was a real surprise for me.

编辑3 ：感谢@ godric7gt我已经删除了TIMESTAMPDIFF，并将从我的所有脚本中删除，因为正如文档中所述：

EDIT 3: Thanks to @godric7gt I have removed TIMESTAMPDIFF and will remove from all my scripts, because as it is said in the documentation:

将第二个参数中的信息（即时间戳持续时间）转换为第一个参数中指定的间隔类型时，将使用这些假设。 返回的估算值可能会变化几天。例如，如果要求1997-03-01-00.00.00和1997-02-01-00.00.00之间的差距（间隔16），则结果为30.这是因为时间戳之间的差异是1个月，一个月的假设为30天。

These assumptions are used when converting the information in the second argument, which is a timestamp duration, to the interval type specified in the first argument. The returned estimate may vary by a number of days. For example, if the number of days (interval 16) is requested for the difference between '1997-03-01-00.00.00' and '1997-02-01-00.00.00', the result is 30. This is because the difference between the timestamps is 1 month, and the assumption of 30 days in a month applies.

这是一个真正的惊喜，因为我总是相信这个功能的日子差异。

It was a real surprise, because I was always trust this function for days difference.

这篇关于IBM DB2：生成两个日期之间的日期列表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！