仅在 MYSQL DATEDIFF 中显示小时数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11579946/
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
Only show hours in MYSQL DATEDIFF
提问by Mossawi
I've been busy with this for hours, but I cant get it to work.
我已经忙了好几个小时了，但我无法让它工作。
SQL
SELECT DATEDIFF(end_time, start_time) as `difference` FROM timeattendance WHERE timeattendance_id = '1484'
start_time = 2012-01-01 12:00:00
end_time = 2012-01-02 13:00:00
The difference is 25 (hours). But the output I get is 1 (day).
差异是 25（小时）。但我得到的输出是 1（天）。
How can I get the 25 hours as output?
如何获得 25 小时作为输出？
回答by FlyingMolga
What about using TIMESTAMPDIFF?
使用TIMESTAMPDIFF怎么样？
SELECT TIMESTAMPDIFF(HOUR, start_time, end_time)
as `difference` FROM timeattendance WHERE timeattendance_id = '1484'
回答by Miroshko
You better use TIMEDIFF instead of DATEDIFF since DATEDIFF casts all times to dates before comparing. After performing TIMEDIFF you can obtain hours with HOUR function.
您最好使用 TIMEDIFF 而不是 DATEDIFF，因为 DATEDIFF 在比较之前将所有时间转换为日期。执行 TIMEDIFF 后，您可以使用 HOUR 函数获取小时数。
SELECT HOUR(TIMEDIFF(end_time, start_time)) as `difference`;
回答by jth_92
You can use the TIMEDIFF and HOUR function to just extract the hour.
您可以使用 TIMEDIFF 和 HOUR 函数来提取小时。
SELECT HOUR(TIMEDIFF(end_time, start_time)) as `difference` FROM timeattendance WHERE timeattendance_id = '1484'
http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_timediff
http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_hour
http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_timediff
http://dev.mysql.com/doc/refman/5.5/en/date-and -time-functions.html#function_hour
回答by madfriend
As stated in MySQL reference, DATEDIFFonly takes days in account.
正如 MySQL参考中所述，DATEDIFF只需要几天的时间。
If both dates are after 1970, you can substract timestamps:
如果两个日期都在 1970 年之后，您可以减去时间戳：
SELECT ( UNIX_TIMESTAMP("2012-01-02 13:00:00") -
UNIX_TIMESTAMP("2012-01-01 12:00:00") ) / 3600 ...
or:
或者：
SELECT TIMEDIFF ( "2012-01-02 13:00:00", "2012-01-01 12:00:00") / 3600 ...
