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

Erwin Brandstetter · Accepted Answer · 2011-10-21 23:28:55Z

2

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

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).

answered Oct 21, 2011 at 23:23

668k159 gold badges1.2k silver badges1.3k bronze badges

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

Comments

Michael Fredrickson · Accepted Answer · 2011-10-21 23:01:56Z

1

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';

answered Oct 21, 2011 at 23:01

37.5k6 gold badges95 silver badges110 bronze badges