postgresql list of time slots from 'Monday' | 09:00:00 | 11:00:00

Question

I’m building a booking system where a user will set their availability eg: I’m available Monday’s from 9am to 11am, Tuesdays from 9am to 5pm etc… and need to generate a list of time slots 15mins apart from their availability.

I have the following table (but am flexible to changing this):

availabilities(day_of_week text, start_time: time, end_time: time)

which returns records like:

‘Monday’ | 09:00:00 | 11:00:00
‘Monday’ | 13:00:00 | 17:00:00
‘Tuesday’ | 08:00:00 | 17:00:00

So I’m trying to build a stored procedure to generate a list of time slots so far I've got this:

create or replace function timeslots ()
return setof timeslots as $$
  declare
    rec record;

  begin
    for rec in select * from availabilities loop
      /*
        convert 'Monday' | 09:00:00 | 11:00:00 into:
        2020-02-03 09:00:00
        2020-02-03 09:15:00
        2020-02-03 09:30:00
        2020-02-03 09:45:00
        2020-02-03 10:00:00
        and so on...
      */
      return next
    end loop
$$ language plpgsql stable;

I return a setof instead of a table as I'm using Hasura and it needs to return a setof so I just create a blank table.

I think I'm on the right track but am currently stuck on:

• how do I create a timestamp from 'Monday' 09:00:00 for the next monday as I only care about timeslots from today onwards?
• how do I convert 'Monday' | 09:00:00 | 11:00:00 into a list of time slots 15 mins apart?

Answer 1

how do I create a timestamp from 'Monday' 09:00:00 for the next monday as I only care about timeslots from today onwards?

You can use date_trunc for this (see this question for more info):

SELECT date_trunc('week', current_date) + interval '1 week';

From the docs re week:

The number of the ISO 8601 week-numbering week of the year. By definition, ISO weeks start on Mondays

So taking this value and adding a week gives next Monday (you may need to ammend this behaviour based upon what you want to do if today is monday!).

how do I convert 'Monday' | 09:00:00 | 11:00:00 into a list of time slots 15 mins apart?

This is a little tricker; generate_series will give you the timeslots but the trick is getting it into a result set. The following should do the job (I have included your sample data; change the values bit to refer to your table) - dbfiddle :

with avail_times as (
select
    date_trunc('week', current_date) + interval '1 week' + case day_of_week when 'Monday' then interval '0 day' when 'Tuesday' then interval '1 day' end + start_time as start_time,
    date_trunc('week', current_date) + interval '1 week' + case day_of_week when 'Monday' then interval '0 day' when 'Tuesday' then interval '1 day' end + end_time as end_time
from
    (
values 
('Monday','09:00:00'::time,'11:00:00'::time),
('Monday','13:00:00'::time,'17:00:00'::time),
('Tuesday','08:00:00'::time,'17:00:00'::time)
) as availabilities (day_of_week,
    start_time,
    end_time) )
select
    g.ts
from
    (
    select
        start_time,
        end_time
    from
        avail_times) avail,
    generate_series(avail.start_time, avail.end_time - interval '1ms', '15 minutes') g(ts);

A few notes:

