How to add a variable to a timestamp.

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/&gt; 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