X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 39A9852A64 for ; Sat, 19 Mar 2005 21:54:44 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 66662-05 for ; Sat, 19 Mar 2005 21:54:41 +0000 (GMT) Received: from mail.ffm.my-linux.net (debian.ffm.my-linux.net [213.83.34.100]) by svr1.postgresql.org (Postfix) with ESMTP id 23D5D565D8 for ; Sat, 19 Mar 2005 21:54:40 +0000 (GMT) Received: from [192.168.100.100] (unknown [192.168.100.100]) by mail.ffm.my-linux.net (Postfix) with ESMTP id E984A3EC0A for ; Sat, 19 Mar 2005 22:54:32 +0100 (CET) Message-ID: <423C9FC1.1050902@mk-os.de> Date: Sat, 19 Mar 2005 22:55:13 +0100 From: Martin Knipper User-Agent: Mozilla/5.0 (de-AT; rv:1.7.5) X-Accept-Language: de, en MIME-Version: 1.0 To: pgsql-sql@postgresql.org Subject: Group by 15 Minute Steps Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests= X-Spam-Level: X-Archive-Number: 200503/266 X-Sequence-Number: 20745 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 null default 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 | 280566 1111268405 | 78874 | 22592 1111268104 | 88645 | 32803 1111267804 | 76273 | 19024 ... 1111255204 | 149963 | 62889 1111254904 | 164788 | 81594 1111254604 | 147720 | 62093 1111254305 | 161958 | 78813 1111254005 | 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