I have a Postgres table that looks like this:

Every row stores open and closing times for a business on a specific day of week. Example data for a business looks like this:

You can see that the business is opened from 18:00 to 23:00 from Mo.-Fr. Note that on the weekend the opening hours extend over to the next day.

I'm trying to write a single statement query that determines if a business is opened now or at a specific time.

I tried writing the query below but the results are wrong and I can't think of another way to solve this problem.

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

Thank you for helping me out with this query

CodePudding user response：

I would use a range type and a constraint to avoid conflicts. Something like this:

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

CodePudding user response：

I finally came up with this query and wrapped it inside a function for easier usage:

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;

$$;

I'm not 100% sure if this query is correct. My tests showed that it produces the expected result when I called this function with every hour in a week.

Please consider commenting if you think it could be done in a better/more efficient way.