Find average time of day

Analytics

Re: Find average time of day

Data type is TIMESTAMP(0) without time zone. Example below-

open_dt

2/1/2018 13:53:36
2/11/2018 13:39:44

 

Actual Result- 01:46:40; Expected Result- 13:46:40

 

I checked my TD system time and it displays current time in 24 hr format. (Code used- sel current_timestamp(0))

Highlighted
Junior Contributor

Re: Find average time of day

This seems to work regardless of time zones, brute force :-)

Avg(Cast(To_Char(opened_at, 'hh24:mi:ss') AS INTERVAL HOUR TO SECOND(0))) -- this results in an Interval
+ TIME '00:00:00'  -- if you need a Time (both look exactly the same)