How to round to nearest X minutes with PL/pgSQL?

Question

How I can round to nearest X minutes?

Here's my attempt:

DECLARE
  _stamp ALIAS FOR $1; -- timestamp
  _nearest ALIAS FOR $2; -- minutes (integer)
  _minutes decimal;
  _ret timestamp;
BEGIN
  _ret := date_trunc('minute', _stamp);

  SELECT EXTRACT (minute FROM _ret)::integer INTO _minutes;

 IF (_minutes % _nearest < (_nearest / 2)) THEN
    RETURN _ret + _minutes * interval '1 minute';
  ELSE
    RETURN _ret - _minutes * interval '1 minute';
  END IF;

  RETURN _ret;
END;

Example:

SELECT round_to_nearest_minute ('2010-01-01 12:34:56', 15);

Should return

2010-01-01 12:30:00

Answer 1

PostgreSQL 14 will introduce date_bin function:

date_bin

date_bin ( interval, timestamp, timestamp ) → timestamp

The function date_bin “bins” the input timestamp into the specified interval (the stride) aligned with a specified origin.

In the case of full units (1 minute, 1 hour, etc.), it gives the same result as the analogous date_trunc call, but the difference is that date_bin can truncate to an arbitrary interval.

It this case it will be:

SELECT date_bin('15 minutes', TIMESTAMP '2010-01-01 12:34:56', TIMESTAMP '2010-01-01');
-- 2010-01-01 12:30:00

Answer 2

The round time function looks good. Another way to do it which will work for any unit of time without modification would be the following:

SELECT '1970-01-01'::timestamptz + EXTRACT(epoch FROM now())::integer / 300
            * 300 * interval '1 second';

Where the two references to 300 are the number of seconds you wish to round to, e.g. 300 = 5 minutes. By using integer maths you are truncating and then multiplying out the number of seconds from the epoch to give the rounded value.

By using a simple cast this will always round down, but you could change the rounding to round to nearest if you wanted.

If you want to round to the nearest 15 minutes then use 900 instead of 300. Nearest hour and a half would be 5400. Etc.

Answer 3

Further to my previous answer, here's a function that puts it all together and does exactly what you ask for:

CREATE FUNCTION date_round(base_date timestamptz, round_interval interval)
    RETURNS timestamptz AS $BODY$
SELECT '1970-01-01'::timestamptz 
    + (EXTRACT(epoch FROM $1)::integer + EXTRACT(epoch FROM $2)::integer / 2)
    / EXTRACT(epoch FROM $2)::integer
    * EXTRACT(epoch FROM $2)::integer * interval '1 second';
$BODY$ LANGUAGE SQL STABLE;

And here's an example of calling it:

SELECT date_round(now(), '15 minutes');

You can supply any interval you want and it should work. As coded it rounds to the nearest interval, either up or down.

If you wanted to truncate instead then just remove the + EXTRACT(epoch FROM $2)::integer / 2).

Hopefully this will now act as a definitive answer that accepts the correct argument types (interval rather than integer number of minutes).

Answer 4

Instead of adding or subtracting

_minutes * interval '1 minute'

you should be subtracting

(_minutes % _nearest) * interval '1 minute'

or adding

(_nearest - (_minutes % _nearest)) * interval '1 minute'

Answer 5

This could be also useful: A function to round a Timestamp up to 5 minutes. You can easily modify it to get it working with any field of the timestamp and any quantity of that field.

round_time function

Answer 6

You can do it as a simple SQL statement, substitute CURRENT_TIMESTAMP with your TIMESTAMP value.

SELECT date_trunc('minute', CURRENT_TIMESTAMP::timestamp without time zone)
    + (
    CASE WHEN extract(second from CURRENT_TIMESTAMP ) >= 30
        THEN 1::text
        ELSE 0::text
    END
    || ' minute'

    )::interval ;

An optimization, would be adding 30 seconds and then just doing date_trunc(), I knew this before but special thanks irc://irc.freenode.net/#postgresql's StuckMojo

SELECT date_trunc(
    'minute'
    , CURRENT_TIMESTAMP::timestamp without time zone
      + '30 seconds'::interval
);

update @stephen, sure it does -- even though technically speaking that isn't what the question called for.

CREATE OR REPLACE FUNCTION round_trunc ( in text, in timestamp ) RETURNS timestamp without time zone  AS $$
  SELECT pg_catalog.date_trunc(
      $1
      , $2::timestamp without time zone
        + ('0.5 ' || $1 )::interval
  )
$$ LANGUAGE sql VOLATILE;

Oh wait, I see what he is asking for, he wants to round to the nearest abstract sub-unit of time. Like a quarter hour, I'm way off here.

Answer 7

This seems to work:

DECLARE
  _stamp ALIAS FOR $1;
  _nearest ALIAS FOR $2;
  _seconds integer;
  _ret timestamp;
  _minutes decimal;
  _mod decimal;
BEGIN
  _ret := date_trunc('minute', _stamp);

  SELECT EXTRACT (minute FROM _ret)::integer INTO _minutes;

  _mod := _minutes % _nearest;

  IF (_mod > (_nearest / 2)) THEN
    RETURN _ret + (_nearest - _mod) * interval '1 minute';
  ELSE
    RETURN _ret - (_mod) * interval '1 minute';
  END IF;

  RETURN _ret;

END;

Thanks to Stephen Denne :)

Answer 8

Use the function date_trunc('src', timestamp [value]).

See the documentation: http://www.postgresql.org/docs/9.1/static/functions-datetime.html