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