PostgreSQL query for current minute

Question

My web application needs to get the results of all the items which have their time value for this minute. My table has a time attributes

id, message, time, ...,

where time is of type timestamp with timezone. I need to get a list of all entries where the time is the current minute. Comparing the entire value does not match because the timestamp has microsecond resolution.

Currently I am using the following query to return this list and it works, but I think there should be a more efficient way of doing this.

SELECT * FROM notification WHERE 
 date_part('year', time) = date_part('year', current_date) AND 
 date_part('month', time) = date_part('month', current_date) AND 
 date_part('day', time) = date_part('day', current_date) AND 
 date_part('hour', time) = date_part('hour', current_time) AND 
 date_part('minute', time) = date_part('minute', current_time);

I'd also like to know how to get the results for the this and the previous minute or the last 'n' minutes. Database is PostgreSQL 8.4.3

Thanks

Answer 1

I'd try something like this:

WHERE
    time >= date_trunc('minute', now()) AND
    time < date_trunc('minute', now()) + INTERVAL '1 minute';

This will define the boundaries and search for times within that range, which means it still can use the index you have (or should have) on the time column.

Answer 2

What about simply comparing the truncated timestamps?

SELECT *
  FROM notification n
 WHERE date_trunc('minute', n.time) = date_trunc('minute', current_timestamp);

I doubt that the optimizer is clever enough to make use of an index on the time column using this approach though. If you have such an index, then you should probably use Lukáš Lalinský's suggestion if you want better performance. Depends how much data you have really.