This is a real-world solution I created for a problem I encountered in development of an appointment-booking platform. I had to get a dynamic date-range (let’s say “next 30 days” for this example) and pull availability based both on office hours and scheduled appointments. I left office hours out of this example for simplicity.
The solution I came up with was to develop a dynamic view based on curdate() functions that would pull a dataset of the next 30 days as dates, and join that to my availability and appointments to pull available time slots for a given business.
Here’s a simplified example of how to accomplish this:
Assume the following table of appointments:
CREATE TABLE appointment (
id INT NOT NULL AUTO_INCREMENT,
start_date DATETIME NOT NULL,
end_date DATETIME NOT NULL,
created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (id),
INDEX (start_date)
);
Let’s populate it with some data:
INSERT INTO appointment (start_date, end_date)
VALUES
(CONCAT(curdate(), ' 09:00:00'), CONCAT(curdate(), ' 10:00:00')),
(CONCAT(curdate() + INTERVAL 1 DAY, ' 09:00:00'), CONCAT(curdate() + INTERVAL 1 DAY, ' 10:00:00')),
(CONCAT(curdate() + INTERVAL 1 DAY, ' 11:00:00'), CONCAT(curdate() + INTERVAL 1 DAY, ' 12:00:00')),
(CONCAT(curdate() + INTERVAL 15 DAY, ' 13:00:00'), CONCAT(curdate() + INTERVAL 15 DAY, ' 14:00:00')),
(CONCAT(curdate() + INTERVAL 18 DAY, ' 19:00:00'), CONCAT(curdate() + INTERVAL 18 DAY, ' 20:00:00'));
Let’s say we want to get a view of the next 30 days’ appointments grouped by date, and without missing dates for days with no appointments scheduled.
First, we can create a dynamic view of the next 30 days, which will auto-update its contents every day:
CREATE OR REPLACE VIEW `next30days` AS
SELECT curdate() AS `day`
UNION ALL SELECT (curdate() + INTERVAL 1 DAY)
UNION ALL SELECT (curdate() + INTERVAL 2 DAY)
UNION ALL SELECT (curdate() + INTERVAL 3 DAY)
UNION ALL SELECT (curdate() + INTERVAL 4 DAY)
UNION ALL SELECT (curdate() + INTERVAL 5 DAY)
UNION ALL SELECT (curdate() + INTERVAL 6 DAY)
UNION ALL SELECT (curdate() + INTERVAL 7 DAY)
UNION ALL SELECT (curdate() + INTERVAL 8 DAY)
UNION ALL SELECT (curdate() + INTERVAL 9 DAY)
UNION ALL SELECT (curdate() + INTERVAL 10 DAY)
UNION ALL SELECT (curdate() + INTERVAL 11 DAY)
UNION ALL SELECT (curdate() + INTERVAL 12 DAY)
UNION ALL SELECT (curdate() + INTERVAL 13 DAY)
UNION ALL SELECT (curdate() + INTERVAL 14 DAY)
UNION ALL SELECT (curdate() + INTERVAL 15 DAY)
UNION ALL SELECT (curdate() + INTERVAL 16 DAY)
UNION ALL SELECT (curdate() + INTERVAL 17 DAY)
UNION ALL SELECT (curdate() + INTERVAL 18 DAY)
UNION ALL SELECT (curdate() + INTERVAL 19 DAY)
UNION ALL SELECT (curdate() + INTERVAL 20 DAY)
UNION ALL SELECT (curdate() + INTERVAL 21 DAY)
UNION ALL SELECT (curdate() + INTERVAL 22 DAY)
UNION ALL SELECT (curdate() + INTERVAL 23 DAY)
UNION ALL SELECT (curdate() + INTERVAL 24 DAY)
UNION ALL SELECT (curdate() + INTERVAL 25 DAY)
UNION ALL SELECT (curdate() + INTERVAL 26 DAY)
UNION ALL SELECT (curdate() + INTERVAL 27 DAY)
UNION ALL SELECT (curdate() + INTERVAL 28 DAY)
UNION ALL SELECT (curdate() + INTERVAL 29 DAY)
UNION ALL SELECT (curdate() + INTERVAL 30 DAY);
Then, we can left-join our view on the appointments, grouping by day and concatenating the appointments into a simple result.
SELECT n.day, GROUP_CONCAT(CONCAT(TIME(start_date), '-', TIME(end_date))) AS appointments
FROM next30days n
LEFT JOIN appointment AS a ON n.day = DATE(a.start_date)
GROUP BY n.day;
And our results will look like this:
+------------+-------------------------------------+
| day | appointments |
+------------+-------------------------------------+
| 2011-03-03 | 09:00:00-10:00:00 |
| 2011-03-04 | 09:00:00-10:00:00,11:00:00-12:00:00 |
| 2011-03-05 | NULL |
| 2011-03-06 | NULL |
| 2011-03-07 | NULL |
| 2011-03-08 | NULL |
| 2011-03-09 | NULL |
| 2011-03-10 | NULL |
| 2011-03-11 | NULL |
| 2011-03-12 | NULL |
| 2011-03-13 | NULL |
| 2011-03-14 | NULL |
| 2011-03-15 | NULL |
| 2011-03-16 | NULL |
| 2011-03-17 | NULL |
| 2011-03-18 | 13:00:00-14:00:00 |
| 2011-03-19 | NULL |
| 2011-03-20 | NULL |
| 2011-03-21 | 19:00:00-20:00:00 |
| 2011-03-22 | NULL |
| 2011-03-23 | NULL |
| 2011-03-24 | NULL |
| 2011-03-25 | NULL |
| 2011-03-26 | NULL |
| 2011-03-27 | NULL |
| 2011-03-28 | NULL |
| 2011-03-29 | NULL |
| 2011-03-30 | NULL |
| 2011-03-31 | NULL |
| 2011-04-01 | NULL |
| 2011-04-02 | NULL |
+------------+-------------------------------------+
