Re: How to find earlest possible start times for given duration excluding reservations - Mailing list pgsql-general

On 28 October 2014 19:14, Andrus <kobruleht2@hot.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;