postgresql - Postgres generate_series 不包括日期范围

Question

我正在创建订阅管理系统，需要生成 future 2 年即将到来的计费日期列表。我已经能够使用 generate_series 来获取适当的日期:

SELECT i::DATE
FROM generate_series('2015-08-01', '2017-08-01', '1 month'::INTERVAL) i

我需要采取的最后一步是从计算中排除特定日期范围。这些排除的日期范围可以是任何时间范围。此外，不应将它们计入 generate_series 的时间范围。

例如，假设我们排除了从“2015-08-27”到“2015-09-03”的日期范围。生成的 generate_series 应该从计算中排除那一周的日期，并且基本上将所有 future 月份的账单日期推到 future 一周:

2015-08-01
2015-09-10
2015-10-10
2015-11-10
2015-12-10

Answer 1

首先，您创建了 future 两年日期的时间序列，EXCEPT 您的停电日期:

SELECT dt
FROM generate_series('2015-08-01'::date, '2017-08-01'::date, interval '1 day') AS s(dt)
EXCEPT
SELECT dt
FROM generate_series('2015-08-27'::date, '2015-09-03'::date, interval '1 day') as ex1(dt)

请注意，您可以根据需要拥有任意多个 EXCEPT 子句。对于个别停电日(与范围相反)，您可以使用 VALUES 子句而不是 SELECT。

然后您对该时间序列进行窗口化以生成计费天数的行号:

SELECT row_number() OVER (ORDER BY dt) AS rn, dt
FROM (<query above>) x

然后您选择您要计费的那些日子:

SELECT dt
FROM (<query above>) y
WHERE rn % 30 = 1; -- billing on the first day of the period

(后一个查询遵循克雷格的 30 天计费建议)

产量:

SELECT dt
FROM (
  SELECT row_number() OVER (ORDER BY dt) AS rn, dt
  FROM (
    SELECT dt
    FROM generate_series('2015-08-01'::date, '2017-08-01'::date, interval '1 day') AS s(dt)
    EXCEPT
    SELECT dt
    FROM generate_series('2015-08-27'::date, '2015-09-03'::date, interval '1 day') as ex1(dt)
  ) x
) y
WHERE rn % 30 = 1;