Welcome to ShenZhenJia Knowledge Sharing Community for programmer and developer-Open, Learning and Share
menu search
person
Welcome To Ask or Share your Answers For Others

Categories

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
See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
thumb_up_alt 0 like thumb_down_alt 0 dislike
942 views
Welcome To Ask or Share your Answers For Others

1 Answer

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'


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
thumb_up_alt 0 like thumb_down_alt 0 dislike
Welcome to ShenZhenJia Knowledge Sharing Community for programmer and developer-Open, Learning and Share
...