CODEWITHSUNDEEP
sqloracle-database
user1751356
user1751356

Reputation: 615

Avg of time in Oracle SQL

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

Upvotes: 1

Views: 16524

Answers (1)

sstan
sstan

Reputation: 36473

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.

Upvotes: 4

Related Questions