From: Jonathan Daugherty <cygnus@cprogrammer.org>
To: pgsql-sql@postgresql.org
Subject: Re: Group by 15 Minute Steps
Date: Sun, 20 Mar 2005 20:30:10 -0800
Message-ID: <20050321043010.GC20336@vulcan.cprogrammer.org> (raw)
In-Reply-To: <423C9FC1.1050902@mk-os.de>
References: <423C9FC1.1050902@mk-os.de>
# 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
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: cygnus@cprogrammer.org
Subject: Re: Group by 15 Minute Steps
In-Reply-To: <20050321043010.GC20336@vulcan.cprogrammer.org>
* 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