Re: Finding first free time from reservations table - Mailing list pgsql-general

"Andrus" <kobruleht2@hot.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