PostgreSQL query for elapsed interval-CodePudding

I am trying to query PostgreSQL database for rows where interval has elapsed from the last run. Main columns for this purpose are processed_at as timestamptz and frequency (in minutes) as integer.

I am failing with operators, since not many of them can operate together timestamp & integer.

Can someone please propose a query that would solve this? Thank you very much for help

CodePudding user response：

From here Date/time operators:

timestamp interval → timestamp

Add an interval to a timestamp

timestamp '2001-09-28 01:00' interval '23 hours' → 2001-09-29 00:00:00


select now()   (10::varchar || ' min')::interval;
           ?column?            
-------------------------------
 2021-10-15 09:05:37.927163-07


--Or in your case. If I'm following you are adding the interval.

select processed_at   (frequency::varchar || ' min')::interval;

The query takes the integer value of minutes and converts it to an interval of minutes that can be added to the timestamp.

Further explanation, || is the Postgres concatenation operator and ::varchar, ::interval are casting shorthand.

UPDATE

I keep forgetting about the make_*() functions for date/time/interval

--A shorter version

select processed_at   make_interval(mins => frequency);

Saves all the casting.