How to round the minute of a timestamp to increments of 15?

Question

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?

score 5 · Accepted Answer · answered Jun 15 '11 at 16:43

5

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.

<cfscript>
function roundTo15(theTime) {
    var roundedMinutes = round(minute(theTime) / 15 ) * 15;
    var newHour=hour(theTime);
    if (roundedMinutes EQ 60) {
        newHour=newHour + 1;
        roundedMinutes=0;
    }
    return timeFormat(createTime(newHour,roundedMinutes,0),"HH:mm");
}
</cfscript>

answered Jun 15 '11 at 16:43

ale

6,369
7
55
65

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? – HPWD 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");` – HPWD Jun 15 '11 at 19:17

score 0 · Answer 2 · answered Jun 15 '11 at 16:24

0

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

answered Jun 15 '11 at 16:24

jpm

3,165
17
24

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. – HPWD Jun 15 '11 at 19:14

score 0 · Answer 3 · edited May 23 '17 at 10:00

0

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.

edited May 23 '17 at 10:00

Community

1
1

answered Jun 15 '11 at 16:50

Henry

32,689
19
120
221

@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. – HPWD 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. http://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: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=64755 – Leigh Jun 15 '11 at 19:17
@Leigh Hmm.. I'm sure they work but they're ugly functions and I guess the performance will be similar, yet CFML QoQ is cleaner? – Henry Jun 15 '11 at 20:41
@Henry - Part of me agrees with you ;) But keep in mind it is a matter of in-lining versus udf. The CF code would be pretty ugly as a one liner too. If you want it more readable/pretty, just make a function for it. Most db's support them. As far as performance, I would guess a scaler db function would be perform better. With a QoQ there is the overhead of rebuilding the whole set in memory. Just a guess though. – Leigh Jun 15 '11 at 21:21

score 0 · Answer 4 · answered Jun 18 '11 at 17:05

0

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.

answered Jun 18 '11 at 17:05

krubo

5,969
4
37
46

How to round the minute of a timestamp to increments of 15?

4 Answers4