如何在表中查找最小和最大时间差异?

I have the following table structure and data

我有以下表结构和数据

DriverName CarType      StartTime                  EndTime
---------- -------      ---------                  --------
DriverA    Honda        2014-09-29 14:32:03        2014-09-29 14:48:09
DriverA    Honda        2014-09-29 12:32:03        2014-09-29 14:32:09
DriverA    Honda        2014-09-29 10:32:03        2014-09-29 14:32:09

DriverA    Toyota        2014-09-29 10:32:03       2014-09-29 10:34:09
DriverA    Toyota        2014-09-29 9:32:03        2014-09-29 9:39:09
DriverA    Toyota        2014-09-29 8:32:03        2014-09-29 8:52:09

How can i get the worst best and average times in either seconds/minutes/or hours

我怎样才能在几秒钟/几分钟/几小时内得到最坏的结果

DriverName  CarType     Worst                        Best      
---------- --------     ------                       -----     
DriverA     Honda       04:00:06 (4 hrs 6 seconds)    00:04:06 (4 minutes 3 seconds)
DriverA     Toyota      00:20:06 (4 hrs 6 seconds)    00:2:06 (20 minutes 3 seconds)

2 个解决方案

#1

You probably want to use timestampdiff()

您可能需要使用timestampdiff()

select DriverName, CarType,
       min(timestampdiff(second, StartTime, EndTime)) as Best,
       max(timestampdiff(second, StartTime, EndTime)) as Worst
from followingtable ft
group by DriverName, CarType;

If you want to show the value as a time format:

如果你想以时间格式显示值:

select DriverName, CarType,
       date_format(cast(0 as datetime) + interval min(timestampdiff(second, StartTime, EndTime,)) second,
                   '%H:%m:%d) as Best,
       date_format(cast(0 as datetime) + interval max(timestampdiff(second, StartTime, EndTime)) second,
                   '%H:%m:%d') as Worst
from followingtable ft
group by DriverName, CarType;

#2

To get the best and worst you will want to get the date diff between the start time and end time and then get either the min or max of that respectively, then for the average you can basically do the same but using the average function. And finally you will want to group on the CarType and (I think) Driver here is an example:

为了得到最好和最坏的结果你需要得到开始时间和结束时间之间的差值然后分别得到最小值或者最大值，然后对于平均值你基本上可以做同样的事情但是使用平均函数。最后，你会想把汽车类型和(我认为)驱动放在一起这是一个例子:

Edit:

编辑:

It was pointed out that DATEDIFF does not function the way I expected, it will explicitly return the diff in days, I have updated the query to use TIMEDIFF instead, and I've also added the use of the TIME_TO_SEC() function to provide the output directly in seconds.

有人指出，DATEDIFF的功能与我预期的不一样，它将在几天内显式地返回diff，我更新了查询以使用TIMEDIFF代替，我还添加了使用TIME_TO_SEC()函数以秒为单位直接提供输出。

SELECT DriverName, 
       CarType, 
       MAX(TIME_TO_SEC(TIMEDIFF(EndTime,StartTime))) AS 'Worst', 
       MIN(TIME_TO_SEC(TIMEDIFF(EndTime,StartTime))) AS 'Best', 
       AVG(TIME_TO_SEC(TIMEDIFF(EndTime,StartTime))) AS 'Average'
GROUP BY DriverName, CarType

#1