sql - Find free adjacent timeslot in postgres -

i have table containing timeslots booking appointments , try figure out way sql statement/view find adjacent free timeslots appointments of different duration.

the create table looks this:

create table timeslot (   timeslot_id bigserial not null,   duration bigint,   successor bigint,   predecessor bigint,   start_year character varying not null,   start_month character varying not null,   start_day character varying not null,   start_hour character varying not null,   start_minute character varying not null,   end_year character varying not null,   end_month character varying not null,   end_day character varying not null,   end_hour character varying not null,   end_minute character varying not null,   employee_id integer not null,   available_status_id integer,   appoint_calendar_id integer   constraint timeslot_id primary key (timeslot_id),   constraint appoint_calendar_id foreign key (appoint_calendar_id)   references appoint_calendar (appoint_calendar_id) match simple   on update no action on delete no action,   constraint available_status_id foreign key (available_status_id)   references available_status (available_status_id) match simple   on update no action on delete no action,   constraint employee_id foreign key (employee_id)   references employee (employee_id) match simple   on update no action on delete no action ) 

here example insert data available_status_id of 1 means free timeslot , available_status_id of 2 free timeslot:

insert timeslot(         timeslot_id, duration, successor, predecessor, start_year, start_month,          start_day, start_hour, start_minute, end_year, end_month, end_day,          end_hour, end_minute, employee_id, available_status_id, appoint_calendar_id) values (11870, 30, null, 11869, "2013", "09",          "02", "18", "00", "2013", "09", "02",          "18", "30", 4, 1, null);  insert timeslot(         timeslot_id, duration, successor, predecessor, start_year, start_month,          start_day, start_hour, start_minute, end_year, end_month, end_day,          end_hour, end_minute, employee_id, available_status_id, appoint_calendar_id) values (11904, 30, 12000, 11999, "2013", "09",          "09", "10", "30", "2013", "09", "09",          "11", "00", 5, 2, 761); 

i looking query in postgres find free timeslots appointments of different durations 15, 30 or 60 minutes. @ moment free timeslots database , iterate on them in java , add minutes of duration until have found enough adjacent timeslots , return first timeslot each subgroup displayed in calendar. there must better , quicker way in postgres? in advance

edit

input needed duration in minutes (e.g 60), employee_id (e.g. 5) , date (e.g. 09.09.2013). required output subsets adjacent (in time), free , have enough duration. above example be:

 timeslot_id 11904 duration 30 successor 12000 predecessor 11999 start_year 2013 start_month 09 start_day 09 start_hour 10 start_minute 30 end_year 2013 end_month 09 end_day 09 end_hour 11 end_minute 00  employee_id 5 available_status_id 1 appoint_calendar_id null 

and

 timeslot_id 12000 duration 30 successor 11906 predecessor 11904 start_year 2013 start_month 09 start_day 09 start_hour 11 start_minute 00 end_year 2013 end_month 09 end_day 09 end_hour 11 end_minute 30 employee_id 5 available_status_id 1 appoint_calendar_id null 

personally, think it's idea make in java code.

other option may creating postgresql function cursor.

but if want perform in 1 sql request , suppose have timeslot_id differs 1 each consecutive time slot , can predict maximum appointment duration , number of time slots needed can try this:

select ts1.timeslot_id start_timeslot_id,      coalesce(t4.timeslot_id, t3.timeslot_id, t2.timeslot_id, t1.timeslot_id) end_timeslot_id,      coalesce(t4.end_hour, t3.end_hour, t2.end_hour, t1.end_hour)*60+coalesce(t4.end_minute, t3.end_minute, t2.end_minute, t1.end_minute) - t1.start_hour*60+t1.start_minute duration_minutes timeslot ts1 left join timeslot ts2  on ts1.timeslot_id+1 = ts2.timeslot_id , t12.available_status_id = 1  left join timeslot ts3  on ts2.timeslot_id+1 = ts3.timeslot_id , ts3.available_status_id = 1  left join timeslot ts4  on ts3.timeslot_id+1 = ts4.timeslot_id , ts4.available_status_id = 1  ts1.start_year = '2013' -- these input parameters , ts1.start_month = '09' , ts1.start_day = '09' , employee_id = 5 , coalesce(t4.end_hour, t3.end_hour, t2.end_hour, t1.end_hour)*60+coalesce(t4.end_minute, t3.end_minute, t2.end_minute, t1.end_minute) - t1.start_hour*60+t1.start_minute >= 60 -- duration in minutes 

presumably, request give every possible time slots bigger or equals required one. didn't try run query against real database, might contain errors.