Help calculating an average Start Time?

Database

Help calculating an average Start Time?

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 :)

2 REPLIES
Junior Contributor

Re: Help calculating an average Start Time?

I'm afraid you can't do an average on a TIME :-(

But it's not that complicated :-)

   TIME '00:00:00'
+ 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)...

Dieter

Enthusiast

Re: Help calculating an average Start Time?

Hi,

Try this, it worked for me.

SELECT ColumnName, AVG(CAST(TIME_COLUMN AS  INTERVAL HOUR(4) TO SECOND(0)))
FROM
(
SEL CAST(CAST(CAST('2013-09-13 12:01:23' AS TIMESTAMP(0)) AS FORMAT 'HH:MI:SS') AS CHAR(8)) TIME_COLUMN
)drv
GROUP BY ColumnName;
Khurram