sqltimesnowflake-cloud-data-platformaverage

how to find AVG of time in snowflake

I have a timestamp column with following sample data:

JOB_ID  OOB_TIMESTAMP
1495576 NULL
1495576 2022-08-25 18:29:00.000 -0700
1495576 2022-08-24 19:56:00.000 -0700
1495576 2022-08-23 17:32:00.000 -0700
1495576 2022-08-22 17:12:00.000 -0700
1495576 2022-08-21 15:35:00.000 -0700
1495576 2022-08-20 15:41:00.000 -0700
1495576 2022-08-19 17:09:00.000 -0700
1495576 2022-08-18 17:25:00.000 -0700
1495576 2022-08-17 17:38:00.000 -0700
1495576 2022-08-16 20:00:00.000 -0700
1495576 2022-08-15 14:02:00.000 -0700
1495576 2022-08-14 12:01:00.000 -0700
1495576 2022-08-13 13:09:00.000 -0700
1495576 2022-08-12 15:19:00.000 -0700
1495576 2022-08-11 14:14:00.000 -0700
1495576 2022-08-10 16:24:00.000 -0700

I need to ignore the date field and calculate the average of ONLY time per job_id

expected answer: something like --> 15:30:00.000 -0700

this allows me to extract just the time from timestamp to_time(to_timestamp_tz(value:dailyOutOfBudgetDatetime::string)) but avg(to_time(to_timestamp_tz(value:dailyOutOfBudgetDatetime::string))) doesn't work throwing an error

Solution 
  •  select JOB_ID , avg_seconds ,
     floor(avg_seconds/3600) as hour,
    floor((avg_seconds%3600)/60) as min, 
    floor(avg_seconds%60) as sec
    from (select JOB_ID ,
    avg(date_part(epoch_second , to_timestamp(OOB_TIMESTAMP::string))- date_part(epoch_second , to_timestamp(SUBSTR(OOB_TIMESTAMP , 1,10 )::string))) as
    avg_seconds
    from  ( select 1495576 as JOB_ID ,  NULL as OOB_TIMESTAMP UNION
    select 1495576 as JOB_ID , '2022-08-25 18:29:00.000 -0700' as OOB_TIMESTAMP UNION
    select 1495576 as JOB_ID , '2022-08-24 19:56:00.000 -0700' as OOB_TIMESTAMP UNION
    select 1495576 as JOB_ID , '2022-08-23 17:32:00.000 -0700' as OOB_TIMESTAMP UNION
    select 1495576 as JOB_ID , '2022-08-22 17:12:00.000 -0700' as OOB_TIMESTAMP UNION
    select 1495576 as JOB_ID , '2022-08-21 15:35:00.000 -0700' as OOB_TIMESTAMP UNION
    select 1495576 as JOB_ID , '2022-08-20 15:41:00.000 -0700' as OOB_TIMESTAMP UNION
    select 1495576 as JOB_ID , '2022-08-19 17:09:00.000 -0700' as OOB_TIMESTAMP UNION
    select 1495576 as JOB_ID , '2022-08-18 17:25:00.000 -0700' as OOB_TIMESTAMP UNION
    select 1495576 as JOB_ID , '2022-08-17 17:38:00.000 -0700' as OOB_TIMESTAMP UNION
    select 1495576 as JOB_ID , '2022-08-16 20:00:00.000 -0700' as OOB_TIMESTAMP UNION
    select 1495576 as JOB_ID , '2022-08-15 14:02:00.000 -0700' as OOB_TIMESTAMP UNION
    select 1495576 as JOB_ID , '2022-08-14 12:01:00.000 -0700' as OOB_TIMESTAMP UNION
    select 1495576 as JOB_ID , '2022-08-13 13:09:00.000 -0700' as OOB_TIMESTAMP UNION
    select 1495576 as JOB_ID , '2022-08-12 15:19:00.000 -0700' as OOB_TIMESTAMP UNION
    select 1495576 as JOB_ID , '2022-08-11 14:14:00.000 -0700' as OOB_TIMESTAMP UNION
    select 1495576 as JOB_ID , '2022-08-10 16:24:00.000 -0700' as OOB_TIMESTAMP  ) 
    group by JOB_ID )

    I have intentionally ignored Time Zone as we need to find avg of time only.