need to join successive log entries into one - Mailing list pgsql-sql

From George Young
Subject need to join successive log entries into one
Date
Msg-id 01031413092300.02477@pen
Whole thread Raw
Responses Re: need to join successive log entries into one
Re: need to join successive log entries into one
List pgsql-sql
I have a table like:

run   | seq   | start                | done  1415|    261| 2001-01-29 12:36:55| 2001-02-07 13:02:38 1415|    263|
2001-02-1407:40:04| 2001-02-15 16:05:04 1415|    264| 2001-02-16 16:05:05| 2001-03-08 16:34:03 1415|    265| 2001-03-08
16:34:04|                      1747|    257| 2001-02-15 09:14:39| 2001-03-01 08:58:03 1747|    258| 2001-03-01
08:58:04|2001-03-01 09:01:27 1747|    260| 2001-03-01 09:01:39| 2001-03-01 09:02:39 1747|    262| 2001-03-05 13:13:58|
                    1954|    218| 2001-02-02 20:55:39| 2001-02-08 12:44:48 1954|    219| 2001-02-08 12:44:49|
2001-02-0812:47:36 1954|    220| 2001-02-08 12:47:36| 2001-02-08 16:50:33 1954|    221| 2001-02-08 16:50:33| 2001-02-08
16:50:451954|    222| 2001-02-08 16:50:46| 2001-02-12 14:36:41 1954|    223| 2001-02-12 14:36:41| 2001-03-02 10:17:15
 

This is a log of some operations done on some runs. 'seq' is the step
within the run.

I need to produce a new table that coalesces immediately successive
operations on a run into one, e.g.:

run   |  start               | done 1415|  2001-01-29 12:36:55| 2001-02-07 13:02:38 1415|  2001-02-14 07:40:04| 1747|
2001-02-1509:14:39| 2001-03-01 09:02:39 1747|  2001-03-05 13:13:58|   1954|  2001-02-02 20:55:39| 2001-03-02 10:17:15
 

i.e. where a run has one or more steps with succesive seq values,
or equivalently, with abutting start/end values, then I want
the new table to have only one entry representing the full span of
time.  Null 'done' just means it's not done yet.  

Unfortunately, the start time of a 'succesive' op is sometimes 1 second 
later that the 'done' time of the previous one, so maybe using 
the seq field is simpler.

Can anyone think of a way I can do this in postgres?

--
George Young,  Rm. L-204        gry@ll.mit.edu
MIT Lincoln Laboratory
244 Wood St.
Lexington, Massachusetts  02420-9108    (781) 981-2756


pgsql-sql by date:

Previous
From: Roland Roberts
Date:
Subject: Re: my pgsql error?
Next
From: Andrew Perrin
Date:
Subject: Re: Rule/currval() issue