CODEWITHSUNDEEP
sqltimesnowflake-cloud-data-platformaverage
deg
deg

Reputation: 37

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

Upvotes: 1

Views: 1536

Answers (3)

G. M.
G. M.

Reputation: 1

This uses modulus with the number of seconds in a day to remove the date component of epoch second representations of timestamps. Then calculates the average number of seconds, converts that back to a timestamp, then extracts just the time.

select to_time(
    to_timestamp_ntz(
        avg(
            mod(
                date_part(epoch_seconds, OOB_TIMESTAMP)
                ,86400
            )
        )
    )
) avg_OOB_TIME

Upvotes: 0

ADITYA PAWAR
ADITYA PAWAR

Reputation: 695

You have to calculate average at hour and minute level seperately and then combine and cast to time.

Check if below query helps

select (avg(date_part('HH',to_timestamp_tz(OOB_TIMESTAMP::string)))::int::varchar
||':'|| avg(date_part('MI',to_timestamp_tz(OOB_TIMESTAMP::string)))::int::varchar||':00')::time avgTime
from my_table;

Upvotes: 1

Narsireddy
Narsireddy

Reputation: 403

 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.

Upvotes: 1

Related Questions