Requesting assist with postgresql query

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 dow开发者_JAVA百科n 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.

Here is the current query:

select * from callhistory where created>date_trunc('minute', timestamp (current_timestamp-interval '5' minute));

It doesn't return anything. Also, the format of the created field is "2011-05-18 18:11:32.024."

Any help would be appreciated.

The syntax is your date_trunc is a bit off:

select *
from callhistory
where created > date_trunc('minute', current_timestamp - interval '5' minute)

You could also use now() in place of current_timestamp:

select *
from callhistory
where created > date_trunc('minute', now() - interval '5' minute)

And an example:

=> create table stuff (created timestamp not null);
=> insert into stuff (created) values (current_timestamp);
-- Wait a could seconds...
=> insert into stuff (created) values (current_timestamp);
=> select * from stuff where created > date_trunc('minute', current_timestamp - interval '5' minute);
          created           
----------------------------
 2011-06-01 11:32:55.672027
 2011-06-01 11:33:00.182953
=> select * from stuff where created > date_trunc('minute', current_timestamp - interval '7' day);
          created           
----------------------------
 2011-06-01 11:32:55.672027
 2011-06-01 11:33:00.182953

UPDATE: Looks like PostgreSQL version 8 is a little stricter on the format for interval, the fine manual says that you should use interval '$n $unit' where $n is the number of things and $unit is the time unit. Version 9 allows you to say interval '$n' $unit without complaint but version 8 converts your interval to 0 if you don't use the documented format. So, you should use this for both version 8 and version 9:

select *
from callhistory
where created > date_trunc('minute', current_timestamp - interval '5 minute')

Here is a sample test I did against a PostgreSQL 8.3 server. You did not mention the version you are running.

select current_timestamp, to_char((current_timestamp - '5 minutes'::interval), 'yyyy-mm-dd HH:mi:00')::timestamp;

The interval deducts the 5 minutes and to_char() method rounds down to the closest minute. If this is what you are looking for, then the query should look as follows:

select * from callhistory where created > to_char((current_timestamp - '5 minutes'::interval), 'yyyy-mm-dd HH:mi:00')::timestamp