postgresql - 如何根据开始日期的星期几计算下一个发生日期？

Question

我的事件表有开始日期和结束日期列。它还有一个循环 bool 列，用于将事件标记为循环发生。我只需要支持每周重复。我如何为这些重复发生的事件选择下一个合适的日期？

给定以下示例数据和编写本文的时间，我怎样才能得到以下结果？

startdate    enddate     nextdate
12/29/14     12/31/15    1/12/15
1/2/15       4/1/15      1/9/15
6/1/14       12/31/14    null
4/1/15       12/31/15    4/1/15

更新:我刚刚写了这个函数，但现在我想知道是否有更高效的东西......

CREATE OR REPLACE FUNCTION nextdowinrange(startdate timestamp with time zone, enddate timestamp with time zone)
  RETURNS timestamp with time zone AS
$BODY$
    DECLARE
        loopday int;
        nextdate timestamp with time zone;
        BEGIN

        --RANGE PAST
        IF enddate < current_timestamp THEN
            RETURN null;
        --RANGE NOT STARTED
        ELSIF startdate > current_timestamp THEN
            RETURN startdate;
        --SAME DAY OF WEEK
        ELSIF extract(dow from startdate) = extract(dow from current_timestamp) THEN
            RETURN startdate;
        --FIND NEXT
        ELSE
            loopday:= 0;
            LOOP
                IF extract(dow from now() + (loopday || ' days')::interval) = extract(dow from startdate) THEN
                    nextdate:= now() + (loopday || ' days')::interval;
                END IF;
                EXIT WHEN extract(dow from now() + (loopday || ' days')::interval) = extract(dow from startdate);
                loopday:= loopday + 1;

            END LOOP;
            RETURN nextdate;
        END IF;

        END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

Answer 1

可以通过简单的选择来完成，不需要自定义函数。

PostgreSQL 9.3+ 最简单的解决方案是 LATERAL加入:

select  t.*, nextdate
from    t
left join lateral (
  select nextdate
  from   generate_series(startdate, enddate, '1 week') nextdate
  where  nextdate > now()
  limit  1
) nd on true;

对于较旧的 PostgreSQL，您可以对 nextdate 列使用子选择:

select *, (select ds
           from   generate_series(startdate, enddate, '1 week') ds
           where  ds > now()
           limit  1) nextdate
from   t;

SQLFiddle