CODEWITHSUNDEEP
sqlpostgresqlgenerate-series
gibbz00
gibbz00

Reputation: 1987

Creating N days interval function in postgresql

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:

2020-01-10
2020-01-08
2020-01-06
2020-01-04
2020-01-02

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;

Upvotes: 1

Views: 340

Answers (1)

user330315
user330315

Reputation:

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;

Upvotes: 4

Related Questions