Post reply

Average of time

  • March 30, 2006 at 1:57 pm

    #68201

    Hi,

    Is there a way to get Avg of Time

    I have values like

    01:30:02

    00:23:40

    03:34:45 etc.....

    I know avg function wont take datetime as an argument ...is there  work around ?

    I would appreciate anyones input.

    Thanks

    Viq

  • March 31, 2006 at 5:49 am

    #630055

    this should work,

    select avg(cast(date_field as int)) from wherever

  • March 31, 2006 at 7:44 am

    #630103

    Well since the values have characters like ":" makes it illegal value for int conversion.

     

  • March 31, 2006 at 7:45 am

    #630104

    That will only average the date part.  If you want to average durations (time values), you need to do something different.

    select avg(cast(date_field as float))  -- Get average date & time

    select avg(datediff(s, starttime, endtime) * 1.0)  -- Get average duration in seconds

  • March 31, 2006 at 9:53 am

    #630179

    Well I have done this to solve my problem

    This solution assumes that the totlatime field is character field and has format atleast "0:0:0", which means atleast two colons seperated by numerical characters.

    Convert(Varchar(10), Avg(Convert(int, substring(TotalTime, 1,  charIndex(':',TotalTime,1) - 1)))) + ':' +

    Convert(Varchar(10), Avg(Convert(int, Substring(TotalTime, (charindex(':', TotalTime,1)+1), charindex(':', TotalTime, (charindex(':', TotalTime,1)+1)) -((charindex(':', TotalTime,1)+1)))))) + ':' +

    convert(Varchar(10), Avg(Convert(int, Right(TotalTime, (Len(TotalTime) - charindex(':',TotalTime,charIndex(':',TotalTime,1)+1)))))) as 'Ave of Total Time',

    If you want explanation please let me know I will be happy to provide.

    Thank you

  • March 31, 2006 at 2:19 pm

    #630236

    That's a great solution if one of your goals is job security, but unfortunately it gives you the wrong answer.

    You implied it was a datetime field originally, not character.  If you convert it to datetime there are various ways to get the right answer.

    select

    Convert(Varchar(10), Avg(Convert(int, substring(TotalTime, 1, charIndex(':',TotalTime,1) - 1)))) + ':' +

        Convert(Varchar(10), Avg(Convert(int, Substring(TotalTime, (charindex(':', TotalTime,1)+1), charindex(':', TotalTime, (charindex(':', TotalTime,1)+1)) -((charindex(':', TotalTime,1)+1)))))) + ':' +

        convert(Varchar(10), Avg(Convert(int, Right(TotalTime, (Len(TotalTime) - charindex(':',TotalTime,charIndex(':',TotalTime,1)+1)))))) as 'Ave of Total Time',

        convert(varchar,cast(avg((datepart(hh,Duration)*60 + datepart(n,Duration)) * 60 + datepart(s,Duration))/86400.0 as datetime),8) as AvgDuration1,

        convert(varchar,cast(avg(cast(Duration as float)) as datetime),8) as AvgDuration2

    from (

        select TotalTime, cast(TotalTime as datetime) as Duration

        from (

            select '01:30:00' as TotalTime

            union select '06:48:15'

            union select '15:23:09'

            union select '04:44:55'

        ) x

    ) y

  • March 31, 2006 at 2:30 pm

    #630240

    What took you so long...ur d Man

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply