在php/mysql中将时间舍入到最接近的15分钟 [英] rounding time to the nearest 15 minutes in php/mysql

问题描述

我有一个带有time字段的MySQL表，用于存储广告的发布时间.我需要按15分钟的时间对它们进行分组并显示.

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.

示例:

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

以此类推，直到24:00！因此，我需要做两件事:

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

1)将整天划分为15分钟，并且

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

2)将广告投放到最近的时间.

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

有什么主意吗?我只能使用PHP/MySQL.

Any idea? I can use only PHP/MySQL.

推荐答案

尝试一下-

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

其中900是60秒* 15分钟.

Where 900 is 60 seconds * 15 minutes.

这篇关于在php/mysql中将时间舍入到最接近的15分钟的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！