MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select @@version;


@@version


8.0.36


CREATE TABLE shifts (
    id int NOT NULL,
    unique_id varchar(255) NOT NULL,
    start_date DATETIME NOT NULL,
    end_date DATETIME NOT NULL,
    PRIMARY KEY (id)
);

CREATE TABLE appointments (
    id int NOT NULL,
    unique_id varchar(255) NOT NULL,
    start_date DATETIME NOT NULL,
    end_date DATETIME NOT NULL,
    shift_id int NOT NULL,
    PRIMARY KEY (id),
    FOREIGN KEY (shift_id) REFERENCES shifts(id)
);

INSERT INTO `shifts` (`id`, `unique_id`, `start_date`, `end_date`)
VALUES
	('4600', '614fc0c5dab2485bba8cbbd9eb98aa53', '2022-03-15 10:00:00', '2022-03-15 17:00:00');


INSERT INTO `appointments` (`id`, `unique_id`, `start_date`, `end_date`, `shift_id`)
VALUES
	('1', '17875012cad44c3fb890ac12ea9840dc', '2022-03-15 10:30:00', '2022-03-15 11:30:00', 4600),
	('2', 'e5b7c274f9d444a6b7560c8e3230aab2', '2022-03-15 11:30:00', '2022-03-15 12:15:00', 4600),
	('3', 'abc3e702f77b423d8bac6250a5be7408', '2022-03-15 15:00:00', '2022-03-15 15:40:00', 4600);

Records: 3  Duplicates: 0  Warnings: 0


WITH shift (shift_start, shift_end) AS (
       SELECT '2022-03-15 10:00:00', '2022-03-15 17:00:00'
     )
   , e0 AS (
       SELECT shift_id, start_date, end_date
         FROM appointments
        WHERE shift_id = 4600
          AND end_date   <= (SELECT shift_end   FROM shift)
          AND start_date >= (SELECT shift_start FROM shift)
        UNION ALL
       SELECT 4600, shift_end, shift_end FROM shift
        ORDER BY start_date 
     )
   , e AS (
      SELECT e0.*
           , LAG(end_date) OVER (PARTITION BY shift_id ORDER BY start_date) AS lasttime_to
        FROM e0
     )
SELECT shift_id, Available_from, Available_to
  FROM (
       SELECT shift_id
            , CAST(COALESCE(lasttime_to, shift_start) AS datetime) AS Available_from
            , start_date AS Available_to
         FROM e
         CROSS JOIN shift
      ) x
 WHERE Available_to > DATE_ADD(Available_from, INTERVAL 20 MINUTE)
;


shift_id
Available_from
Available_to


4600
2022-03-15 10:00:00
2022-03-15 10:30:00

4600
2022-03-15 12:15:00
2022-03-15 15:00:00

4600
2022-03-15 15:40:00
2022-03-15 17:00:00


WITH e0 AS (
       SELECT a.shift_id, a.start_date, a.end_date
            , s.start_date AS shift_start
            , s.end_date   AS shift_end
         FROM appointments AS a
         JOIN shifts       AS s
           ON a.shift_id = 4600
          AND a.shift_id = s.id
          AND a.end_date   <= s.end_date
          AND a.start_date >= s.start_date
        UNION
       SELECT id, end_date, end_date
            , start_date  , end_date
         FROM shifts WHERE id = 4600
     )
   , e AS (
      SELECT e0.*
           , LAG(end_date) OVER (PARTITION BY shift_id ORDER BY start_date) AS lasttime_to
        FROM e0
     )
SELECT shift_id, Available_from, Available_to
  FROM (
       SELECT shift_id
            , CAST(COALESCE(lasttime_to, shift_start) AS datetime) AS Available_from
            , start_date AS Available_to
         FROM e
      ) x
 WHERE Available_to > DATE_ADD(Available_from, INTERVAL 20 MINUTE)
;


shift_id
Available_from
Available_to


4600
2022-03-15 10:00:00
2022-03-15 10:30:00

4600
2022-03-15 12:15:00
2022-03-15 15:00:00

4600
2022-03-15 15:40:00
2022-03-15 17:00:00
shift_id	Available_from	Available_to
4600	2022-03-15 10:00:00	2022-03-15 10:30:00
4600	2022-03-15 12:15:00	2022-03-15 15:00:00
4600	2022-03-15 15:40:00	2022-03-15 17:00:00