1

I'm trying to find out avg time of when a particular task is getting completed.I can ignore date because the task will complete everyday.

So, I'm trying to find out when the job is getting complete on avg like 4:45:50

My Data:

28-AUG-2015 01.24.58.000000000 AM
27-AUG-2015 01.31.33.000000000 AM
26-AUG-2015 01.28.09.000000000 AM
25-AUG-2015 01.30.43.000000000 AM
24-AUG-2015 01.02.46.000000000 AM
23-AUG-2015 01.18.56.000000000 AM
22-AUG-2015 01.25.24.000000000 AM
21-AUG-2015 01.30.07.000000000 AM
20-AUG-2015 01.25.58.000000000 AM
19-AUG-2015 01.27.08.000000000 AM
18-AUG-2015 01.28.12.000000000 AM
17-AUG-2015 01.27.51.000000000 AM
16-AUG-2015 01.34.32.000000000 AM
15-AUG-2015 01.46.10.000000000 AM
14-AUG-2015 01.56.47.000000000 AM
13-AUG-2015 01.38.55.000000000 AM
Justin Cave
  • 227,342
  • 24
  • 367
  • 384
user1751356
  • 565
  • 4
  • 14
  • 33
  • Given that data, how would you get an average of 4:45:50? Is your data a `timestamp`? A `timestamp with time zone`? A `timestamp with local time zone`? A `varchar2`? A `date`? Or something else? – Justin Cave Sep 04 '15 at 14:04
  • The average is not 4:45:50, i'm just giving an example of the answer that i'm looking for. My data is a timestamp with local time zone. US CST. – user1751356 Sep 04 '15 at 14:09
  • Thank you for looking into this – user1751356 Sep 04 '15 at 14:09
  • 2
    If you're going to give us sample data, tell us what result you'd want given that sample data. Otherwise, we're just guessing. Do you just want to calculate the average elapsed time since midnight? We can do that. But since the column is `timestamp with local time zone`, that would change depending on the client's time zone (1:00am Central = 2:00am Eastern). That seems unlikely to be what you want. Is there a `start_time` somewhere that goes with this `end_time` value? – Justin Cave Sep 04 '15 at 14:15
  • I think you can query the data to get only the time, change the time to seconds, find the average of the seconds and then change back the average result to time. I think this post may have some explanations. A long and confusing query will be something like `select to_char(to_date(avg(to_number(to_char(to_date(to_char(YourColumn,'hh24:mi:ss'),'hh24:mi:ss'),'sssss'))),'sssss'),'hh24:mi:ss') aveTime from YourTable` http://stackoverflow.com/questions/10307353/averaging-dates-in-oracle-sql – NaNey Sep 04 '15 at 14:22

1 Answers1

4

There has to be a better way, but this should work:

select to_char(trunc(sysdate) + 
               avg(cast(your_timestamp as date) - cast(trunc(your_timestamp) as date))
               , 'hh:mi:ss PM')
  from your_table

Steps:

  1. cast(your_timestamp as date) - cast(trunc(your_timestamp) as date): Calculate the date's time offset since midnight. (I cast to date so that the arithmetic returns a number instead of an interval, which allows step #2 to work).
  2. avg(...): get the average time offset since midnight (Can't do avg over a time interval. That's why I'm using dates)
  3. trunc(sysdate) + ...: Construct a valid date using the average time offset from step #2.
  4. to_char(..., 'hh:mi:ss PM'): Extract the time portion for display.
sstan
  • 35,425
  • 6
  • 48
  • 66
  • You could [extract the total number of seconds from an interval](http://stackoverflow.com/a/29821049/266304), but same idea... But casting to date will lose the time zone information, which might be an issue for the data for the daylight saving switchover days? – Alex Poole Sep 04 '15 at 15:06