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')

In the past with MSSql I've cross joined to a table containing numbers to act as an iteration index and then added time based on that number. Something like this `select adddate(hour, @date, NumbersTable.number) from NumbersTable` — Chris Moutray, Mar 25 '13 at 11:15

score 1 · Answer 1 · answered Mar 25 '13 at 12:37

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);

answered Mar 25 '13 at 12:37

Gerardo Lima

6,467
3
31
47

ERROR: syntax error at or near "[" LINE 1: SELECT DATEPART(HOUR, timestamp) [HOUR], ^ ********** Error ********** ERROR: syntax error at or near "[" SQL state: 42601 Character: 34 – 170730350 Apr 10 '13 at 13:38
This error is because of the function to extract the Hour part of the datetime column. Unfortunately, I don't know the appropriate function in postgre (this is from SQLServer), but if you know the right function, this solution should work. Please let me know if you get stuck. – Gerardo Lima Apr 10 '13 at 17:01

score 0 · Answer 2 · answered Mar 25 '13 at 12:10

0

Something like:

select '2013-03-20'::date + delta * '1 hour'::interval
from generate_series(0,23) g(delta)

answered Mar 25 '13 at 12:10

araqnid

127,052
24
157
134

This only generates the series of hours... how do you get it to give soemthing like a timestamp in one column and a count of values for that hour in a second column? – 170730350 Apr 10 '13 at 13:40

Clodoaldo Neto · Answer 3 · 2013-03-25T13:17:14.203

0

select
    date_trunc('hour', clock) "hour",
    sum((itemid = 23661)::integer) hits 
from history_uint
group by 1
order by 1

Or with all the hours filled:

select
    s.hour, count(itemid = 23661 or null) hits 
from
    (
        select date_trunc('hour', clock) "hour", itemid
        from history_uint
    ) h
    right join (
        select date_trunc('hour', d) "hour"
        from generate_series (
            (select min(clock::date)),
            (select max(clock)::date + 1) - interval '1 hour',
            '1 hour'
        ) s(d)
    ) s on s.hour = h.hour
group by 1
order by 1

edited Mar 25 '13 at 13:17

answered Mar 25 '13 at 12:43

Clodoaldo Neto

118,695
26
233
260

1

I think date_trunc won't work with clock because clock is not a timestamp object but integer (epoch time) – 170730350 Apr 10 '13 at 13:34

Iterative summations in SQL

3 Answers3