仅在MYSQL DATEDIFF中显示小时数

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小时的输出？

4 个解决方案

#1

What about using TIMESTAMPDIFF?

那么使用TIMESTAMPDIFF呢？

SELECT TIMESTAMPDIFF(HOUR, start_time, end_time) 
           as `difference` FROM timeattendance WHERE timeattendance_id = '1484'

#2

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`;

#3

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 - 时间 - functions.html＃function_hour

#4

As stated in MySQL reference, DATEDIFF only 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 ...

#1