Query using two column values to create range

Question

I have a table with a start time timestamp column and a length integer column representing minutes. I want to be able to do something like this:

SELECT * FROM table t 
    WHERE t.start_time + interval 't.length minutes' < '2011-10-21';

But this doesn't work. Is there any way to get a new timestamp from the two column values?

I'm running version 8.3 of postgresql

Answer 1

SELECT *
FROM   table 
WHERE  (start_time + interval '1 min' * length_minutes) < '2011-10-21 0:0'::timestamp;

Notes

• Simply multiply your integer with 1-minute intervals and add it to the timestamp.
• It is slightly faster to compare the timestamp to a timestamp. A date would have to be cast to timestamp (automatically).

Answer 2

You need to cast t.length as a character and then append it... try this instead?

SELECT * 
FROM table t 
WHERE 
    t.start_time 
    + cast(cast(t.length as character varying) || ' minutes' as interval) 
    < '2011-10-21';