时间戳

首页 > 解决方案 > 在 MySQL 中查找可用的预约空档

mysql - 在 MySQL 中查找可用的预约空档

问题描述

我需要为具有以下要求的预约系统找到可用的时间段：

我只是无法理解它。任何帮助是极大的赞赏！

我的表格如下所示。最后一个表是我希望从我正在寻找的查询中收到的输出。

SQL小提琴

MySQL 5.6 架构设置：

CREATE TABLE calendar (
  id INT
);
INSERT INTO calendar(id) VALUES(1);

CREATE TABLE appointments (
  id INT,
  calendarid INT,
  startdatetime DATETIME,
  duration TIME
);
INSERT INTO appointments(id,calendarid,startdatetime,duration) VALUES
(1,1,"2020-01-01 09:30:00", "01:00:00"),
(2,1,"2020-01-01 10:00:00", "01:00:00"),
(3,1,"2020-01-01 14:00:00", "00:30:00"),
(4,1,"2020-01-02 08:00:00", "02:00:00"),
(5,1,"2020-01-02 13:00:00", "02:00:00");

CREATE TABLE availability(
  id INT,
  calendarid INT,
  weekday INT,     -- 1 = monday, 7 = sunday
  starttime TIME,
  endtime TIME,
  capacity INT
);
INSERT INTO availability(id,calendarid,weekday,starttime,endtime,capacity) VALUES
(1,1,1,"08:00:00","12:00:00",2),
(2,1,1,"13:00:00","17:00:00",2),
(3,1,2,"08:00:00","12:00:00",2),
(4,1,2,"13:00:00","17:00:00",2),
(5,1,3,"08:00:00","12:00:00",2),
(6,1,3,"13:00:00","17:00:00",2),
(7,1,4,"08:00:00","12:00:00",1),
(8,1,4,"13:00:00","15:00:00",1),
(9,1,5,"08:00:00","12:00:00",2),
(10,1,5,"13:00:00","15:00:00",2);

CREATE TABLE desired_output (
  slot DATETIME
);
-- 2020-01-01 => weekday = 3, capacity = 2, available 08:00:00-12:00:00 and 13:00:00-17:00:00
-- 2020-01-02 => weekday = 4, capacity = 1, available 08:00:00-12:00:00 and 13:00:00-15:00:00
INSERT INTO desired_output(slot) VALUES
("2020-01-01 08:00:00"), -- last possible because at 10:00 there is no capacity left
("2020-01-01 13:00:00"),
("2020-01-01 13:15:00"),
("2020-01-01 13:30:00"),
("2020-01-01 13:45:00"),
("2020-01-01 14:00:00"),
("2020-01-01 14:15:00"),
("2020-01-01 14:30:00"),
("2020-01-01 14:45:00"),
("2020-01-01 15:00:00"),
("2020-01-02 10:00:00");

查询 1：

SELECT * FROM calendar

结果：

| id |
|----|
|  1 |

查询 2：

SELECT * FROM appointments

结果：

| id | calendarid |        startdatetime | duration |
|----|------------|----------------------|----------|
|  1 |          1 | 2020-01-01T09:30:00Z | 01:00:00 |
|  2 |          1 | 2020-01-01T10:00:00Z | 01:00:00 |
|  3 |          1 | 2020-01-01T14:00:00Z | 00:30:00 |
|  4 |          1 | 2020-01-02T08:00:00Z | 02:00:00 |
|  5 |          1 | 2020-01-02T13:00:00Z | 02:00:00 |

查询 3：

SELECT * FROM availability

结果：

| id | calendarid | weekday | starttime |  endtime | capacity |
|----|------------|---------|-----------|----------|----------|
|  1 |          1 |       1 |  08:00:00 | 12:00:00 |        2 |
|  2 |          1 |       1 |  13:00:00 | 17:00:00 |        2 |
|  3 |          1 |       2 |  08:00:00 | 12:00:00 |        2 |
|  4 |          1 |       2 |  13:00:00 | 17:00:00 |        2 |
|  5 |          1 |       3 |  08:00:00 | 12:00:00 |        2 |
|  6 |          1 |       3 |  13:00:00 | 17:00:00 |        2 |
|  7 |          1 |       4 |  08:00:00 | 12:00:00 |        1 |
|  8 |          1 |       4 |  13:00:00 | 15:00:00 |        1 |
|  9 |          1 |       5 |  08:00:00 | 12:00:00 |        2 |
| 10 |          1 |       5 |  13:00:00 | 15:00:00 |        2 |

问题 4：

-- desired parameters
SET 
@search_window_start = "2020-01-01 08:00:00",
@search_window_end = "2020-01-02 18:00:00",
@appointment_duration = "02:00:00",
@slot_size = "00:15:00"

结果：

问题 5：

SELECT * FROM desired_output

结果：

|                 slot |
|----------------------|
| 2020-01-01T08:00:00Z |
| 2020-01-01T13:00:00Z |
| 2020-01-01T13:15:00Z |
| 2020-01-01T13:30:00Z |
| 2020-01-01T13:45:00Z |
| 2020-01-01T14:00:00Z |
| 2020-01-01T14:15:00Z |
| 2020-01-01T14:30:00Z |
| 2020-01-01T14:45:00Z |
| 2020-01-01T15:00:00Z |
| 2020-01-02T10:00:00Z |

标签： mysql

解决方案

推荐阅读