Archive for July 14th, 2009
On Jul 1, 2009, PostgreSQL 8.4 was released.
Among other imporvements, this version supports window functions, recursive queries and common table expressions (CTE‘s).
Despite being a minor release (accoring to the version numeration), this version can become quite a milestone, since these features make developer’s life much, much easier.
Let’s check how efficienly these features are implemented.
To do this, I’ll take some tasks that I wrote about in the previous blog posts and try to reimplement them using new PostgreSQL features.
I’ll start with quite a common task of flattening the intersecting timespans which I wrote about in this article:
This task requires calculating a running maximum and taking a previous record from a recordset, and therefore is a good illustration for window functions.
A quick reminder of the problem, taken from Stack Overflow:
I have lots of data with start and stop times for a given ID and I need to flatten all intersecting and adjacent timespans into one combined timespan.
To make things a bit clearer, take a look at the sample data for 03.06.2009:
The following timespans are overlapping or contiunous and need to merge into one timespan:
date start stop 2009.06.03 05:54:48:000 10:00:13:000 2009.06.03 09:26:45:000 09:59:40:000
The resulting timespan would be from 05:54:48 to 10:00:13.
Since there’s a gap between 10:00:13 and 10:12:50, we also have the following timespans:
date start stop 2009.06.03 10:12:50:000 10:27:25:000 2009.06.03 10:13:12:000 11:14:56:000 2009.06.03 10:27:25:000 10:27:31:000 2009.06.03 10:27:39:000 13:53:38:000 2009.06.03 11:14:56:000 11:15:03:000 2009.06.03 11:15:30:000 14:02:14:000 2009.06.03 13:53:38:000 13:53:43:000 2009.06.03 14:02:14:000 14:02:31:000
which result in one merged timespan from 10:12:50 to 14:02:31, since they’re overlapping or adjacent.
Any solution, be it SQL or not, is appreciated.
Let’s create a sample table:
Read the rest of this entry »