CODEWITHSUNDEEP
sqlpostgresql
TJ Shah
TJ Shah

Reputation: 445

Query using two column values to create range

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

Upvotes: 0

Views: 264

Answers (2)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656982

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

Upvotes: 2

Michael Fredrickson
Michael Fredrickson

Reputation: 37388

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

Upvotes: 1

Related Questions