Re: How to find earlest possible start times for given duration excluding reservations

On 28 October 2014 19:14, Andrus <kobruleht2(at)hot(dot)ee> wrote:

> Hi!
>
> >Would you be able to adapt this to your needs?:
>
> Thank you very much.
> Great solution.
> I refactored it as shown below.
> Query returns only dates for single day. Changing limit clause to 300
> does not return next day.
> How to return other day dates also, excluding sundays and public
> holidays in pyha table ?
>

It's not a robust solution if you need it to span days, but you could just
increment the 2nd timestamptz parameter in the generate_series function
call by a year:

generate_series('2014-10-28 10:00+2'::timestamptz, '2015-10-28 21:00+2',
'15 mins'::interval)

It's hacky, but it should work, but if you happened to have a policy
whereby reservations couldn't be made beyond, say, 3 months in advance, you
could just give it a date 3 months in the future, and make sure that the
first parameter is capped to the same range.

So here's an example of what you could do (although it could probably be
simplified and made more elegant). Here it will find times from the
current time until 3 months in the future. It also filters out holiday
dates.

SELECT yksus2.yksus, times.period
FROM generate_series(now()::date::timestamptz, now()::date::timestamptz +
'3 months'::interval, '15 mins'::interval) times(period)
CROSS JOIN yksus2
LEFT JOIN reservat ON tstzrange(times.period,times.period + '1 hour 30
mins'::interval, '[)') && reservat.during
AND yksus2.yksus = reservat.objekt2
LEFT JOIN pyha ON times.period::date = pyha.pyha::date
WHERE reservat.during IS NULL
AND pyha.pyha IS NULL
AND times.period::time BETWEEN '10:00'::time AND '21:00'::time
AND times.period >= now()
ORDER BY 2, 1
LIMIT 300;

--
Thom