Thread: How to add a variable to a timestamp.
Hi,
I'm trying to do something like this.
SELECT
d.i,
h.i,
'2022-10-31 00:00:00'::TIMESTAMP + INTERVAL 'd.i DAY'
FROM
GENERATE_SERIES(0, 6) AS d(i),
GENERATE_SERIES(0, 23) AS h(i);
where I add d.i days (and also h.i hours) to a timestamp.
I can't seem to get this to work. Any ideas appreciated.
E.
> On 29/10/2022 19:35 CEST Eagna <eagna@protonmail.com> wrote: > > I'm trying to do something like this. > > SELECT > d.i, > h.i, > > '2022-10-31 00:00:00'::TIMESTAMP + INTERVAL 'd.i DAY' > FROM > GENERATE_SERIES(0, 6) AS d(i), > GENERATE_SERIES(0, 23) AS h(i); > > where I add d.i days (and also h.i hours) to a timestamp. > > I can't seem to get this to work. Any ideas appreciated. Create the interval with make_interval(days => d.i, hours => h.i). https://www.postgresql.org/docs/15/functions-datetime.html#id-1.5.8.15.6.2.2.28.1.1.1 -- Erik
On 10/29/22 10:35, Eagna wrote: > > Hi, > > I'm trying to do something like this. > > SELECT > d.i, > h.i, > > '2022-10-31 00:00:00'::TIMESTAMP + INTERVAL 'd.i DAY' > FROM > GENERATE_SERIES(0, 6) AS d(i), > GENERATE_SERIES(0, 23) AS h(i); > > where I add d.i days (and also h.i hours) to a timestamp. > > I can't seem to get this to work. Any ideas appreciated. SELECT d.i, h.i, '2022-10-31 00:00:00'::TIMESTAMP + (d.i::text || ' DAY ' || h.i::text || ' HOUR')::interval FROM GENERATE_SERIES(0, 6) AS d(i), GENERATE_SERIES(0, 23) AS h(i); > > > E. > > Sent with Proton Mail <https://proton.me/> secure email. > > -- Adrian Klaver adrian.klaver@aklaver.com
Adrian Klaver <adrian.klaver@aklaver.com> writes: > On 10/29/22 10:35, Eagna wrote: >> I'm trying to do something like this. >> '2022-10-31 00:00:00'::TIMESTAMP + INTERVAL 'd.i DAY' That will not work. A literal is a literal, you can't expect that the system will interpret parts of it as variable references. > '2022-10-31 00:00:00'::TIMESTAMP + (d.i::text || ' DAY ' || h.i::text > || ' HOUR')::interval That'll work, but my what a kluge. More recommendable is '2022-10-31 00:00:00'::TIMESTAMP + d.i * '1 day'::interval + h.i * '1 hour'::interval (Or you can spell the constants like INTERVAL '1 day', if you prefer.) regards, tom lane