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