How to calculate the AVG time stamp for last one week

Question

I am trying to calculate the AVG timestamp for last 7 days in Snowflake database. Data type is VARCHAR and below is the sample data.

LOAD_TIME VARCHAR(10) -

Sample Data:

LOAD_TIME (HHMM) 1017 0927 0713 0645 1753 2104 1253

Answer 1

If you convert these values to epoch_seconds, it's possible to calculate the average:

select to_varchar(to_timestamp(avg(date_part(epoch_second,to_timestamp(load_time,'HH24MI')))), 'HH24MI') as average
from values 
('1017'),('0927'),('0713'),('0645'),('1753'),('2104'),('1253') tmp (load_time);

+---------+
| AVERAGE |
+---------+
|    1213 |
+---------+