Message-ID: <e514289e-d513-483f-850d-7d61e797a5b2@gmx.net>
Date: Mon, 1 Jul 2024 09:37:24 +0200
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: Passing a dynamic interval to generate_series()
To: pgsql-general@lists.postgresql.org
References: <CA+zig0-suhFUeS7xcJyZ=4HDFXJ3Kn2adXo3B4zHUzDFgMscCw@mail.gmail.com>
Content-Language: de-DE, en-US
From: Shammat <shammat@gmx.net>
In-Reply-To: <CA+zig0-suhFUeS7xcJyZ=4HDFXJ3Kn2adXo3B4zHUzDFgMscCw@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: quoted-printable
UI-OutboundReport: notjunk:1;M01:P0:IafUlf+TQzs=;XI/YX4bzxtdVXNQXNkEmetpZEHK
 z5CB02qQlwp9OBPdS1pB0VFbK57B+dcx1cObDK/0kAyLTv/qwEVIReakpeK/vd5DHWZAe5/ix
 M/O85YdTMs/TIZE6VXxXWoUXxMFRBjy2qn0NG9u4+q0Qx9aeczGHKV+5tEOaKYHvSC8oR/pNt
 +QrABB2Xr38bMCKSNnJFgdm8rCz7IJzSJu4WKCH7h8hlT9hzHycOyOp/0oke3tUuwe7oVNAoS
 ahIsBy8/Cd9JDAe81ilBzKmdKtKRsIc7f5dIot+XbhGEoE4zDXIJJLCaEbFPsK8EEz4YhKatD
 wrPZKt+4b7rW/sZ6EMJO0czfijd+bhzOMhNIyFqZGtYY5Brela2ARs8QfdpE4JTLtM0Iqhzan
 ozJtCGBBvim+rwdKUZBWJ+F6TNvGTsRPkojFHe1BxGEERTcdByVI2tkmZNRhCKv0/cK8RPz+L
 67J2LFTCW03UptutdtNtlk0+y8pNJ5O3hdbcdIb7Z6EYm4epD6vZ3ZTeGNGmuYZoIpS6FiHJQ
 Tdisdnt6XeobRYy370jUcqc1Q2gB4Q7geWD388pOHrewi+fzKdNcphitA8iL6psWja3jFx6qp
 i0DesKx602+LINdsBDU1Pvz41TGAJhXIExefUUEiHGtrL+DhhB7HKE/6zT3vYFVNmQN91qWVi
 fqNfRBB5M8Y5Xuaeh4CsxrTRsOrWm5SSzcYbLcd7DiSC0Vo70MlQMeaBz0tf/1C5L+moNht4t
 12/GeLxEX9orEVxMOg80wg5rKZ2SKl+6QjiReNSTrXzdbH1/6zTIGPybjKwg15EhVPmuW+ClG
 x7hr0pkCOhJOQLr10edhKoZj09RpCP5Dq1V77cX0S9KdY=
Archived-At: <https://www.postgresql.org/message-id/e514289e-d513-483f-850d-7d61e797a5b2%40gmx.net>
Precedence: bulk


Igal Sapir schrieb am 01.07.2024 um 00:39:
> I am trying to pass a dynamic interval to generate_series() with date ra=
nge.
>
> This works as expected, and generates a series with an interval of 1 mon=
th:
>
> SELECT generate_series(
> =C2=A0 =C2=A0 date_trunc('month', current_date),
> =C2=A0 =C2=A0 date_trunc('month', current_date + interval '7 month'),
> =C2=A0 =C2=A0 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 n=
ear "'1 '"):
>
> SELECT generate_series(
> =C2=A0 =C2=A0 date_trunc('month', current_date),
> =C2=A0 =C2=A0 date_trunc('month', current_date + interval '7 month'),
> =C2=A0 =C2=A0 interval ('1 ' || 'month')::interval
> )

I am a fan of make_interval() when it comes to creating intervals from dyn=
amic parameters:

SELECT generate_series(
    date_trunc('month', current_date),
    date_trunc('month', current_date + interval '7 month'),
    make_interval(months =3D> 1)
)


The value for make_interval() can e.g. passed as a parameter from your pro=
gramming language.