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

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 2

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 :)