postgreSQL如何根据一些条件为约会生成时间表?

Question

我正在创建一个项目，为理发店的顾客安排时间。

提供的服务例如:

name              service_time
______________________________
| Haircut         | 00:30:00 |
| Beard           | 00:15:00 |
| Eyebrow         | 00:45:00 |
------------------------------

我在Postgres数据库中创建了一个函数，用来生成我可以显示给客户端的时间。

CREATE OR REPLACE FUNCTION free_hours(start_work TIME, start_launch_time TIME, stop_launch_time TIME, stop_work TIME)
RETURNS TABLE(step INTERVAL, free_hours TIMESTAMP WITHOUT TIME ZONE)
AS $$
SELECT step::INTERVAL, free_hours FROM
UNNEST(ARRAY ['15 min', '30 min', '45 min', '60 min']) AS t(step)
CROSS JOIN LATERAL
GENERATE_SERIES(
'2021-10-20'::date + start_work,
'2021-10-20'::date + stop_work + INTERVAL '1 day',
step::INTERVAL
) AS free_hours
WHERE free_hours::TIME NOT BETWEEN start_launch_time AND stop_launch_time
AND free_hours::TIME >= start_work AND free_hours::TIME <= stop_work
AND EXTRACT(dow FROM free_hours) <> 0 -- Sunday
AND free_hours NOT IN (
SELECT GENERATE_SERIES(
a.created_at::TIMESTAMP - (step::INTERVAL - '1 min'::INTERVAL)::TIME,
a.created_at,
INTERVAL '1 min'
) as hours_to_remove
FROM appointments a
INNER JOIN services s ON a.service_id = s.id
)
AND free_hours NOT IN (
SELECT GENERATE_SERIES(
a.created_at,
a.created_at::TIMESTAMP + s.service_time::TIME - '00:01:00'::TIME,
INTERVAL '1 min'
) as hours_to_remove
FROM appointments a
INNER JOIN services s ON a.service_id = s.id
)
$$
LANGUAGE SQL
IMMUTABLE

这个函数，它根据一些规则生成可用的调度时间，例如:

• 只显示工作日的开始和结束时间
• 不列出理发师在午餐时间的时间
• 不列出周日的时间

由这个函数完成的另一个验证，它是基于客户选择的时间，它负责生成2个新的序列，这有助于避免出现可用时间的风险，当理发师与某个客户在一起时，也就是说，他保证所选择的时间不会受到其他时间的干扰。

---

这个函数正在做需要做的事情，但是我可能犯了一个错误，在这个函数中，我生成了所有存在的服务时间的时间表:15min,30分钟45分钟,和60 min，这些时间依次生成，例如:15min, 15min，等等。

但是发生在我身上的问题，我无法解决它，当以下情况发生时，例如:

我预订了理发在09:30，此剪辑的持续时间为30min，以10:00h结尾举例来说，那时候我就可以安排一个。眉毛，持续45min，但是我这样做的方式，因为在45分钟的情况下，时间表从45分钟到45分钟，将会发生的问题是，上午10点的时间将无法让我标记，这个例子将只在10:30可用

有人能帮帮我吗?如果不应用这些规则，我真的想不出另一种方法来制作这个列表。

创建表和函数的链接，插入和选择:https://www.db-fiddle.com/f/wMK5BP1oXZPdj4Bp1CxWLp/0

Answer 1

我建议:

1. 不创建多个步骤，而只创建最不常见的步骤，例如15分钟
2. 使用数据类型tstzrange处理时间戳范围，并通过使用特定的函数计算范围的差异，例如:

CREATE OR REPLACE FUNCTION range_diff (x anyrange, y anyrange)
RETURNS setof anyrange LANGUAGE plpgsql AS
$$
BEGIN
CASE
WHEN x @> y
THEN RETURN QUERY SELECT tstzrange(lower(x), lower(y)) UNION SELECT tstzrange(upper(y), upper(x)) ;
ELSE RETURN QUERY SELECT x - y ;
END CASE ;
END ;
$$

3. 将每日计划管理为一个范围，例如

tstzrange('20211101 090000','20211101 170000')

2021年11月1日上午9:00至下午5:00

4. 根据所选服务的开始时间和持续时间计算预约表中的预订时段，例如

tstzrange('20211101 094500' :: timestamp with time zone,'20211101 094500' :: timestamp with time zone + INTERVAL '45 MINUTES')

从2021年11月1日上午9点45分开始

5. 从该时间表中删除已预订的时间段，例如

range_diff (tstzrange('20211101 090000','20211101 170000'),tstzrange('20211101 094500','20211101 103000'))

，以便计算在上午9:45预订眉毛服务后可用的自由时间范围。结果是:

["2021-11-01 09:00:00+01","2021-11-01 09:45:00+01")
["2021-11-01 10:30:00+01","2021-11-01 17:00:00+01")

6. 当您创建一个新的约会时，您可以检查它是否可以插入到日程安排中，即它包含在一个自由范围