Find free adjacent timeslot in postgres

Question

I have a table containing timeslots for booking appointments and i try to figure out a way with a sql statement/view to find adjacent free timeslots for appointments of different duration.

The create table looks like 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 is example insert data where a available_status_id of 1 means a free timeslot and a available_status_id of 2 is a free timeslot:

INSERT INTO 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 INTO 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 am looking for a query in postgres to find all free timeslots for appointments of different durations like 15, 30 or 60 minutes. At the moment I just get all free timeslots from the database and iterate over them in Java and add together the minutes of duration until I have found enough adjacent timeslots and return then the first timeslot for each subgroup to be displayed in a calendar. But there must be a better and quicker way in postgres? Thanks in advance

Edit

Input is the needed duration in minutes (e.g 60), the employee_id (e.g. 5) and a date (e.g. 09.09.2013). Required output are all subsets that are adjacent (in time), free and have enough duration. For the above example this could 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

Have you considered using timestamps for the date+time interval ? — wildplasser
– wildplasser, Commented Sep 4, 2013 at 13:04
I have considered this but at first I thought it would be more important to retrieve and sort the date parts separately and now it would be difficult to change this. Would this change anything? — Dani
– Dani, Commented Sep 5, 2013 at 7:56
Your inserts are invalid. In SQL (and therefor in Postgres) string literals need to be enclosed in single quotes. double quotes denote object names (tables, columns). "2013" refers to a column named 2013, '2013'is a string with the value 2013. Additionally why are you storing numbers as characters? If you choose to split up a date into it's components it makes much more sense to at least define those components as integers. Storing numbers in character columns is almost always a very bad idea. And I agree with wildplasser: using timestamps would make much more sense. — user330315
– user330315, Commented Sep 5, 2013 at 9:05

user1455836 · Accepted Answer · 2013-09-05 08:57:01Z

Personally, I think it's good idea to make this in Java code.

Other option may be creating PostgreSQL function with cursor.

But if you really want to perform this in one SQL request and suppose you have timeslot_id that is differs by exactly one for each consecutive time slot and you can predict maximum appointment duration and number of time slots needed you can try something like this:

 select ts1.timeslot_id as start_timeslot_id, coalesce(t4.timeslot_id, t3.timeslot_id, t2.timeslot_id, t1.timeslot_id) as 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 as duration_minutes from timeslot ts1 left join timeslot ts2 on ts1.timeslot_id+1 = ts2.timeslot_id and t12.available_status_id = 1 left join timeslot ts3 on ts2.timeslot_id+1 = ts3.timeslot_id and ts3.available_status_id = 1 left join timeslot ts4 on ts3.timeslot_id+1 = ts4.timeslot_id and ts4.available_status_id = 1 where ts1.start_year = '2013' -- these all are your input parameters and ts1.start_month = '09' and ts1.start_day = '09' and employee_id = 5 and 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, this request will give you every possible time slots that are bigger or equals to required one. I didn't try to run this query against real database, so it might contain errors.

Hi your statement is absolutely amazing, after some fighting with brackets and casting around and changing your timeslot_id + 1 to successor it does exactly what I intended. Thank you very much :-)

joop · Accepted Answer · 2013-09-05 09:49:07Z

CREATE TABLE appoint_calendar ( appoint_calendar_id SERIAL NOT NULL PRIMARY KEY); INSERT INTO appoint_calendar(appoint_calendar_id) VALUES (761),(762); CREATE TABLE employee (employee_id SERIAL NOT NULL PRIMARY KEY); INSERT INTO employee(employee_id) VALUES (4),(5); CREATE TABLE available_status (available_status_id SERIAL NOT NULL PRIMARY KEY); INSERT INTO available_status(available_status_id) VALUES (1),(2); CREATE TABLE timeslot ( timeslot_id bigserial NOT NULL PRIMARY KEY , duration bigint , successor bigint , predecessor bigint , start_date timestamp with time zone , end_date timestamp with time zone , employee_id integer NOT NULL REFERENCES employee (employee_id) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE NO ACTION , available_status_id integer REFERENCES available_status (available_status_id) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE NO ACTION , appoint_calendar_id integer REFERENCES appoint_calendar (appoint_calendar_id) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE NO ACTION ); INSERT INTO timeslot(timeslot_id, duration,successor,predecessor,start_date,end_date,employee_id,available_status_id,appoint_calendar_id) VALUES (11870, 30, null, 11869, '2013-09-02 18:00:00', '2013-09-02 18:30:00', 4, 1, null) , (11904, 30, 12000, 11999, '2013-09-09 10:30:00', '2013-09-09 11:00:00', 5, 2, 761) ;

Select some timeslots (the interval arithmetic is not entirely correct, YMMV)

SELECT * FROM timeslot ts WHERE ts.employee_id IN(5) AND ts.available_status_id IN(1, 2) AND ts.start_date::date = '2013-09-09'::date AND ts.end_date >= (ts.start_date + '30 min'::interval) ;

That is a better table definition, but how does this answer the question?
The OP could plug in his own application logic (my interval logic is not correct, but enough to get him going) Compared to the timestamps, the interval-arithmatic would be very hard using separate {yy,mm,dd,hh,mm} fields. At the moment, I am too lazy to create a function anyway.
Ah, I didn't see the SELECT after the DDL. Maybe you should separate them somehow visually (two different code blocks)
Thanks for your answer. My application logic is basically that I retrieve all free timeslots from the database and iterate over the successors until I have enough timeslots for the duration given and than place the first timslot of this subset in a list to be displayed. Your solution does not work for my case because all of the adjacent timeslots should be displayed, so that if the duration is given with 60 minutes two adjacent (e.g. 18:00 and 18:30 if the timslot size is 30 minutes) timeslots are displayed.

Collectives™ on Stack Overflow

Find free adjacent timeslot in postgres

2 Answers 2

1 Comment

4 Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

4 Comments

Related