MySQL 如何获得四舍五入到小时的两个日期之间的差异
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3621440/
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
How to get the difference between two dates rounded to hours
提问by Brian Roisentul
I'll illustrate what I would like to get in the following example:
我将在以下示例中说明我想得到的内容：
'2010-09-01 03:00:00' - '2010-09-01 00:10:00'
Using TIMEDIFF(), we get 2 as a result. This means, it's not considering the 50 minutes left.
使用TIMEDIFF()，我们得到 2 结果。这意味着，它没有考虑剩下的 50 分钟。
In this case, what I'd like to get is: 50 (minutes) / 60 = 0.83 period. Therefore, the result should be 2.83 and not 2.
在这种情况下，我想得到的是：50（分钟）/ 60 = 0.83 周期。因此，结果应该是 2.83 而不是 2。
回答by Matthew
select time_to_sec(timediff('2010-09-01 03:00:00', '2010-09-01 00:10:00' )) / 3600;
+-----------------------------------------------------------------------------+
| time_to_sec(timediff('2010-09-01 03:00:00', '2010-09-01 00:10:00' )) / 3600 |
+-----------------------------------------------------------------------------+
| 2.8333 |
+-----------------------------------------------------------------------------+
回答by riahc3
I think a more complete answer is
我认为更完整的答案是
select time_format(timediff(onedateinstring,anotherdateinstring),'%H:%i:%s')
This allows you to see the hours, minutes, seconds, etc. that you wish to see in the format you want...
这允许您以您想要的格式查看您希望看到的小时、分钟、秒等...
More information on time_format: http://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html#function_time-format
有关 time_format 的更多信息：http: //dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html#function_time-format
回答by Saurabh Shakyawar
Use TIMESTAMPDIFF for exact number of hours :
使用 TIMESTAMPDIFF 获取确切的小时数：
SELECT
b.`Start_Date`,
b.`End_Date`,
TIMESTAMPDIFF(HOUR, b.`Start_Date`, b.`End_Date`) AS `HOURS`
FROM my_table b;
回答by fgimenez
You could try the following:
您可以尝试以下操作：
time_format(timediff(max(l.fecha), min(l.fecha) ),'%H:%m') //Hora y minutos
回答by BSingh
Its a very old thread, but you can also try TIMESTAMPDIFF and give MINUTE, HOUR, SECONDS as the qualifier.
它是一个非常古老的线程，但您也可以尝试 TIMESTAMPDIFF 并将 MINUTE、HOUR、SECONDS 作为限定符。
http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_timestampdiff
http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_timestampdiff
回答by karthik
MySQL get the number of hours between timestamps:
MySQL 获取时间戳之间的小时数：
select timestampdiff(second,'2010-09-01 00:10:00', '2010-09-01 03:00:00')/3600;
回答by spacedev
If you are using timestamp, this could be the solution in MySQL using SQL.
如果您使用时间戳，这可能是 MySQL 中使用 SQL 的解决方案。
SELECT TIMESTAMPDIFF(HOUR, '2010-11-29 13:13:55', '2010-11-29 13:16:55') as newtable;
| newtable |
7
1 row in set (0.01 sec)
回答by Paul Chris Jones
Using UNIX_TIMESTAMP:
使用 UNIX_TIMESTAMP：
SELECT (UNIX_TIMESTAMP(End)-UNIX_TIMESTAMP(Start))/3600)
AS Difference_in_hours
FROM Table
