计算mysql中两个日期时间之间的差异

I want the query to calculate the difference between two date time fields

我希望查询计算两个日期时间字段之间的差异

to calculate total time taken

计算总时间
two date time fields are in same table as start_time and stop_time

两个日期时间字段与start_time和stop_time在同一个表中
And I want to calculate total start duration and stop duration

我想计算总开始持续时间和停止持续时间

3 个解决方案

#1

You can use MySQL's UNIX_TIMESTAMP() function to convert your datetime expressions to seconds since the UNIX epoch, then taking the sum of all differences will yield the total duration in seconds:

您可以使用MySQL的UNIX_TIMESTAMP()函数将您的日期时间表达式转换为自UNIX纪元以来的秒数,然后获取所有差异的总和将产生以秒为单位的总持续时间:

SELECT SUM(UNIX_TIMESTAMP(stop_time) - UNIX_TIMESTAMP(start_time)) FROM my_table

See it on sqlfiddle.

在sqlfiddle上看到它。

Note that UNIX_TIMESTAMP() is limited to the range '1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC.

请注意,UNIX_TIMESTAMP()仅限于“1970-01-01 00:00:01”UTC到“2038-01-19 03:14:07”UTC的范围。

#2

HINT:

SELECT TIMESTAMPDIFF(SECOND,'2012-12-30 12:01:01','2012-12-31 10:02:00'); 
-- result: 79259  the difference in seconds with the time.

See this link for more details on DateTime functions.

有关DateTime函数的更多详细信息,请参阅此链接。

#3

Try this

SELECT TIMEDIFF(STOP_TIME - START_TIME) AS INTERVAL
FROM ......

#1