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?

https://stackoverflow.com/questions/9376350/postgresql-how-to-concat-interval-value-2-days — Oto Shavadze, Jul 18 '17 at 22:46
`SELECT * FROM processes WHERE last_run < now() - (exec_interval||' minute')::interval` — Oto Shavadze, Jul 18 '17 at 22:47

joanolo · Accepted Answer · 2017-07-18T23:32:32.753

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

Strange that many forget about the nice function `make_interval()`: `make_interval(mins := exec_interval)` — Abelisto, Jul 18 '17 at 23:16
@Abelisto: It's nice to have, but I've always thought that `n * interval '1 minute'` is closer to the SQL standard... — joanolo, Jul 18 '17 at 23:20

interpolate column into postgres interval

1 Answers1