Here's how.
let's make a trivial diary table
create table diary ( ix serial primary key, startTime time, endTime time, activity varchar(80));leading to this table of data.
select * from diary;
| ix | starttime | endtime | activity |
| 1 | 09:00:00 | 09:30:00 | breakfast |
| 2 | 09:30:00 | 10:00:00 | jogging |
| 3 | 13:00:00 | 14:00:00 | lunch |
| 4 | 10:00:00 | 11:30:00 | meeting with joe |
| 5 | 14:00:00 | 18:00:00 | golfing at Royal Dornoch |
so the problem is this... how can I query that table to discover my free time on that day?
(there is 90 minutes before lunch)
NOTE - the activities are NOT in order!
the answer (and I found this in the excellent "sql cookbook") involves getting a view of that table in the correct order, and then performing a join on adjacent rows.
so first, get the ordered view.
=> select * from diary order by starttime;
| ix | starttime | endtime | activity |
| 1 | 09:00:00 | 09:30:00 | breakfast |
| 2 | 09:30:00 | 10:00:00 | jogging |
| 4 | 13:00:00 | 14:00:00 | lunch |
| 3 | 10:00:00 | 11:30:00 | meeting with joe |
| 5 | 14:00:00 | 18:00:00 | golfing at Royal Dornoch |
But this doesn't help... because there is no way to definatively refer to the "next" activity, to do this we need to replace ix with a generated rownumber. We will use the row_number() window function of postgres 8.4.
=> select * from (select row_number() over (order by starttime) as rownumber,
starttime, endtime, activity from diary) as ordered_diary;
| rownumber | starttime | endtime | activity |
| 1 | 09:00:00 | 09:30:00 | breakfast |
| 2 | 09:30:00 | 10:00:00 | jogging |
| 3 | 10:00:00 | 11:30:00 | meeting with joe |
| 4 | 13:00:00 | 14:00:00 | lunch |
| 5 | 14:00:00 | 18:00:00 | golfing at Royal Dornoch |
no we can self join that result set on itself..
(adding columns from the next row to the current row)
=> select * from
(select row_number() over (order by starttime) as row_number, * from diary) as diary1,
(select row_number() over (order by starttime) as row_number, * from diary) as diary2
where diary2.row_number = diary1.row_number+1
| row_number | ix | starttime | endtime | activity | row_number | ix | starttime | endtime | activity |
| 1 | 1 | 09:00:00 | 09:30:00 | breakfast | 2 | 2 | 09:30:00 | 10:00:00 | jogging |
| 2 | 2 | 09:30:00 | 10:00:00 | jogging | 3 | 4 | 10:00:00 | 11:30:00 | meeting with joe |
| 3 | 4 | 10:00:00 | 11:30:00 | meeting with joe | 4 | 3 | 13:00:00 | 14:00:00 | lunch |
| 4 | 3 | 13:00:00 | 14:00:00 | lunch | 5 | 5 | 14:00:00 | 18:00:00 | golfing at Royal Dornoch |
or more succinctly...
=> select diary1.activity as first_activity, diary1.endtime as finishes,
diary2.activity as next_activity, diary2.starttime as starts from
(select row_number() over (order by starttime) as row_number, * from diary) as diary1,
(select row_number() over (order by starttime) as row_number, * from diary) as diary2
where diary2.row_number = diary1.row_number+1
| first_activity | finishes | next_activity | starts |
| breakfast | 09:30:00 | jogging | 09:30:00 |
| jogging | 10:00:00 | meeting with joe | 10:00:00 |
| meeting with joe | 11:30:00 | lunch | 13:00:00 |
| lunch | 14:00:00 | golfing at Royal Dornoch | 14:00:00 |
so we simply now have to add a check to see if those times differ
so here's the final query!
=> select diary1.endtime as freetime_start, diary2.starttime as freetime_end from
(select row_number() over (order by starttime) as row_number, endtime from diary) as diary1,
(select row_number() over (order by starttime) as row_number, starttime from diary) as diary2
where diary2.row_number = diary1.row_number+1
and diary1.endtime < diary2.starttime
| freetime_start | freetime_end |
| 11:30:00 | 13:00:00 |
