postgresql 查询业务是否开放

xn1cxnb4 于 2022-11-23 发布在 PostgreSQL

关注(0)|答案(2)|浏览(85)

我有一个Postgres表，如下所示：
| 名称名称名称|型号|项目名称|
| - -|- -|- -|
| 业务标识（主键）|整数4|业务ID|
| 天（PK）|整数4|星期几（0-6，星期一为零）|
| 打开的|计时器|开放时间|
| 关闭|计时器|关闭时间|
每一行都存储一家企业在一周中特定一天的营业时间和营业结束时间。企业的示例数据如下所示：
| 业务标识|日间|打开的|关闭|
| - -|- -|- -|- -|
| 一个|第0页|晚上18时|晚上10点|
| 一个|一个|晚上18时|晚上10点|
| 一个|2个|晚上18时|晚上10点|
| 一个|三个|晚上18时|晚上10点|
| 一个|四个|晚上18时|上午1时|
| 一个|五个|晚上18时|下午2点|
您可以看到，营业时间为18：00至23：00，从Mo.-Fr.开始。请注意，在周末，营业时间将延长至第二天。
我正在尝试编写一个单语句查询，用于确定某个企业是现在开业还是在特定时间开业。
我试着编写下面的查询，但结果是错误的，我想不出其他方法来解决这个问题。

select count(*)
from (
select *
from business_hours bh
where 
    bh.business_id = 1
    and bh.day =  extract(dow from now()) - 1
union all
select *
from business_hours bh
where 
    bh.business_id = 1
    and bh.day = extract(dow from now()) - 1
) a
where
    ("from" < "to" and now()::time between "from" and "to")
    or ("from" > "to" and now()::time not between "to" and "from")

感谢您帮助我解决此问题

postgresql

来源：https://stackoverflow.com/questions/74396374/query-if-business-is-open

2条答案

按热度按时间

7lrncoxx1#

我会使用一个范围类型和一个约束条件来避免冲突。

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
);

赞(0）回复(0）举报 2022-11-23

5lhxktic2#

最后，我提出了这个查询，并将它封装在一个函数中，以便更容易使用：

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%确定这个查询是否正确。我的测试表明，当我在一周内每小时调用这个函数时，它产生了预期的结果。
如果你认为可以用更好/更有效的方式完成，请考虑评论。

赞(0）回复(0）举报 2022-11-23

我来回答

postgresql 查询业务是否开放

2条答案

相关问题

热门标签

最新问答