MySQL 中两个时间字段的分钟数差异
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5070111/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Difference in minutes from two time fields in MySQL
提问by NeilG
I set up my MySQL database with the field 'time'
我使用字段“时间”设置了我的 MySQL 数据库
It is not HH:MM in the traditional sense, it is the time an event occurred, so an event with the value of 5:45 occurred with 5 minutes 45 seconds left in a game. 12:25 occurred with 12 minutes and 25 seconds left, etc.
它不是传统意义上的 HH:MM，它是一个事件发生的时间，所以一个值为 5:45 的事件在比赛还剩 5 分 45 秒时发生。12:25 发生，还有 12 分 25 秒，等等。
I would like to be able to find out the total time elapsed, so if I have an event that occurred at 12:25 and the next event occurred at 5:45 I want to be able to get the difference, which would be equal to 6:40. Then, I would like to express this as a decimal, in this case 6.67.
我希望能够找出经过的总时间，所以如果我有一个发生在 12:25 的事件而下一个事件发生在 5:45 我希望能够得到差异，这将等于6:40。然后，我想将其表示为小数，在本例中为 6.67。
Is this possible?
这可能吗？
Thanks in advance.
提前致谢。
回答by Shawn
Just use
只需使用
TIMEDIFF(fromtime, totime):
SELECT TIMEDIFF("12:25", "5:45");
If you need a decimal, use TIME_TO_SEC()/3600(it assumes you passed it seconds, but the format you're storing the times in is vague and SQL probably will interpret them as HH:MM - you can fix this with CONCAT("00:",MinuteSecondField)maybe?) - then you can use TIME_TO_SEC()/60, which is more correct)
如果您需要小数，请使用TIME_TO_SEC()/3600（它假设您传递了秒数，但是您存储时间的格式是模糊的，SQL 可能会将它们解释为 HH:MM - 您可以用CONCAT("00:",MinuteSecondField)也许解决这个问题？） - 那么您可以使用TIME_TO_SEC()/60，这是更正确的）
回答by A T
For me this worked:
对我来说这有效：
TIMESTAMPDIFF(MINUTE, T0.created, T0.modified)
Reference: https://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html#function_timestampdiff
参考：https: //dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html#function_timestampdiff
回答by artfulrobot
I needed similar. Two useful functions: TIME_TO_SECand SUBTIME.
我需要类似的。两个有用的功能：TIME_TO_SEC和SUBTIME。
e.g. if your time fields were normal HH:MM times, then
例如，如果您的时间字段是正常的 HH:MM 时间，那么
SELECT (TIME_TO_SEC(end_time) - TIME_TO_SEC(start_time))/60 AS `minutes`
In your case, as I understand it, the times are backwards, ie. 12:00<6:00so your end_time and start_time would need swapping.
在你的情况下，据我所知，时代是倒退的，即。12:00<6:00所以你的 end_time 和 start_time 需要交换。
If you wanted the output in HH:MM:SS you could do
如果你想要 HH:MM:SS 的输出，你可以这样做
SELECT SUBTIME(end_time, start_time)
回答by Alex Pakka
I used UNIX_TIMESTAMP(event1)-UNIX_TIMESTAMP(event2), which gives you seconds between events. Divide it by 60.0 and you will get a decimal as per your requirement. This solution assumes, your columns are date-compatible values. It will work really fast if they are TIMESTAMPs.
我使用了 UNIX_TIMESTAMP(event1)-UNIX_TIMESTAMP(event2)，它为您提供了事件之间的秒数。将其除以 60.0，您将根据您的要求得到一个小数。此解决方案假定您的列是日期兼容的值。如果它们是 TIMESTAMP，它会工作得非常快。
UPDATE: This solution only works with timestamp data types, not time datatypes as in original question.
更新：此解决方案仅适用于时间戳数据类型，而不适用于原始问题中的时间数据类型。
