查询是否营业

Question

我有一个Postgres表，看起来像这样:

business_id (PK) int4 业务ID int4星期几(0-6，星期一为零)开放时间关闭时间

Answer 1

我将使用范围类型和约束来避免冲突。像这样:

CREATE EXTENSION btree_gist; -- for the constraints
CREATE TYPE timerange AS RANGE (
subtype = time
);
CREATE TABLE business_hours(
business_id INT
, day INT
, timerange timerange
,     EXCLUDE USING GIST (business_id WITH =, day WITH =, timerange WITH &&) -- constraint
);
INSERT INTO business_hours VALUES
(1,0,'[18:00,23:00)'::timerange),
(1,1,'[18:00,23:00)'::timerange),
(1,2,'[18:00,23:00)'::timerange),
(1,3,'[18:00,23:00)'::timerange),
(1,4,'[18:00,24:00)'::timerange),
(1,5,'[00:00,01:00)'::timerange),
(1,5,'[18:00,24:00)'::timerange),
(1,6,'[00:00,02:00)'::timerange);
SELECT  COUNT(*)
FROM    business_hours
WHERE   business_id = 1
AND (
(day = EXTRACT(DOW FROM CURRENT_DATE)::INT -1 AND   timerange @> LOCALTIME) -- this is now
OR 
(day = EXTRACT(DOW FROM '2022-11-12'::date) - 1 AND timerange @> '23:30'::time) -- some other day and or time
);

Answer 2

我最终想出了这个查询，并将其包装在一个函数中，以便于使用:

CREATE FUNCTION fn_business_is_open
(
business_id int4
,at timestamptz
)
RETURNS bool
LANGUAGE sql
AS
$$
SELECT COUNT(*) > 0
FROM
(
SELECT *
FROM business_hours
WHERE
day = EXTRACT(dow from $2) - 1
AND
(
("from" < "to" AND $2::time >= "from" AND $2::time <= "to")
OR
("from" > "to" AND $2::time >= "from" AND $2::time >= "to")
)
UNION ALL
SELECT *
FROM business_hours
WHERE
day = EXTRACT(dow from $2) - 2
AND "from" > "to" AND $2::time <= "to"
) a
WHERE
business_id = $1;
$$;

我不能100%肯定这个查询是否正确。我的测试表明，当我在一周内每小时调用这个函数时，它产生了预期的结果。

如果你认为可以用更好/更有效的方法来做，请考虑评论。