Archives of the TeradataForum

Message Posted: Tue, 23 Oct 2012 @ 13:53:41 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

You're almost there. Since the calculation gives you seconds since midnight, you need to convert those back into hour/minute/second intervals, then add the total interval to midnight (time '00:00:00') to get a point in time. The following statement gives the desired result -- almost. Unfortunately, it refused to give me the fractional portion of the seconds, so I dropped those.

     select avg( case
                   when start_date <> end_date
                   then extract(second from end_time)
                      + extract(minute from end_time) * 60
                      + extract(hour   from end_time) * 3600
                      + 86400
                   else extract(second from end_time)
                      + extract(minute from end_time) * 60
                      + extract(hour   from end_time) * 3600
                   end) mod 86400
            as avg_ssm   /* seconds since midnight */ ,
        time '00:00:00' +
        cast(cast(avg_ssm as integer) / 3600 as interval hour) +
        cast(cast(avg_ssm as integer) / 60 as interval minute) +
        cast(cast(avg_ssm as integer) MOD 60 as interval second) tempus
        from    bd_time_test;

	<Prev	Next>		<<First	<Prev	Next>	Last>>