• The CTE avail_times is used to simplify things; it generates two columns (start_time and end_time) which are the full timestamps (so including the date). In this example the first row is "2020-02-03 09:00:00, 2020-02-03 11:00:00" (I'm running this on 2020-02-02 so 2020-02-03 is next Monday).
• The way I'm converting 'monday' etc to a day of the week is a bit of a hack (and I have not bothered to do the full week); there is probably a better way but storing the day of week as an integer would make this simpler.
• I subtract 1ms from the end time because I'm assuming you dont want this in the result set.
• The main query is using a LATERAL Subquery. See this question for more info.

Aditional Question

how to adjust this so I can pass in a start and end date so I can get time slots for a particular period

You could do something like the following (just adjust the dates CTE to return whatever days you want to include; you could convert to a function or just pass the dates in as parameters).

Note that as @Belayer mentions my original solution did not cater for shifts over midnight so this addresses that too.

with dates as (
select
    day
from
    generate_series('2020-02-20'::date, '2020-03-10'::date, '1 day') as day ),
availabilities as (
select
    *
from
(
    values (1,'09:00:00'::time,'11:00:00'::time),
    (1,'13:00:00'::time,'17:00:00'::time),
    (2,'08:00:00'::time,'17:00:00'::time),
    (3,'23:00:00'::time,'01:00:00'::time) 
) as availabilities 
    (day_of_week,   -- 1 = monday
     start_time,
     end_time) ) ,
avail_times as (
select
    d.day + start_time as start_time,
    case
        end_time > start_time
        when true then d.day
        else d.day + interval '1 day' end + end_time as end_time
    from
        availabilities a
    inner join dates d on extract(ISODOW from d.day) = a.day_of_week )
select
    g.ts
from
    (
    select
        start_time,
        end_time
    from
        avail_times) avail,
    generate_series(avail.start_time, avail.end_time - interval '1ms', '15 minutes') g(ts)
order by
    g.ts;

Answer 2

The following uses much of the techniques mentioned by @Brits. They present some very good information, so I'll not repeat but suggest you review it (and the links). I do however take a slightly different approach. First a couple table changes. I use the ISO day of week 1-7 (Monday-Sunday) rather than the day name. The day name is easily extracted for the dater later. Also I use interval instead to time for start and end times. ( A time data type works for most scenarios but there is one it doesn't (more later).
One thing your description does not make clear is whether the ending time is included it the available time or not. If included the last interval would be 11:00-11:15. If excluded the last interval is 10:45-11:00. I have assumed to excluded it. In the final results the end time is to be read as "up to but not including".

-- setup 
create table availabilities (weekday integer, start_time interval, end_time interval);


insert into availabilities (weekday , start_time , end_time )
   select wkday
        , start_time
        , end_time 
     from (select * 
             from (values (1, '09:00'::interval, '11:00'::interval) 
                        , (1, '13:00'::interval, '17:00'::interval)
                        , (2, '08:00'::interval, '17:00'::interval) 
                        , (3, '08:30'::interval, '10:45'::interval)
                        , (4, '10:30'::interval, '12:45'::interval)                         
                  ) as v(wkday,start_time,end_time)
          ) r ;

select * from availabilities;

The Query
It begins with a CTE (next_week) generates a entry for each day of the week beginning Monday and the appropriate ISO day number for it. The main query joins these with the availabilities table to pick up times for matching days. Finally that result is cross joined with a generated timestamp to get the 15 minute intervals.

-- Main 
with next_week (wkday,tm) as 
     (SELECT n+1, date_trunc('week', current_date) + interval '1 week' + n*interval '1 day' 
        from generate_series (0, 6) n
     )  
select to_char(gdtm,'Day'), gdtm start_time, gdtm+interval '15 min' end_time
  from ( select wkday, tm, start_time, end_time
           from next_week   nw
           join availabilities av 
             on (av.weekday = nw.wkday)  
       ) s 
  cross join lateral 
        generate_series(start_time+tm, end_time+tm- interval '1 sec', interval '15 min') gdtm ;

The outlier
As mentioned there is one scenario where a time data type does not work satisfactory, but you may not nee it. What happens when a shift worker says they available time is 23:00-01:30. Believe me when a shift worker goes to work at 22:00 of Friday, 01:30 is still Friday night, even though the calendar might not agree. (I worked that shift for many years.) The following using interval handles that issue. Loading the same data as prior with an addition for the this case.

 insert into availabilities (weekday, start_time, end_time )
    select wkday
         , start_time
         , end_time + case when end_time < start_time
                           then interval '1 day'
                           else interval '0 day'
                      end 
      from (select * 
              from (values (1, '09:00'::interval, '11:00'::interval) 
                         , (1, '13:00'::interval, '17:00'::interval)
                         , (2, '08:00'::interval, '17:00'::interval) 
                         , (3, '08:30'::interval, '10:45'::interval)
                         , (5, '23:30'::interval, '02:30'::interval)  -- Friday Night - Saturday Morning
                   ) as v(wkday,start_time,end_time)
           ) r
           ;           
select * from availabilities; 

Hope this helps.