pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedGroup by 15 Minute Steps
2+ messages / 2 participants
[nested] [flat]
* Group by 15 Minute Steps
@ 2005-03-19 21:55 Martin Knipper <martin@mk-os.de>
0 siblings, 1 reply; 2+ messages in thread
From: Martin Knipper @ 2005-03-19 21:55 UTC (permalink / raw)
To: 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
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Group by 15 Minute Steps
@ 2005-03-21 04:30 Jonathan Daugherty <cygnus@cprogrammer.org>
parent: Martin Knipper <martin@mk-os.de>
0 siblings, 0 replies; 2+ messages in thread
From: Jonathan Daugherty @ 2005-03-21 04:30 UTC (permalink / raw)
To: pgsql-sql
# 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
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2005-03-21 04:30 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-03-19 21:55 Group by 15 Minute Steps Martin Knipper <martin@mk-os.de>
2005-03-21 04:30 ` Jonathan Daugherty <cygnus@cprogrammer.org>
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