Geek Answers Handbook

Calculate average time difference between two datetime fields per day

I have a taxi database with two datetime fields 'BookedDateTime' and 'PickupDateTime'. The client should know the average waiting time from the moment of booking a taxi to the moment when the driver actually “picked up” the client.

The database contains a bunch of rows spanning a couple of months.

The goal is to process the request, which shows me the daily average.

So a super simple example:

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) = average value 00: 03: 00.000 (3 minutes)

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

Is this possible, or do I need to do some crunches in the code (i.e. C #)?

Any pointers would be greatly appreciated.

+4
4

, :

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

:

select
    convert(date, BookedDateTime) as day,
    AVG(DATEDIFF(minute, PickupDateTime, BookedDateTime)) as avg_minutes
from bookings
group by convert(BookedDateTime, datetime, 101)
+2

Try the following:

IF OBJECT_ID(N'tempdb..#TEMP') > 0
    BEGIN
       DROP TABLE #TEMP
    END
CREATE TABLE #TEMP(BookedDateTime DateTime,
                PickupDateTime DateTime)
INSERT INTO #TEMP
VALUES
('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-10 23:59:00.000', '2014-06-11 00:01:00.000')

SELECT CAST(BookedDateTime AS DATE) AS YMDDate,
      CONVERT(CHAR(8), DATEADD(second, AVG(DATEDIFF(s, BookedDateTime, PickupDateTime)), 0), 108) [hh:mi:ss],
      CONVERT(CHAR(15), DATEADD(second, AVG(DATEDIFF(s, BookedDateTime, PickupDateTime)), 0), 114) [hh:mi:ss:mmm(24h)]
FROM #TEMP
GROUP BY CAST(BookedDateTime AS DATE)
+1
source

This can be done if you find the difference in each row, summarize them and divide by the number of rows.

In SQLSERVER, this will look below.

SELECT SUM(DATEDIFF(MINUTE,BookedDateTime,PickupDateTime)) * 1.0 
           / (SELECT COUNT(*) * 1.0 FROM MyTable)
FROM MyTable
0
source

More articles:


All Articles