使用mySQL将时间四舍五入到最近的6分钟

I have a DATETIME field in mySQL (fromStamp) with a valid date/time stamp in it. I need to be able to round the minutes UP to the next 6 minute interval. Seconds should be ignored or treated as :00

我在mySQL (fromStamp)中有一个DATETIME字段，其中有一个有效的日期/时间戳。我需要能够在接下来的6分钟时间内完成每分钟。秒应该被忽略或当作:00

So, if my DATETIME is 2013-01-31 13:07:17 I need the result to be 2013-01-31 13:12:00 EXCEPT for times between :00 and :06 and :31 and :36 those need to be rounded DOWN.

所以，如果我的日期时间是2013-01-31 13:07:17，我需要结果是2013-01-31 13:12:00，除了在:00和:06和:31和:36之间的时间需要四舍五入。

 :00-06 round DOWN to 00
 :07-12 round UP to 12
 :13-18 round UP to 18
 :19-24 round UP to 24
 :24-30 round UP to 30
 :31-36 round DOWN to 30
 :37-42 round UP to 42
 :43-48 round UP to 48
 :49-54 round UP to 54
 :55-00 round UP to 00

I found a reasonable way to selectively round down using:

我找到了一种合理的方法来选择性地使用:

SELECT
  fromStamp, 
  CONCAT(DATE_FORMAT(`fromStamp`, '%Y-%m-%d'), ' ',
         SEC_TO_TIME((TIME_TO_SEC(`fromStamp`) DIV 360) * 360))
FROM `table` 
WHERE (
    DATE_FORMAT(`fromStamp`, '%i') BETWEEN 0 AND 6 
    OR DATE_FORMAT(`fromStamp`, '%i') BETWEEN 31 AND 36
);

Is there a good way of rounding everything else up to the next 6 minute interval? (I feel like I'm missing something simple)

是否有一种很好的方法可以在接下来的6分钟内完成所有的事情?(我觉得我错过了一些简单的东西)

UPDATE

更新

So I'm going with @mellamokb's solution but his comment regard seconds is right, it does come into play.

所以我采用了@mellamokb的解决方案但是他的评论认为秒是正确的，它确实起作用了。

A DATETIME of 2013-01-22 12:24:13 rounds UP to 12:30 which is not what is needed so I'll first do:

日期为2013-01-22 12:24:13的时候到12:30，这是不需要的所以我首先做:

UPDATE table SET fromStamp = CONCAT(DATE_FORMAT(fromStamp, '%Y-%m-%d %h:%i'), ':00');

to get rid of the seconds then his query which gets exactly what I'm looking for.

为了去掉秒，他的查询得到了我想要的。

3 个解决方案

#1

Rounding up is very similar logic, except you add 1 to shift to the next nearest 6-minute interval. I've also subtracted 1 from TIME_TO_SEC as a correcting factor, otherwise borderline values such as 06:00 get shifted up the next 6-minute interval, i.e., 12:00.

舍入是非常相似的逻辑，除了你加1到下一个最近的6分钟间隔。我还从TIME_TO_SEC中减去了1，作为一个修正因子，否则，如06:00这样的边界值会在接下来的6分钟时间间隔内移动，即。,12点。

SEC_TO_TIME( ( (TIME_TO_SEC(`fromStamp`) - 1) DIV 360 + 1) * 360))
                                                      ^^^ add one

To combine the two, use a CASE statement to control which form of rounding is used per record:

要合并两者，请使用CASE语句来控制每条记录使用的舍入形式:

SELECT
  fromStamp, 
  CASE WHEN
    DATE_FORMAT(`fromStamp`, '%i') BETWEEN 0 AND 6 
    OR DATE_FORMAT(`fromStamp`, '%i') BETWEEN 31 AND 36
  THEN
    CONCAT(DATE_FORMAT(`fromStamp`, '%Y-%m-%d'), ' ',
         SEC_TO_TIME((TIME_TO_SEC(`fromStamp`) DIV 360) * 360))
  ELSE
    CONCAT(DATE_FORMAT(`fromStamp`, '%Y-%m-%d'), ' ',
         SEC_TO_TIME(((TIME_TO_SEC(`fromStamp`) - 1) DIV 360 + 1) * 360))
  END
FROM `table`

(DEMO)

(演示)

#2

I think the most straightforward way to handle this odd type of rounding , based on the criteria provided, would be:

我认为，根据所提供的标准，处理这种奇怪的舍入的最直接方式是:

 SELECT `fromStamp`
      , DATE_FORMAT(`fromStamp`,'%Y-%m-%d %H') + INTERVAL
        CASE
            WHEN TIME_FORMAT(`fromStamp`,'%i') <=  6 THEN  0  -- round DOWN
            WHEN TIME_FORMAT(`fromStamp`,'%i') <= 12 THEN 12  -- round up
            WHEN TIME_FORMAT(`fromStamp`,'%i') <= 18 THEN 18  -- round up
            WHEN TIME_FORMAT(`fromStamp`,'%i') <= 24 THEN 24  -- round up
         -- WHEN TIME_FORMAT(`fromStamp`,'%i') <= 30 THEN 30  -- round up
            WHEN TIME_FORMAT(`fromStamp`,'%i') <= 36 THEN 30  -- round DOWN
            WHEN TIME_FORMAT(`fromStamp`,'%i') <= 42 THEN 42  -- round up
            WHEN TIME_FORMAT(`fromStamp`,'%i') <= 48 THEN 48  -- round up
            WHEN TIME_FORMAT(`fromStamp`,'%i') <= 54 THEN 54  -- round up
            WHEN TIME_FORMAT(`fromStamp`,'%i') >= 55 THEN 60  -- round up
        END MINUTE AS rnd_fromStamp

That seems the most straightforward way to express what is happening, for future reviewers who look at the statement, and wonder "What the plastic is that statement doing to fromStamp?"

这似乎是表达正在发生的事情的最直接的方式，对于未来的审阅人员来说，如果他们看到这个语句，并想知道“这个语句对fromStamp有什么影响?”

There might be slightly faster (more efficient) algorithms, but I don't think any of those are going to be any more elegant, or as easily readable and understood as this.

可能会有更快(更高效)的算法，但我认为它们都不会更优雅，也不会像这样容易阅读和理解。

#3

This i used for rounds up to minute

这个我用了一分钟。

SELECT MINUTE(SEC_TO_TIME(((TIME_TO_SEC(call_duration)) DIV 60 +1)*60)) call_duration FROM call_log

选择MINUTE(SEC_TO_TIME((TIME_TO_SEC(call_duration)) DIV 60 +1)*60))调用_duration FROM call_log

#1

SEC_TO_TIME( ( (TIME_TO_SEC(`fromStamp`) - 1) DIV 360 + 1) * 360))
                                                      ^^^ add one