avg time oracle sql

Question

I'm trying to find the avg start time for a date range but the avg function doesn't seem to work on to_char(cast(Start_Dt as date),'HH24:MI') (Start_Dt sample: 12/12/18 02:30:13). Any ideas on how I can get my desired result?

sample:

SELECT
job_nm
avg(to_char(cast(Start_Dt as date),'HH24:MI'))
FROM batch_table
Where Start_Dt >= trunc(sysdate-10)
group by job_nm

Error: ORA-01722: invalid number 01722. 00000 - "invalid number" *Cause: The specified number was invalid. *Action: Specify a valid number.

Ideal results would be the query only looks at jobs ran in the last 10 days and the average start time for the period.
desired results:

 JOB_NM | AVG Time
    open   |  2:30
    close  |  3:30

Answer 1

Oracle is pretty flexible when it comes to treating dates as floating point numbers, so there's probably no need for the massively convoluted data type conversions in the accepted answer:

SELECT
  job_nm,
  to_char(trunc(sysdate) + avg(start_dt - trunc(start_dt)),'HH24:MI')) avg_time
FROM batch_table
  Where Start_Dt >= trunc(sysdate-10)
  group by job_nm

It works because start_dt - trunc(start_dt) is a float between 0 and 1 representing the time of day (0.5 = 12 noon, 0.75 = 6pm), these can all be averaged giving another float that is the average time (6am and 6pm are average, noon. 0.25 and 0.75 are average 0.5).

Adding this back onto any date (like the trunc(sysdate)) gives a date and time, and to_char just prints the time portion of it, toss the date away. Oracle should be smart enough to not insist that you group by trunc(sysdate)

https://dbfiddle.uk/?rdbms=oracle_11.2&fiddle=2563874ab5566f9ff6030601497b718b

Answer 2

To compute an average time, you would use :

SELECT
    job_nm,
    TO_CHAR(
        TO_DATE(
            AVG(
                TO_NUMBER(
                    TO_CHAR(
                        TO_DATE(
                            TO_CHAR(Start_Dt,'HH24:MI:SS'),
                        'HH24:MI:SS'),
                    'sssss')
                )
            ),
        'sssss'),
    'hh24:mi:ss')
FROM batch_table
WHERE Start_Dt >= trunc(sysdate-10)
GROUP BY job_nm;

Here is another option, maybe less convoluted :

 SELECT 
    job_nm, 
    FLOOR(24 * AVG(Start_Dt- TRUNC(Start_Dt)))
     || ':'
     || FLOOR(MOD(24 * AVG(Start_Dt - TRUNC(Start_Dt)),1) * 60)
     || ':'
     || FLOOR(MOD(MOD(24 * AVG(Start_Dt- TRUNC(Start_Dt)),1) * 60,1) * 60)
FROM batch_table
WHERE Start_Dt >= trunc(sysdate-10)
GROUP BY job_nm;

And, just in case, if you are looking to compute an average date, you would convert dates to Julian date format, compute average, then translate back to date, like :

SELECT
    job_nm,
    TO_DATE(
        ROUND(
            AVG(
                TO_NUMBER(TO_CHAR(Start_Dt, 'J'))
            )
        ),
   'J')
FROM batch_table
WHERE Start_Dt >= trunc(sysdate-10)
GROUP BY job_nm;