CODEWITHSUNDEEP
phpmysql
Mirela
Mirela

Reputation: 461

rounding time to the nearest 15 minutes in php/mysql

I have a MySQL table with a time field that stores the time a commercial has been published. I need to group them by periods of 15 minutes and display.

Example:

            |comm1|comm2 |comm3|
--------------------------------
|8:00- 8:15 |   2 | 5    |  0  |
--------------------------------
|8:15- 8:30 |   0 | 0    |  1  |
--------------------------------
|8:30- 8:45 |   4 | 1    |  3  |
--------------------------------
|8:45- 9:00 |   4 | 5    |  2  |
--------------------------------
|...        |   . | .    |  .  |
--------------------------------

and so on till 24:00! So, I need two things to do:

1) the division in periods of 15 minutes of all day, and

2) round the time a commercial has run, to the nearest of these periods.

Any idea? I can use only PHP/MySQL.

Upvotes: 0

Views: 3465

Answers (2)

Devart
Devart

Reputation: 121902

Try this one - 

SELECT
  SEC_TO_TIME((TIME_TO_SEC(time_field) DIV 900) * 900) AS round_time
FROM table

Where 900 is 60 seconds * 15 minutes.

Upvotes: 3

Nin
Nin

Reputation: 3020

Wihtout knowing anything about your table it's kind of hard to say. But assuming you have a unix timestamp field you can get the starting hour+quarter like this:

SELECT concat( hour( from_unixtime( time ) ) , ':', floor( minute( from_unixtime( time ) ) %4 ) *15 ) FROM table

In this case time is your unix timestamp field. If you have a time/datetime field you can remove the FROM_UNIXTIME function.

Upvotes: 0

Related Questions