Group by 15 Minute Steps

From: Martin Knipper <martin(at)mk-os(dot)de>
To: pgsql-sql(at)postgresql(dot)org
Subject: Group by 15 Minute Steps
Date: 2005-03-19 21:55:13
Message-ID: 423C9FC1.1050902@mk-os.de
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

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

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message tgl 2005-03-20 16:21:11 Re: Mail Authentification
Previous Message Leon Stringer 2005-03-18 21:07:51 Re: Consecutive row count query