Hi Everyone -
Looking for some advice!
I have a column Start_dt which is in 'TimeStamp' format - ie has the date and the time.
I would like to calculate the 'average start time', i.e 9:00, 9:10 the average would be 9:05. Trying to work out if i can extract just the time from start_dt & do an average, or whether I need to extract the hour, minutes, seconds turn it all into seconds then back into a time? (i am hoping the previous!)
Any help would be fantastic!! thanks :)
I'm afraid you can't do an average on a TIME :-(
But it's not that complicated :-)
+ CAST(AVG(EXTRACT(HOUR FROM starttime) * 3600 +
EXTRACT(MINUTE FROM starttime) * 60 +
EXTRACT(SECOND FROM starttime)) AS INT) * INTERVAL '00:00:01' HOUR TO SECOND
Btw, you can do an AVG(DATEcol)...
Try this, it worked for me.
SELECT ColumnName, AVG(CAST(TIME_COLUMN AS INTERVAL HOUR(4) TO SECOND(0)))
SEL CAST(CAST(CAST('2013-09-13 12:01:23' AS TIMESTAMP(0)) AS FORMAT 'HH:MI:SS') AS CHAR(8)) TIME_COLUMN
GROUP BY ColumnName;