Обсуждение: 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

I'm trying to generate a result set of all the free time slots in
availability by taking the difference of the two. I've so far been
unable to generate anything that works. Is this possible?

Mike

--
Mike Erickson <mee@quidquam> http://www.quidquam.com/
"Hatred is the coward's revenge for being intimidated" - George Bernard Shaw

On Fri, Oct 27, 2000 at 04:13:13AM -0700, Mike E wrote:
> 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
>

not sure about the exact syntax, but to give you an idea:

select av.starttime as start_time,ap.starttime as end_time from availability av, appointments ap where
av.emp_id=ap.emp_idand av.emp_id=1; 

Same concept for the second row and then use the union-feature of the select-command to join.


hope this helps,
peter

--
mag. peter pilsl
email: pilsl@goldfisch.at
pgp-key available

On Fri, Oct 27, 2000 at 02:13:07PM +0200, Peter Pilsl wrote:
> On Fri, Oct 27, 2000 at 04:13:13AM -0700, Mike E wrote:
> > 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
> >
>
> not sure about the exact syntax, but to give you an idea:

That was just an example. What I would like to do is for any given
range of availabilty, take all the appointments, subtract/difference
them out and get a result set of all the time slots that are left.
Is this something that can be done in SQL, or will I have to pull
all the relevant data out and do all the logic in my application?

I will have a varying number of appointments on each day, which
possibly more than one availability record as well.

Thanks,

Mike

--
Mike Erickson <mee@quidquam> http://www.quidquam.com/
"Hatred is the coward's revenge for being intimidated" - George Bernard Shaw

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