[lug] postgresql query question

Tkil tkil at scrye.com
Mon May 15 17:42:14 MDT 2006


>>>>> "Nick" == Nick Golder <nrg at nirgo.net> writes:

Nick> Is there a way to query data between two timestamps based on
Nick> some selected interval?  For example, I want to query all data
Nick> for every 60 second interval between 2004-09-10 14:00:00 and
Nick> 2004-10-10 14:00:00.

Those sound like two different queries (or, at least, that the latter
is a case where you want to do the former multiple times) It sounds
like you want a simple "group by" query.  In Oracle, I'd do:

| SELECT TO_STRING( 'YYYY-mm-dd HR24:MM:00', date_column ) AS minute,
|        SUM( factoid ), AVG( factoid )
|   FROM table
|   GROUP BY TO_STRING( 'YYYY-mm-dd HR24:MM:00', date_column );

Sane databases tend to allow one to use either numeric or alias names
in the "group by" clause, so you wouldn't have to replicate the
expression, but Oracle is a bit stupid that way.  E.g., given that
this other DB had a function "trunc_to_minute" to remove the unwanted
precision in the timestamp, we can request that the results be grouped
on column 1:

| SELECT TRUNC_TO_MINUTE( date_column ) AS minute, SUM( factoid )
|   FROM table
|   GROUP BY 1;

Even in Oracle, a nested query could clean it up a little:

| SELECT minute, SUM( factoid ), AVG( factoid )
|   FROM ( SELECT TO_STRING( 'YYYY-mm-dd HR24:MM:00',
|                            date_column ) AS minute,
|                 factoid
|            FROM table )
|   GROUP BY minute;

Anyway.  Is that what you were looking for?

t.



More information about the LUG mailing list