126

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.

dan
  • 43,914
  • 47
  • 153
  • 254

4 Answers4

209

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

Bohemian
  • 412,405
  • 93
  • 575
  • 722
21

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.

Peter Krauss
  • 13,174
  • 24
  • 167
  • 304
8

In trying to use Peter's answer above to create ceiling and floor functions I found that you have to take into account the seconds too when calling the rounding function. This here are my sets of functions that will round, floor, and ceiling timestamps.

CREATE OR REPLACE 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 / cast($2 as float)))
$$ LANGUAGE SQL IMMUTABLE STRICT;

CREATE OR REPLACE FUNCTION floor_minutes( TIMESTAMP WITHOUT TIME ZONE, integer ) 
RETURNS TIMESTAMP WITHOUT TIME ZONE AS $$ 
    SELECT round_minutes( $1 - cast((($2/2)::varchar ||' min') as interval ), $2 );
$$ LANGUAGE SQL IMMUTABLE STRICT;

CREATE OR REPLACE FUNCTION ceiling_minutes( TIMESTAMP WITHOUT TIME ZONE, integer ) 
RETURNS TIMESTAMP WITHOUT TIME ZONE AS $$ 
    SELECT round_minutes( $1 + cast((($2/2)::varchar ||' min') as interval ), $2 );
$$ LANGUAGE SQL IMMUTABLE STRICT;
Daniel
  • 27,718
  • 20
  • 89
  • 133
crowmagnumb
  • 6,621
  • 9
  • 33
  • 42
  • Thanks to the correction (!) and extension: now we have a complete "aproximate minutes" library. PS: I think you can add a `int DEFAULT 5` (second parameter) to simplify the most frequent use. – Peter Krauss Apr 03 '14 at 11:06
  • Ops, another issue: use `LANGUAGE SQL IMMUTABLE`. – Peter Krauss Apr 03 '14 at 11:11
  • This is not entirely correct: every odd 10 minute interval (10, 30, 50) will floor to the interval below it. e.g. `select floor_minutes('2022-12-14 22:30:00', 10);` returns `2022-12-14 22:20:00` while `select floor_minutes('2022-12-14 22:20:00', 10);` returns `2022-12-14 22:20:00` – Ignace Vau Dec 23 '22 at 23:16
5

to round down a timestamp

CREATE or replace FUNCTION date_round_down(base_date timestamptz, round_interval INTERVAL) 
RETURNS timestamptz AS $BODY$
            SELECT TO_TIMESTAMP(EXTRACT(epoch FROM date_trunc('hour', $1))::INTEGER + trunc((EXTRACT(epoch FROM $1)::INTEGER - EXTRACT(epoch FROM date_trunc('hour', $1))::INTEGER) / EXTRACT(epoch FROM $2)::INTEGER) * EXTRACT(epoch FROM $2)::INTEGER) 
$BODY$ LANGUAGE SQL STABLE;

SELECT date_round_down('2017-06-02 16:39:35', '15 minutes') -- 2017-06-02 16:30:35
James De Souza
  • 648
  • 1
  • 7
  • 20