On 28 October 2014 20:04, Thom Brown <t...@linux.com> wrote: > On 28 October 2014 19:14, Andrus <kobrule...@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; >
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