Re: Time Difference

From: "Mike E" <mee@quidquam.com>
To: <pgsql-general@postgresql.org>
Sent: Friday, October 27, 2000 12:13 PM
Subject: [GENERAL] Time Difference


> I have the following setup:
>
> appt=# select * from availability \g
>  emp_id |    date    | start_time | end_time
> --------+------------+------------+----------
>       1 | 2000-10-30 | 08:00:00   | 14:30:00
>
> appt=# select * from appointments \g
>  start_time | end_time | emp_id | cus_id | services |    date
> ------------+----------+--------+--------+----------+------------
>  09:00:00   | 11:30:00 |      1 |      2 | {1,2,3}  | 2000-10-30
>
> Now, what I would like to get is the following:
>
>  start_time | end_time
> ------------+----------
>  08:00:00   | 09:00:00
>  11:30:00   | 14:30:00
>
>
> Mike

Thought this couldn't be done for a minute, but it can assuming you have
non-overlapping appointments. I simplified to think about it:

richardh=> select * from used;
 starts | ends
--------+------
      2 |    3
      5 |    6
      9 |   12

richardh=> select a.ends as gapstart, b.starts as gapend
           from used a, used b where b.starts>a.ends
           and b.starts = (
               select min(c.starts) from used c where c.starts>a.ends);

 gapstart | gapend
----------+--------
        3 |      5
        6 |      9


So - we're looking for the gap between the end of "a" and start of "b" where
"b" is the next used slot after "a". You'll need fake entries in used for
the start and end of the day to handle the start/end of your availabilty
period (or just fake it in your application). Not sure if you might want >=
instead of >

If you do have overlapping appointments, then this isn't going to help you,
but I'm guessing you don't.

HTH

- Richard Huxton