Home Page for the TeradataForum
 

Archives of the TeradataForum

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


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


Subj:   Re: How to calculate average endtime irrespective to run date
 
From:   Geoffrey Rommel

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>>  
 
 
 
 
 
 
 
 
  
  Top Home Privacy Feedback  
 
 
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 23 Jun 2019