Postgres: how do you round a timestamp up or down to the nearest minute?

Is there a postgresql function that will return a timestamp rounded to the nearest minute? The input value is a timestamp and the return value should be a timestamp.

Solution 1:

Use the built-in function date_trunc(text, timestamp), for example:

select date_trunc('minute', now())

Edit: This truncates to the most recent minute. To get a rounded result, add 30 seconds to the timestamp first, for example:

select date_trunc('minute', now() + interval '30 second')

This returns the nearest minute.

See Postgres Date/Time Functions and Operators for more info

Solution 2:

Answer to a similar (and more generic) question,

"... to the nearest minute interval" (1-minute, 5-minutes, 10-minutes, etc.)

CREATE FUNCTION round_minutes(TIMESTAMP WITHOUT TIME ZONE, integer) 
RETURNS TIMESTAMP WITHOUT TIME ZONE AS $$ 
  SELECT 
     date_trunc('hour', $1) 
     +  cast(($2::varchar||' min') as interval) 
     * round( 
     (date_part('minute',$1)::float + date_part('second',$1)/ 60.)::float 
     / $2::float
      )
$$ LANGUAGE SQL IMMUTABLE;

CREATE FUNCTION round_minutes(TIMESTAMP WITHOUT TIME ZONE, integer,text) 
RETURNS text AS $$ 
  SELECT to_char(round_minutes($1,$2),$3)
$$ LANGUAGE SQL IMMUTABLE;

SELECT round_minutes('2010-09-17 16:23:12', 5);
-- 2010-09-17 16:25:00

SELECT round_minutes('2010-09-17 16:23:12', 10, 'HH24:MI');
-- 16:20

Adapted from http://wiki.postgresql.org/wiki/Round_time and to the "exact round" as @CrowMagnumb showed.