[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