From: Martin Knipper <martin@mk-os.de>
To: pgsql-sql@postgresql.org
Subject: Group by 15 Minute Steps
Date: Sat, 19 Mar 2005 22:55:13 +0100
Message-ID: <423C9FC1.1050902@mk-os.de> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: martin@mk-os.de
Subject: Re: Group by 15 Minute Steps
In-Reply-To: <423C9FC1.1050902@mk-os.de>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox