计算每天两个日期时间字段之间的平均时差 [英] Calculate average time difference between two datetime fields per day

问题描述

我有一个出租车数据库，其中有两个日期时间字段 BookedDateTime和 PickupDateTime。客户需要知道从预定出租车到驾驶员实际接客的平均等待时间。

I have a taxi database with two datetime fields 'BookedDateTime' and 'PickupDateTime'. The customer needs to know the average waiting time from the time the taxi was booked to the time the driver actually 'picked up' the customer.

数据库中有很多行，覆盖了几个月的数据。

There are a heap of rows in the database covering a couple of month's data.

目标是

所以一个简单的例子是：

So a super simple example would be:

BookedDateTime           | PickupDateTime
2014-06-09 12:48:00.000    2014-06-09 12:45:00.000
2014-06-09 12:52:00.000    2014-06-09 12:58:00.000    
2014-06-10 20:23:00.000    2014-06-10 20:28:00.000
2014-06-10 22:13:00.000    2014-06-10 22:13:00.000

2014-06-09（（-3 + 6）/ 2）=平均值是00：03：00.000（ 3分钟）

2014-06-09 ((-3 + 6) / 2) = average is 00:03:00.000 (3 mins)

2014-06-10（（5 + 0）/ 2）=平均值是00：02：30.000（2.5分钟）

2014-06-10 ((5 + 0) / 2) = average is 00:02:30.000 (2.5 mins)

这可能还是我需要对代码进行一些数字运算（例如C＃）？

Is this possible or do I need to do some number crunching in code (i.e. C#)?

任何指针都将不胜感激。

Any pointers would be greatly appreciated.

推荐答案

我认为这可以做到：

select Convert(date, BookedDateTime) as Date, AVG(datediff(minute, BookedDateTime, PickupDateTime)) as AverageTime
    from tablename 
    group by Convert(date, BookedDateTime)  
    order by Convert(date, BookedDateTime) 

这篇关于计算每天两个日期时间字段之间的平均时差的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！