Take the 2-minute tour ×

I'm building an application to record when my cat has an asthma attack. I'm not interested in the exact time since glancing at the time in interval of 15 minutes is easier to review (e.g. rounding 9:38am should be recorded as 9:45am).

I looked for a UDF at cflib.org for this but couldn't find one. I tinkered with CF's round function but I'm not getting it to do what I want.

Any advice?

share|improve this question
add comment

4 Answers

up vote 5 down vote accepted

This could do with a bit more polish (like data type validation) but it will take a time value and return it rounded to the nearest 15-minute increment.

function roundTo15(theTime) {
    var roundedMinutes = round(minute(theTime) / 15 ) * 15;
    var newHour=hour(theTime);
    if (roundedMinutes EQ 60) {
        newHour=newHour + 1;
    return timeFormat(createTime(newHour,roundedMinutes,0),"HH:mm");
share|improve this answer
everett Perfect or as my cat would say, Purrrrrfect. ;) As written, does this account for the 12 hour clock or 24 hour clock meaning does it show AM/PM? –  dlackey Jun 15 '11 at 19:14
I just added a tt to the time format as in return timeFormat(createTime(newHour,roundedMinutes,0),"HH:mm tt"); –  dlackey Jun 15 '11 at 19:17
add comment

I'm not familiar with the format of the timestamp here, but generally when I want to round I do something like floor((val + increment / 2) / increment) * increment

share|improve this answer
My first thought was to use floor similar to what you did but dealing with the time, this would need a modification to pull out the minute value from the time stamp. –  dlackey Jun 15 '11 at 19:14
add comment

I like Al Everett's answer, or alternatively store the actual time to preserve the most accurate time, then use query of query in the view and use between :00 and :15 to show the time in 15min period.

share|improve this answer
@Henry - that is not a bad idea, but @al-everett solution is a little simpler to implement for this small app that I'm building. –  dlackey Jun 15 '11 at 17:23
... or just do it in SQL. There are plenty of "round to x minutes" algorithms out there. (For next time ... :) –  Leigh Jun 15 '11 at 18:17
@Leigh how? I've never come across any of those.. I just use QoQ for my last project –  Henry Jun 15 '11 at 18:33
@Henry - Here is one idea. I have not used this one specifically. stackoverflow.com/questions/249794/how-to-round-a-time-in-t-sql –  Leigh Jun 15 '11 at 19:04
Two others for MS SQL: DATEADD(n, ROUND(DATEDIFF(n, 0, @Time) / @YourInterval, 0) * @YourInterval, 0) .. or .. dateadd(mi,(datepart(mi,@Day)/5)*5,dateadd(hh,datediff(hh,0,@Day),0)) source: sqlteam.com/forums/topic.asp?TOPIC_ID=64755 –  Leigh Jun 15 '11 at 19:17
show 2 more comments

If you use Henry's suggestion to store the precise time in the database (principle: if there's no cost, prefer to preserve data), then you can simply use Al Everett's rounding function whenever you display the data to the user.

share|improve this answer
add comment

Your Answer


