sql - Average Hour of Day in Teradata -

i have table hundreds of timestamps.

i extract hour, can average that, however, issues.

select purchase, cast(avg(extract(hour opened_at)) int) average_open             db.purchasetable  group 1 

when average hour 22 , hour 2 12(obviously), however, want 0 cause midnight average point in time between 2 hours.

how do in teradata?

you can't average on time, it's allowed interval :-)

subtracting 2 times return interval:

avg(cast(opened_at time(0))            -- extract time portion      - time '12:00:00' hour second(0)) -- subtract 12 hours center @ midnight (returns interval) + time '00:00:00'                         -- change time 

if still want hour result can extract it.