Archives of the TeradataForum

Message Posted: Mon, 22 Oct 2012 @ 19:48:22 GMT

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

Hi,

To illustrate my question, consider this table:

     create table bd_time_test
        ( start_date date
        , end_date date
        , end_time time(6)
        ) primary index(start_date);
     insert into   bd_time_test('2012-10-09','2012-10-10','00:19:56');
     insert into   bd_time_test('2012-10-08','2012-10-08','23:37:18');
     insert into   bd_time_test('2012-10-05','2012-10-05','23:39:47');
     insert into   bd_time_test('2012-10-04','2012-10-04','23:42:47');
     insert into   bd_time_test('2012-10-03','2012-10-03','23:41:54');
     insert into   bd_time_test('2012-10-10','2012-10-11','01:41:49');

I want to calculate the "average" end_time for these jobs without considering the date the job ran. In my case, I am analyzing irregularly scheduled jobs that are submitted at night and may end after midnight (and never run more than 24 hours). The solution I came up with was to extract the "seconds" from the end_time field, add 86400 seconds if the job did not end on the same day, and then mod by 86400 to get the time in seconds. For the above data, the result is 435.1667 seconds (which should be approximately 00:07:15.17).

Now I want to convert that result back to a time value. Using some "casting" examples found on the internet, I came up with this:

     select cast(cast(cast(
        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 format '99:99:99.99')
        as char(11))
        as time(6)) as avg_time
     from    bd_time_test

However, the result comes back as "00:04:35.170000", so I am clearly not doing this correctly.

Two questions:

1. How do I properly create a TIME(6) value given the number of seconds?

2. Are there other possible solutions to this problem?

Thanks!

Bob

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