Archives of the TeradataForum
Message Posted: Mon, 22 Oct 2012 @ 19:48:22 GMT
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.
1. How do I properly create a TIME(6) value given the number of seconds?
2. Are there other possible solutions to this problem?
|Copyright 2016 - All Rights Reserved|
|Last Modified: 28 Jun 2020|