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

On 28 October 2014 20:04, Thom Brown <thom(at)linux(dot)com> wrote:

> 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;
>

A correction to this. As it stands, it will show times like the following:

yksus | period
------------+------------------------
...
JOHN | 2014-10-30 19:45:00+00
MARY | 2014-10-30 19:45:00+00
JOHN | 2014-10-30 20:00:00+00
MARY | 2014-10-30 20:00:00+00
JOHN | 2014-10-30 20:15:00+00
MARY | 2014-10-30 20:15:00+00
JOHN | 2014-10-30 20:30:00+00
MARY | 2014-10-30 20:30:00+00
JOHN | 2014-10-30 20:45:00+00
MARY | 2014-10-30 20:45:00+00
JOHN | 2014-10-30 21:00:00+00
MARY | 2014-10-30 21:00:00+00
JOHN | 2014-10-31 10:00:00+00
MARY | 2014-10-31 10:00:00+00
...

This is incorrect a 1.5 hour appointment after 19:30 would go beyond the
working hours. So that needs to be factored into it:

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::timetz BETWEEN '10:00'::timetz AND '21:00'::timetz - '1
hour 30 mins'::interval
AND times.period >= now()
ORDER BY 2, 1
LIMIT 300;

This gives you:

yksus | period
------------+------------------------
...
JOHN | 2014-10-30 19:15:00+00
MARY | 2014-10-30 19:15:00+00
JOHN | 2014-10-30 19:30:00+00
MARY | 2014-10-30 19:30:00+00
JOHN | 2014-10-31 10:00:00+00
MARY | 2014-10-31 10:00:00+00
JOHN | 2014-10-31 10:15:00+00
MARY | 2014-10-31 10:15:00+00
...

Regards

Thom