如何将时间转到最近的15分钟段?

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

我怎么能在MySQL中来回移动到最近的15分钟(例如，0,15,30,45)?

3 个解决方案

#1

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.

在这个示例中，我使用了CURTIME()作为输入时间，但是您可以使用任何时间字段。

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

900秒=15分钟(周期为)，450秒等于(提供四舍五入元素)的一半。我已经测试了1800/900，以得到最近的半小时，应该和其他人一起工作(600/300,10分钟等)。

#2

SELECT FROM_UNIXTIME( TRUNCATE(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.

这可以推广到任意时间值。900秒= 15分钟。你可以用其他的四舍五入因子替换900。

#3

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;

#1

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