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!
Dig deeper on Oracle and SQL
Related Q&A from Rudy Limeback, SQL Consultant, r937.com
Read SQL expert Rudy Limeback's advice for counting combinations in a table with SQL's GROUP BY clausecontinue reading
Read an example of an SQL case expression from our SQL expert Rudy Limeback.continue reading
Read about the Mimer Validator, a tool used to verify your SQL code, in this tip from SQL expert Rudy Limeback.continue reading
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.