Postgres generate_series排除日期范围

Question

我正在创建一个订阅管理系统，需要生成未来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应该从计算中排除该星期的日期，并且基本上将所有未来一个月的账单日期推到未来一周:

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

Answer 1

首先创建未来两年的日期时间序列，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

(后一个查询遵循Craig的建议按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;

Answer 2

您将不得不拆分调用以生成排除序列。像这样:

• 3查询并集
• 第一个查询从
中提取从开始到排除范围的日期
• 第二个查询提取排除范围到结束日期之间的日期
• 第三个查询提取的日期当您的系列日期没有交叉排除范围

注意:您仍然需要一种循环遍历排除列表的方法(如果有的话)。此外，这个查询可能不是很有效，因为这种情况可以通过函数或过程代码更好地处理。