Thread: Group by 15 Minute Steps
Hi, does anyone hava an idea how to group data e.g by 15 minute steps? I have the following data in my "data_diff" table [...] snmp=# \d data_diff Table "public.data_diff" Column | Type | Modifiers -----------------+---------+-----------------------------------------------------------id | integer | not nulldefault nextval('public.data_diff_id_seq'::text)device | integer |psqlzeit | integer |snmpzeit | integer |ifinoctets | bigint |ifoutoctets | bigint | Indexes: "data_diff_pkey" PRIMARY KEY, btree (id) Foreign-key constraints: "data_diff_device_fkey" FOREIGN KEY (device) REFERENCES device(id) ON UPDATE CASCADE ON DELETE CASCADE [...] An example query looks like this: snmp=# select snmpzeit as snmp,ifinoctets,ifoutoctets from data_diff where device=5 order by psqlzeit desc; snmp | ifinoctets | ifoutoctets ------------+------------+-------------1111268704 | 111382 | 2805661111268405 | 78874 | 225921111268104| 88645 | 328031111267804 | 76273 | 19024 ... 1111255204 | 149963 | 628891111254904 | 164788 | 815941111254604 | 147720 | 620931111254305| 161958 | 788131111254005 | 155495 | 68143 Usally, the steps between each entries is 300 seconds. How can I group by by 15, 30, 45 minutes so that i can get averages over the specified timeframe? Greetings, Martin
# Usally, the steps between each entries is 300 seconds. How can I # group by by 15, 30, 45 minutes so that i can get averages over the # specified timeframe? For 15-minute data, I'd compute the "quadrant" of each record and group by the quadrant number. Anything that occurs from :00 to :14 is in quadrant zero, :15 - :29 is quadrant 1, etc., yielding quadrants 0-3. mydb> SELECT (time / (15 * 60)) AS quadrant, SUM(ifinoctets) FROM mytable GROUP BY quadrant ORDER BY quadrant; Divide the time by 60 to get minutes and 15 to get quadrants. You can see how to extend this for other intervals. -- Jonathan Daugherty Command Prompt, Inc. - http://www.commandprompt.com/ PostgreSQL Replication & Support Services, (503)667-4564