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

Question

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.

Answer 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

Answer 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.