0

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.

Andriy M
  • 76,112
  • 17
  • 94
  • 154
Mirela
  • 461
  • 2
  • 10
  • 20
  • A quick search on stackoverflow shows these two questions which may help you http://stackoverflow.com/questions/3468163/rounding-a-mysql-datetime-to-earliest-15-minute-interval-in-milliseconds-php , http://stackoverflow.com/questions/2480637/round-minute-down-to-nearest-quarter-hour – bumperbox Oct 11 '12 at 09:03
  • 'SELECT CAST([Time] AS Time) AS [Time], COUNT(CASE WHEN DATEPART(minute, [Time]) BETWEEN 8:00 AND 8:15 THEN 1 ELSE NULL END) AS [comm 8:00-8:15], COUNT(CASE WHEN DATEPART(minute, [Time]) BETWEEN 8:15 AND -8:30 THEN 1 ELSE NULL END) AS [comm 8:15-8:30], ...so on FROM Table GROUP BY CAST([Time] AS Time)' – Mirela Oct 11 '12 at 09:07

2 Answers2

3

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.

Danny Beckett
  • 20,529
  • 24
  • 107
  • 134
Devart
  • 119,203
  • 23
  • 166
  • 186
  • this really seems a right,clear and easy answer! Let me try this too and i'll come back as soon as i can. Thanks a lot! – Mirela Oct 11 '12 at 09:19
0

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.

Nin
  • 2,960
  • 21
  • 30
  • Sorry for the lack of information, my table doesn't have a unix timestamp field. The "time" field has it's data like this: 6:19 -> referring hour: minute, in 24H format. i will try this out and let you know..thanks – Mirela Oct 11 '12 at 09:18