CODEWITHSUNDEEP
postgresql
dan
dan

Reputation: 45622

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.

Upvotes: 135

Views: 104791

Answers (5)

paperskilltrees
paperskilltrees

Reputation: 382

For Postgres version >= 14, you can use date_bin(), which has the signature

date_bin(stride : INTERVAL, source : TIMESTAMP, origin : TIMESTAMP) : TIMESTAMP

An example from the aforelinked PostgreSQL Docs:

SELECT date_bin('15 minutes', TIMESTAMP '2020-02-11 15:44:17', TIMESTAMP '2001-01-01');

Result: 2020-02-11 15:30:00

SELECT date_bin('15 minutes', TIMESTAMP '2020-02-11 15:44:17', TIMESTAMP '2001-01-01 00:02:30');

Result: 2020-02-11 15:32:30

Upvotes: 0

James De Souza
James De Souza

Reputation: 668

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

Upvotes: 6

Peter Krauss
Peter Krauss

Reputation: 13920

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.

Upvotes: 24

crowmagnumb
crowmagnumb

Reputation: 7107

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;

Upvotes: 8

Bohemian
Bohemian

Reputation: 425003

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

Upvotes: 235

Related Questions