Re: [HACKERS] Collating records based on a custom group by (aggregate like) function

On Tue, Mar 18, 2008 at 9:00 AM, Dan Searle <dan(at)adelix(dot)com> wrote:
> I've racked my brain about this but can't think of a simple solution,
> even though this appears to be a simple problem, any suggestions much
> appreciated.

Your fact is split across more than one row.

I recommend that you refactor the table to include start/end time
pairs, then you'll have a complete fact per row, and a chance for more
efficient temporal data retrieval.

If that's not possible, consider creating a new helper table with
start/end times and using a trigger on the main table to update/insert
rows on the helper table, then querying from it.

If none of that is possible, then you'll have to resort to writing
kludges like self joins or something like this ...

SELECT e, MIN(t), MAX(t)
FROM t
GROUP BY e, MOD(EXTRACT(MINUTE FROM t)::INT / 5, 60 / 5) ;

... which works for your example data, but it's pretty clear this will
break if/when there are overlapping hours, etc.

Last, but not least, I recommend you read this:

Developing Time-Oriented Database Applications in SQL
by Richard T. Snodgrass
Publications link:
http://www.cs.arizona.edu/~rts/publications.html
PDF link:
http://www.cs.arizona.edu/~rts/tdbbook.pdf

In any case, good luck.