Iterative summations in SQL

Question

I have values in a Zabbix DB that are loaded onto it every minute. I need a query that computes hourly totals for different days e.g under 20 Mar 2013, I'd have a row for 0:00 containing a sum of values where time >= 0:00 and < 1:00 and so on. Then I'd have another row for between 1 and 2 AM etc. I am using the query below, but I have to keep changing the times. What I am looking for is a query that will generate for me 24 rows for each hour period in a day. Please help.

SELECT 
    SUM(CASE WHEN itemid = 23661
        THEN value ELSE 0 END) Hits 
    FROM history_uint WHERE 
        clock >= EXTRACT(EPOCH FROM TIMESTAMP '2013-03-24 00:00:00')
         AND clock < EXTRACT(EPOCH FROM TIMESTAMP '2013-03-24 01:00:00')

Answer 1

Have you tried grouping the results by the hour?

SELECT DATEPART(HOUR, timestamp) [HOUR]
, SUM(CASE WHEN itemid = 23661 THEN value ELSE 0 END) hits 
FROM history_uint
WHERE clock >= EXTRACT(EPOCH FROM timestamp '2013-03-24')
  AND clock < EXTRACT(EPOCH FROM timestamp '2013-03-25')
GROUP BY DATEPART(HOUR, timestamp);