From: | Shammat <shammat(at)gmx(dot)net> |
---|---|
To: | pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | Re: Passing a dynamic interval to generate_series() |
Date: | 2024-07-01 07:37:24 |
Message-ID: | e514289e-d513-483f-850d-7d61e797a5b2@gmx.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Igal Sapir schrieb am 01.07.2024 um 00:39:
> I am trying to pass a dynamic interval to generate_series() with date range.
>
> This works as expected, and generates a series with an interval of 1 month:
>
> SELECT generate_series(
> date_trunc('month', current_date),
> date_trunc('month', current_date + interval '7 month'),
> interval '1 month'
> )
>
>
> This works as expected and returns an interval of 1 month:
>
> SELECT ('1 ' || 'month')::interval;
>
>
> But this throws an error (SQL Error [42601]: ERROR: syntax error at or near "'1 '"):
>
> SELECT generate_series(
> date_trunc('month', current_date),
> date_trunc('month', current_date + interval '7 month'),
> interval ('1 ' || 'month')::interval
> )
I am a fan of make_interval() when it comes to creating intervals from dynamic parameters:
SELECT generate_series(
date_trunc('month', current_date),
date_trunc('month', current_date + interval '7 month'),
make_interval(months => 1)
)
The value for make_interval() can e.g. passed as a parameter from your programming language.
From | Date | Subject | |
---|---|---|---|
Next Message | Dominique Devienne | 2024-07-01 08:33:09 | MVCC: Using function doing INSERT and returning PK in WHERE clause of SELECT statement |
Previous Message | Francisco Olarte | 2024-07-01 07:20:11 | Re: Passing a dynamic interval to generate_series() |