DB2 - get the average time between set of dates

Question

I have a list of events and each one has a startDate and endDate. I need to know the average time taken for each event.

I need something like this:

select sum ( (timestamp(startDate) -  timestamp(endDate)) for each event )
       / (count of events)

Answer 1

It only makes mathematical sense to take the AVG() of a numeric value, not datetime values or durations. Since you want your answer to be in minutes precision, you want to get your difference in minutes, then convert back to days, hours, minutes. (There are 24*60=1440 minutes in a standard day.)

with q as
(select avg(
             timestampdiff(4, char(endDate - startDate) )
           )  as avgmns
  from yourChosenData
)
select int(avgmns / 1440) as avg_days,
       int( mod(avgmns,1440) / 60) as avg_mins,
       mod(avgmns, 60) as avg_secs
  from q

As mentioned below, timestampdiff() is an estimate. To avoid this issue, one could use a more accurate calculation.

with q as
(select avg(
             ( days(endDate) - days(startDate) ) * 1440
           + ( midnight_seconds(endDate) - midnight_seconds(startDate) ) / 60
           )  as avgmns
  from yourChosenData
)
select int(avgmns / 1440) as avg_days,
       int( mod(avgmns,1440) / 60) as avg_mins,
       mod(avgmns, 60) as avg_secs
  from q

In order to address the DST issue, if needed, one might choose either of:

• include a UTC offset column corresponding to each timestamp field. This would also be useful if timstamps were being recorded in more than one timezone. The diference in offsets could then be fed into the calculation along with the timestamps.
• provide a deterministic UDF which could return a UTC or DST adjustment offset for a given timestamp. If multiple timezones are involved, then the zone should also be a parameter to the function. Depending on the geographic areas involved, the logic may also need to consider areas which observe alternative DST rules.

Answer 2

You have to be careful of the denominator to prevent a 0 division: SQL0802 - Data Conversion or Data Mapping Error

Depending on the precision of the results, you will need to convert the date. Let's suppose you need seconds (2)

select 
  sum ( timestampdiff(2, endDate - startDate))
  /
  sum (count of events)
from yourTable

http://pic.dhe.ibm.com/infocenter/db2luw/v10r5/topic/com.ibm.db2.luw.sql.ref.doc/doc/r0000861.html