CODEWITHSUNDEEP
phpsqlpostgresql
Michael Guthrie
Michael Guthrie

Reputation: 522

Postgresql search for items older than a threshold

Seems like this should be a simple thing to do, but the date/time operations in postgresql are killing me. All I'm looking to do is search for items older than 10 minutes. Coding language is PHP.

Field event_time is: timestamp without time zone NOT NULL

$sql = "SELECT * FROM incidents WHERE 
        event_time < ( now() - interval '-{$threshold} minutes' )
        AND submitted=0 ";

This query is returning all results instead of items older than 10 minutes. Do I need to type cast that event_time field somehow?

Upvotes: 1

Views: 4281

Answers (1)

Mike Brant
Mike Brant

Reputation: 71424

By using a negative threshold value, you have a double negative, so you are searching for everything less than ten minutes in the future (which would naturally return everything). Just use interval '{$threshold} minutes}'

Upvotes: 3

Related Questions