PostgreSQL query - return all results with field with past 5 minutes

Question

Hey everyone. Here is my situation... I need to craft a sql query against a postgresql server that will return all records created within the past 5 minutes, rounded down to the lowest minute. So if cron kicks the query off at 12:05:25.000, it needs to query all records created since 12:00:00.000. So I guess I really have two issues.

I know using

DATE_SUB(now(),INTERVAL 5 MINUTE)

works in mysql - is there an equivalent in postgresql? I couldn't find any mention of it in the date/time functions documentation here.
Second, how do I tell it to round down to the minute (ignore seconds) for the 5 minute interval?

Any advice would be appreciated.

Mike Sherrill 'Cat Recall' · Accepted Answer · 2011-05-26 21:09:00Z

8

select current_timestamp - interval '5' minute

And to eliminate the seconds

select current_timestamp - interval '5' minute - 
  (extract(seconds from current_timestamp) || 'seconds')::interval

answered May 26, 2011 at 21:09

Mike Sherrill 'Cat Recall'

96.6k20 gold badges134 silver badges195 bronze badges

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

1 Comment

araqnid Over a year ago

or date_trunc('minute', t) to eliminate the seconds.

Collectives™ on Stack Overflow

PostgreSQL query - return all results with field with past 5 minutes

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related