PostgreSQL query for elapsed interval

Question

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

select now() - (10::varchar || ' min')::interval; 2021-10-15 08:35:07.988718-07, where you would substitute frequency for fixed value in the example. — Adrian Klaver
– Adrian Klaver, Commented Oct 15, 2021 at 15:45
@luckongas Well, as I have written above, processed_at is a timestamp of the last run of a program/process and frequency is an interval in minutes, in which program should run. So say, I will query a database every minute, to see which programs are eligible to run again — Petr Bělecký
– Petr Bělecký, Commented Oct 15, 2021 at 15:46
@AdrianKlaver Would you be so kind and explain the query, please? — Petr Bělecký
– Petr Bělecký, Commented Oct 15, 2021 at 15:47

Adrian Klaver · Accepted Answer · 2021-10-15 16:09:38Z

1

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.

edited Oct 15, 2021 at 16:09

answered Oct 15, 2021 at 15:52

Adrian Klaver

20.3k3 gold badges24 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Brandon Over a year ago

It will likely be more efficient to go the other direction - subtract the interval from now and then query for <= that smaller timestamp. Adding would likely preclude index usage.

Collectives™ on Stack Overflow

PostgreSQL query for elapsed interval

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related