Q

Aggregating time intervals

How can I aggregate intervals? Given two timestamps (one for the beginning, the other for the end of an event) I would like to know the average duration of the event.

How can I aggregate intervals? What I'm trying to do is, given two timestamps (one for the beginning, the other for the end of an event) I would like to know the average duration of the event. What's the best way to do this?

Presumably you want the average duration for a number of events, not just one. For a single event, we obtain a duration by "subtracting" the beginning timestamp from the end timestamp. This requires date arithmetic, which can vary wildly from one database system to the next.

Before we get started, let's create some test data.

create table events
( id  integer  not null primary key
, datetime_start  datetime not null
, datetime_end    datetime not null
);
insert into events values
 ( 1, '2006-09-09 14:00', '2006-09-09 16:00' )
,( 2, '2006-09-10 09:00', '2006-09-10 17:00' )
,( 3, '2006-09-11 13:30', '2006-09-11 14:45' )
,( 4, '2006-09-12 09:00', '2006-09-15 17:00' );

As with any good set of test data, we should be able to work out the correct answer ourselves, ahead of time, and then try to come up with the SQL that will reproduce it. So, using good old paper and pencil, or a spreadsheet if desired, we construct the following:

1. 0 days 2 hours 0 minutes =  120 minutes
2. 0 days 8 hours 0 minutes =  480 minutes
3. 0 days 1 hour 15 minutes =   75 minutes
4. 3 days 8 hours 0 minutes = 4800 minutes
                              ----
                      total   5475 minutes
                     average  1368.75 minutes
                         =  22 hours 48.75 minutes 

Now to produce the same results with SQL. First, we need to compute the difference between the start and end. Many database systems actually allow the use of ordinary arithmetic with datetimes (for example, SELECT somedatecol + 1 usually gives the next day), so let's try subtracting:

select id
     , datetime_start
     , datetime_end
     , datetime_end - datetime_start as diff
  from events

Here's what we get for this query in MySQL:

id datetime_start    datetime_end         diff
1  2006-09-09 14:00  2006-09-09 16:00    20000
2  2006-09-10 09:00  2006-09-10 17:00    80000
3  2006-09-11 13:30  2006-09-11 14:45    11500
4  2006-09-12 09:00  2006-09-15 17:00  3080000

And here's what we get in SQL Server:

id datetime_start    datetime_end      diff
1  2006-09-09 14:00  2006-09-09 16:00  1900-01-01 02:00
2  2006-09-10 09:00  2006-09-10 17:00  1900-01-01 08:00
3  2006-09-11 13:30  2006-09-11 14:45  1900-01-01 01:15
4  2006-09-12 09:00  2006-09-15 17:00  1900-01-04 08:00

Weird, eh? In MySQL, we get a "formatted" integer, and in SQL Server, we get a new (albeit ancient) datetime value. But can we average these differences?

select avg(
       datetime_end - datetime_start 
            ) as avg_diff
  from events

In MySQL we get 797875, which is clearly wrong. In SQL Server, we get "Operand data type datetime is invalid for avg operator."

What does this imply? Simply that when using an aggregate function like AVG(), we must ensure that we are actually aggregating something meaningful.

One solution is to convert each interval to a numerical "common denominator" as we did with the spreadsheet data. This may be easier in some database systems than others. As always, the trick is to know your functions and pick the appropriate ones. Here, for example, is the MySQL solution:

select sec_to_time(
         avg(
           unix_timestamp(datetime_end)
         - unix_timestamp(datetime_start)
            ) ) as avg_diff
  from events  

     
avg_diff
22:48:45 

We'll leave the solution for SQL Server, and other database systems, as an EFTS (exercise for the student). Good luck!

This was first published in July 2006
This Content Component encountered an error

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close