Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to round a time to the nearest 15 minute segment

Tags:

How can I round times in MySQL to the nearest 15 minutes (e.g. 0,15,30,45)?

like image 780
donL Avatar asked Dec 04 '12 17:12

donL


People also ask

How do you round to the nearest 15 minutes?

When reporting time not worked, employees should round to the nearest 1/4 hour. For example: 15 minutes = 0.25 hours, 30 minutes = 0.50 hours and 45 minutes = 0.75 hours.

How do you round a number to the nearest 15 minutes in Excel?

You also can use this formula =MROUND(A2,15/60/24) to round time to nearest minute. 3. If you want to round time to previous 15 minute, use this formula =FLOOR(A2,15/60/24).

How do I round to the nearest 30 minutes in Excel?

1. Enter this formula: =MROUND(A2,"0:15") or =MROUND(A2,"0:30") into a blank cell beside your data which need to round to nearest quarter or half hour, and then drag the fill handle down to the cells you want to apply this formula, and you will get a list of decimal numbers, see screenshot: 2.


1 Answers

SELECT SEC_TO_TIME(FLOOR((TIME_TO_SEC(CURTIME())+450)/900)*900) 

In this example I have used CURTIME() for the input time, but you can use any time field.

900 seconds=15 minutes (the period to round to), 450 seconds is half that (to provide the rounding element). I've tested with 1800/900 to get nearest half hour, should work with others (600/300 for 10 minutes etc).

like image 63
Don Avatar answered Sep 17 '22 08:09

Don