Sunday, October 17, 2010

Calculate average on Time field

Strange, but yes we can calculate average on time fields. Consider a scenario in which a table has following structure

City            Mode        Start-time             end-time
char(10)    char(10)     char(5)(HH:MI)    char(5)(HH:MI)
------------------------------------------------------------------
Paris          Taxi                04:55                09:33
Rome        Taxi                02:14                 08:44
Madrid      Shuttle            01:21                 03:19

The requirement is to calculate average end-time from the table.

Since end-time is char (5), this has to be converted into Time format HH:MI:SS , before calculating the average

When A cast is applied as follows
cast(end-time||':00' as time) and then try to get an average , We will get following error

Error - "invalid operation on an ANSI date-time or interval"

To fix, this issue the concept of interval is applied.

Select CITY, avg (cast(trim(end-time)||':00' as interval hour(4) to second (0) ))
from TRAVEL group by CITY ;

Hence, using interval, we can override the ANSI time error and calculate average on time field

No comments:

Post a Comment