如何将两个日期的差值设为小时

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。

7 个解决方案

#1

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 | 
+-----------------------------------------------------------------------------+

#2

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.sqmyl.com/doc/refman/5.7 / en/date-andtimefunctions.html #function_time格式

#3

You could try the following:

你可以试试下面的方法:

time_format(timediff(max(l.fecha), min(l.fecha) ),'%H:%m') //Hora y minutos

#4

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;

#5

Its a very old thread, but you can also try TIMESTAMPDIFF and give MINUTE, HOUR, SECONDS as the qualifier.

它是一个非常古老的线程，但是您也可以尝试TIMESTAMPDIFF，并以分钟、小时、秒作为限定符。

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

#6

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;

#7

If you are using timestamp, this could be the solution in MySQL using SQL.

如果您正在使用时间戳，这可能是使用SQL的MySQL解决方案。

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)

#1

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 | 
+-----------------------------------------------------------------------------+

#2