On Mon, Aug 13, 2012 at 5:53 PM, Andrew Sullivan <ajs@crankycanuck.ca> wrote:
On Mon, Aug 13, 2012 at 05:28:24PM -0700, Carey Tilden wrote:
> how to solve it. I have a list of program start/stop times, and I want to > know how long each run takes to complete. The thing that's really tripping > me up is there are gaps in the sequence. I've figured out how to collapse > the results down to a single row per attempt, but I can't quite figure out > how to further collapse down each full run to its own row. It'd be easy if > I had a session_id or something to group on, but I don't. All I have are > the start/stop times.
Looking at your data and the sample output you provided, it would appear that the "real" start time of a program is always the one that is started _after_ a completion (or else it's the very first start). In other words, it's never the case that a "start" in the program_runs data is the start of a new run when an existing, previously-started run hasn't completed.
Is that right? If so, then you ought to be able to use windowing functions. For each completion, pick the earliest start before it that is _after_ a completion or, if there is no such completion, is the very first record. See the discussion of windowing functions in the manual for how to do this.
That's right, but I'm not simply trying to see how long it's been between the "real" start and the "real" end. I want to exclude the gaps between runs. I'm looking for how many minutes it spent actually processing.
