From: | hari(dot)fuchs(at)gmail(dot)com |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Finding first free time from reservations table |
Date: | 2012-11-15 09:17:54 |
Message-ID: | 87txsr5inx.fsf@hf.protecting.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
"Andrus" <kobruleht2(at)hot(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Albe Laurenz | 2012-11-15 09:53:40 | Re: File system level copy |
Previous Message | Wang, Hao | 2012-11-15 09:12:46 | Re: File system level copy |