CODEWITHSUNDEEP
mysqltimerounding
donL
donL

Reputation: 1300

How to round a time to the nearest 15 minute segment

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

Upvotes: 25

Views: 16112

Answers (3)

Gavin Towey
Gavin Towey

Reputation: 3200

SELECT FROM_UNIXTIME( ROUND(UNIX_TIMESTAMP(NOW()) / 900,0)*900);

This can be generalized to round to any time value. 900 seconds = 15 minutes. You can replace the 900 with any other rounding factor.

Upvotes: 21

Don
Don

Reputation: 2377

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

Upvotes: 35

donL
donL

Reputation: 1300

Here is some rough code that I used that got the results really close to what I wanted. Because I didn't use seconds I just chose minutes near the half way point.

SELECT
       CASE 
            WHEN minute(timeIn) BETWEEN 0 and 7 THEN SEC_TO_TIME((TIME_TO_SEC(timeIn) DIV 3600) * 3600)
            WHEN minute(timeIn) BETWEEN 8 and 22 THEN ADDTIME(SEC_TO_TIME((TIME_TO_SEC(timeIn) DIV 3600) * 3600), '00:15:00')
            WHEN minute(timeIn) BETWEEN 23 and 37 THEN ADDTIME(SEC_TO_TIME((TIME_TO_SEC(timeIn) DIV 3600) * 3600), '00:30:00')
            WHEN minute(timeIn) BETWEEN 38 and 52 THEN ADDTIME(SEC_TO_TIME((TIME_TO_SEC(timeIn) DIV 3600) * 3600), '00:45:00')
            WHEN minute(timeIn) BETWEEN 53 and 59 THEN ADDTIME(SEC_TO_TIME((TIME_TO_SEC(timeIn) DIV 3600) * 3600), '01:00:00')
       END as 15_min
    FROM
      clock.punches;

Upvotes: 0

Related Questions