interpolate column into postgres interval

Question

I have a Postgresql table I'm using to store information about scheduled processes, including the last time a process was executed. Different processes have different requirements about how often they run.

I pull a list of processes I need to re-run like this:

SELECT * FROM processes WHERE last_run < now() - interval '2 hours'

I've added a new column called exec_interval that has a value in minutes of how often the task should run so I can do away with the hard-coded value.

I'd like to do something like this:

SELECT * FROM processes WHERE last_run < now() - interval exec_interval || ' minutes'

But that throws a syntax error. Is there an accepted way to handle this scenario?

SELECT * FROM processes WHERE last_run < now() - (exec_interval||' minute')::interval — Oto Shavadze
– Oto Shavadze, Commented Jul 18, 2017 at 22:47

joanolo · Accepted Answer · 2017-07-18 23:32:32Z

2

The cleanest way to do this is probably:

SELECT 
     * 
FROM 
    processes 
WHERE 
    last_run < now() - exec_interval * interval '1 minute' ;

Or, probably better:

SELECT 
     * 
FROM 
    processes 
WHERE 
    last_run + exec_interval * interval '1 minute' < now() ;

(that is, you keep everything that can be computed from one row of te table on just one side of the <, instead of having columns (or f(columns)) in both sides)

... together with the following (functional) index:

CREATE INDEX idx_next_run 
    ON processes ( (last_run + (exec_interval * interval '1 minutes') ) ) ;

which will allow for a nice execution plan such as:

| QUERY PLAN                                                                                                                     |
| :----------------------------------------------------------------------------------------------------------------------------- |
| Bitmap Heap Scan on processes  (cost=1060.05..2799.58 rows=49001 width=16) (actual time=10.007..19.612 rows=49792 loops=1)     |
|   Recheck Cond: ((last_run + ((exec_interval)::double precision * '00:01:00'::interval)) < now())                              |
|   Heap Blocks: exact=637                                                                                                       |
|   ->  Bitmap Index Scan on idx_next_run  (cost=0.00..1047.80 rows=49001 width=0) (actual time=9.919..9.919 rows=49792 loops=1) |
|         Index Cond: ((last_run + ((exec_interval)::double precision * '00:01:00'::interval)) < now())                          |
| Planning time: 0.204 ms                                                                                                        |
| Execution time: 23.619 ms                                                                                                      |

Check all the setup and several smallish variations at dbfiddle here

edited Jul 18, 2017 at 23:32

answered Jul 18, 2017 at 22:54

joanolo

6,3881 gold badge33 silver badges40 bronze badges

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

2 Comments

Abelisto Over a year ago

Strange that many forget about the nice function make_interval(): make_interval(mins := exec_interval)

joanolo Over a year ago

@Abelisto: It's nice to have, but I've always thought that n * interval '1 minute' is closer to the SQL standard...

Collectives™ on Stack Overflow

interpolate column into postgres interval

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related