sql: Average Hour of Day in Teradata

samedi 25 avril 2015

I have a table with hundreds of timestamps.

I extract the hour, then I can simply average that, however, this is where I get issues.

select purchase,
CAST(AVG(EXTRACT(HOUR FROM opened_at)) AS INT) as average_open
            from db.purchasetable 
group by 1

When I average hour 22 and hour 2 I get 12(obviously), however, what I want is 0 cause midnight is the average point in time between the two hours.

How do I do this in Teradata?

sql