Lists: | pgsql-general |
---|
From: | "Andrus" <kobruleht2(at)hot(dot)ee> |
---|---|
To: | <pgsql-general(at)postgresql(dot)orG> |
Subject: | Finding first free time from reservations table |
Date: | 2012-11-14 21:02:33 |
Message-ID: | C4548EC84E8746E9B4BAA92310F1A4FE@dell2 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
I’m looking for a way to find first free time in reservations table.
Reservation table contains reservations start dates, start hours and durations.
Start hour is by half hour increments in working hours 8:00 .. 18:00 in work days.
Duration is also by half hour increments in day.
CREATE TABLE reservation (
id serial primary key,
startdate date not null, -- start date
starthour numeric(4,1) not null , -- start hour 8 8.5 9 9.5 .. 16.5 17 17.5
duration Numeric(3,1) not null -- duration by hours 0.5 1 1.5 .. 9 9.5 10
);
table structure can changed if required.
How to find first free half hour in table which is not reserved ?
E.q if table contains
startdate starthour duration
14 9 1 -- ends at 9:59
14 10 1.5 -- ends at 11:29, e.q there is 30 minute gap before next
14 12 2
14 16 2
result should be:
starthour duration
11.5 0.5
Probably PostgreSql 9.2 window function should used to find
first row whose starthour is greater than previous row starthour + duration
How to write select statement which returns this information ?
Andrus.
I posted this also in
From: | hari(dot)fuchs(at)gmail(dot)com |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Finding first free time from reservations table |
Date: | 2012-11-15 09:17:54 |
Message-ID: | 87txsr5inx.fsf@hf.protecting.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
"Andrus" <kobruleht2(at)hot(dot)ee> writes:
> How to find first free half hour in table which is not reserved ?
>
> E.q if table contains
>
> startdate starthour duration
> 14 9 1 -- ends at 9:59
> 14 10 1.5 -- ends at 11:29, e.q there is
> 30 minute gap before next
> 14 12 2
> 14 16 2
>
> result should be:
>
> starthour duration
> 11.5 0.5
>
>
> Probably PostgreSql 9.2 window function should used to find
> first row whose starthour is greater than previous row starthour +
> duration
Yes, you could use something like this:
SELECT min(c1)
FROM (
SELECT starthour + duration AS c1,
lead(starthour) OVER (ORDER BY starthour) AS c2
FROM tst
) dummy
WHERE c2 >= c1 + 0.5
From: | Steve Crawford <scrawford(at)pinpointresearch(dot)com> |
---|---|
To: | Andrus <kobruleht2(at)hot(dot)ee> |
Cc: | pgsql-general(at)postgresql(dot)orG |
Subject: | Re: Finding first free time from reservations table |
Date: | 2012-11-15 18:45:40 |
Message-ID: | 50A53854.2050804@pinpointresearch.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
On 11/14/2012 01:02 PM, Andrus wrote:
> I’m looking for a way to find first free time in reservations table.
> Reservation table contains reservations start dates, start hours and
> durations.
> Start hour is by half hour increments in working hours 8:00 .. 18:00
> in work days.
> Duration is also by half hour increments in day.
> CREATE TABLE reservation (
> id serial primary key,
> startdate date not null, -- start date
> starthour numeric(4,1) not null , -- start hour 8 8.5 9 9.5 ..
> 16.5 17 17.5
> duration Numeric(3,1) not null -- duration by hours 0.5 1 1.5
> .. 9 9.5 10
> );
> table structure can changed if required.
I'm not sure if it will work well for your specific use-case and it
requires an up-to-date version (9.2+??) but I would recommend
investigating range types which have some characteristics that are
useful for reservation and calendaring applications including the
ability to have a "non-overlapping" constraint that prevents creating a
record with a range that overlaps an existing range in the table.
Instead of having three columns (startdate, starthour and duration) you
would have a single column of type tsrange which includes the starting-
and ending-times of each reservation.
Here's the info on range types:
http://www.postgresql.org/docs/9.2/static/rangetypes.html
If you want to limit reservations to start/end at half-hours and/or to
certain times of the day you will probably want to include those
constraints in your table definition.
You asked about finding a free half-hour but since you show durations
that exceed a half-hour, you may want to include the capability to
search for the first available occurrence of X free-time.
Range-types are new and I'm not experienced with them - others may have
better ideas - but the method of finding the first occurrence that
springs to mind is to make a query that uses generate_series to create a
list of "candidate" reservation periods of the desired duration and
select the first one that doesn't overlap an existing reservation. This
should work fine as long as you are looking a limited time in the future
(there are fewer than 20 possible start-times in a day so even looking
100-days ahead is only 2000 candidates) however generating a series of
ranges may involve a sub-query - I don't know if you can generate a
series of ranges directly.
Hope this helps.
Cheers,
Steve