Generate time slots for booking an appointment using generate series

Question

I am trying to generate an available time slot function. for each day I am capturing start time and end time.

for eg: start time is 08:00 AM and end time is 03:00 PM.

I have another table that captures break time for the same day,

for eg: 11:00 AM to 11:15 AM and 01:00 PM to 1:30 PM.

And in another table I am storing already booked slots

for eg: 
09:00 AM to 09:30 AM
11:15 AM to 11:45 AM
01:30 PM 02:00PM

I am using generate_series to generate time intervals between start and end time.

select * FROM generate_series(
       timestamp '2016-11-09 08:00 AM',
       timestamp '2016-11-09 03:00 PM',
       INTERVAL '30m'
    ) t

I need to exclude the break time and already booked time from this generated series. I tried to use except function but this slot 11:15 AM to 11:45 AM is not able to remove. How do I generally write a condition to remove this?

Except query

SELECT t
    FROM generate_series(
       timestamp  '2016-11-09 08:00 AM',
       timestamp  '2016-11-09 03:00 PM',
       INTERVAL '1 hour'
    ) t
    EXCEPT
    SELECT concat(start_date,' ' , start_time)::timestamp as booked 
    FROM my_table where start_date::date = '2016-11-09';

my_table details

CREATE TABLE public.my_table
(
id bigint NOT NULL,
start_date character varying(500),
start_time character varying(500),
CONSTRAINT pk_my_table PRIMARY KEY (id))
WITH (
OIDS=FALSE
);

Exactly how are you storing those booked slots, as strings? What are the names of the tables? and the columns? Please show us your query that uses the except function. — Paul Maxwell
@Used_By_Already Thanks for your quick reply Yes, I am storing it as the string. Table is having start_date and start_time column.I updated my question by adding except query. — Shamseer Pc
Sorry, but why on earth would you use character varying(500) for dates and times? Date and or time information is MUCH more efficiently handled in their "correct" data types e.g. postgresql.org/docs/9.1/static/datatype-datetime.html Did you know that date/time data is stored as sets of numbers? (and that is why they are much more efficient than strings) — Paul Maxwell

Paul Maxwell Paul Maxwell · Accepted Answer · 2018-10-22T23:23:08

The following works, I suggest you use a smaller interval, probably 15 minutes:

with my_table as (
    select '09:00 AM' start_time , '2016-11-09'::character varying start_date
    )

SELECT *
    FROM generate_series(
       timestamp  '2016-11-09 08:00 AM',
       timestamp  '2016-11-09 03:00 PM',
       INTERVAL '15 minutes'
    ) t
EXCEPT
SELECT concat(start_date,' ' , start_time)::timestamp as booked 
FROM my_table where start_date::date = '2016-11-09'

But honestly I simply would not store the appointment as a string that looks like a date or a string that looks like time of day. A character (500) column would happily accept data such as 'this is not a date or a time' and all of a sudden you have a bug because it won't convert. or dates such as 30/02/2019 can exist in the data and be difficult to find.

Why not just store the appointment start as a timestamp? This would be so much more efficient. e.g.

with my_table as (
    select '2016-11-09 09:00:00'::timestamp start_date
    )


SELECT *
    FROM generate_series(
       timestamp  '2016-11-09 08:00 AM',
       timestamp  '2016-11-09 03:00 PM',
       INTERVAL '15 minutes'
    ) t
EXCEPT
SELECT start_date
FROM my_table 
where start_date >= '2016-11-09' and start_date < '2016-11-10'

Such a query would make use of an index on that start_date column to filter the data.

Please do not use strings to store dates or times. I wouldn't separate date from time of day either.

Generate time slots for booking an appointment using generate series

1 Answers