On Tue, Mar 18, 2008 at 9:00 AM, Dan Searle <dan@xxxxxxxxxx> 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. -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
