Creating N days interval function in postgresql

Question

I am trying to create a function in postgresql that returns a set of dates in between two dates with a certain interval. For example, If I pass on these two date parameters like most_recent_day = 2020-01-10 and mininum_day = 2020-01-01 and this interval parameter n_day_group_interval = 5, the function will return the following set:

Following was my attempt that gives an syntax error at or near "end". Thank you for your help.

create function get_n_day_group_dates(most_recent_day date, minimum_day date, n_day_group_interval varchar)
returns table (
    n_day_group_dates date
              )
    as $body$
    begin
        return query

    SELECT
                      date_trunc('day'::text, dd.dd)::date AS n_day_group_dates
               FROM

                    LATERAL generate_series(most_recent_day,
                        minimum_day,
                       '-'||n_day_group_interval||' day'::interval) dd(dd)
end;
    $body$
language plpgsql;

user330315 · Accepted Answer · 2020-07-24 21:07:25Z

4

You are overcomplicating things. LATERAL is not required and date_trunc('day', ..) is the same as casting to date.

To create an interval based on a variable, the easiest way is to use make_interval()

create function get_n_day_group_dates(most_recent_day date, minimum_day date, n_day_group_interval varchar)
returns table (n_day_group_dates date)
as $body$
  SELECT g.dt::date
  FROM generate_series(most_recent_day,
                       minimum_day,
                       make_interval(days => n_day_group_interval) as g(dt);
$body$
language sql;

answered Jul 24, 2020 at 21:07

user330315

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Creating N days interval function in postgresql

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related