# Archives of the TeradataForum

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

 < Last>>

 Subj: How to calculate average endtime irrespective to run date From: DUELL, BOB

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

 < Last>>

Attachments

Library

Quick Reference

Archives

Sample Index

 2016 2007 2015 2006 2014 2005 2013 2004 2012 2003 2011 2002 2010 2001 2009 2000 2008 1999

2012 Indexes

 Jan Jul Feb Aug Mar Sep Apr Oct May Nov Jun Dec

 Top Home Privacy Feedback

Copyright 2016 - All Rights Reserved
Last Modified: 23 Jun 2019