Re: "next" - Mailing list pgsql-novice

On Mon, Dec 02, 2002 at 04:54:43PM +0000, Malcolm Hutty wrote:
>
> Suppose I've got a table containing train departure and arrival times,
> like so:
>
> SELECT id,departure,arrival,arrival-departure AS duration FROM timetable;
>
>  id |       departure        |        arrival         | duration
> ----+------------------------+------------------------+----------
>   1 | 2002-12-02 14:00:00+00 | 2002-12-02 15:00:00+00 | 01:00
>   7 | 2002-12-02 17:00:00+00 | 2002-12-03 14:00:00+00 | 21:00
>   2 | 2002-12-03 17:00:00+00 | 2002-12-03 18:00:00+00 | 01:00
>   6 | 2002-12-03 21:00:00+00 | 2002-12-04 04:00:00+00 | 07:00
>   4 | 2002-12-04 10:00:00+00 | 2002-12-04 18:00:00+00 | 08:00
>   5 | 2002-12-05 08:00:00+00 | 2002-12-05 10:00:00+00 | 02:00
>
>
> Can anyone advise as to the best way to express the following question
> in SQL: "What's the earliest arrival time where there isn't a
> subsequent departure time within (e.g.) 7 hours?". The table will
> contain many rows.
>
> I feel foolish - it _looks_ simple - but I've been banging my head
> against the wall trying to implement this as either a JOIN or a
> subselect, and it's starting to hurt.

create table trains (id serial primary key, depart timestamp not null,
arrive timestamp not null check (arrive > depart));

insert into train (depart,arrive) values ('2002-01-01 1:00
PM','2002-01-01 2:00 PM');

insert into trains (depart,arrive) values ('2002-01-01 1:00
PM','2002-01-01 2:00 PM');

insert into trains (depart,arrive) values ('2002-01-01 4:00
PM','2002-01-01 5:00 PM');

insert into trains (depart,arrive) values ('2002-01-02 4:00
PM','2002-01-01 5:00 PM');

insert into trains (depart,arrive) values ('2002-01-02 4:00
PM','2002-02-01 5:00 PM');

insert into trains (depart,arrive) values ('2002-01-09 4:00
PM','2002-02-09 5:00 PM');


joel@joel=# select * from trains;
 id |       depart        |       arrive
 ----+---------------------+---------------------
 1 | 2002-01-01 13:00:00 | 2002-01-01 14:00:00
 2 | 2002-01-01 16:00:00 | 2002-01-01 17:00:00
 4 | 2002-01-02 16:00:00 | 2002-02-01 17:00:00
 5 | 2002-01-09 16:00:00 | 2002-02-09 17:00:00


Ok, so trains 2 and 4 have arrivals where the last departure is more
than 7 hours away. And train 5 will also appear, since there is no
departure after it.

We can find these with:

select id,
       arrive
from   trains t1
where  t1.arrive + '7 hours' < all ( select depart
                                     from   trains t2
                     where  t2.depart > t1.arrive );

to find these earliest of these, just order them by arrive, and limit 1
on the outer query, as such:

select id,arrive from trains t1 where t1.arrive + '7 hours' < all
(select depart from trains t2 where t2.depart > t1.arrive ) order by
arrive limit 1;

And we get train #1, which is the earliest train arrival that meets the
requirements.

--

Joel BURTON  |  joel@joelburton.com  |  joelburton.com  |  aim: wjoelburton
Independent Knowledge Management Consultant