Обсуждение: 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/> 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