Storing Open Hours/Holidays

Question

What kind of SQL schema would you suggest for storing hours and holidays, and what kind of query to check if a restaurant is open? Right now I have this:

CREATE TABLE hours (
    "restaurant" integer NOT NULL REFERENCES restaurants ON DELETE CASCADE,
    "dow" integer NOT NULL,
    "open" time NOT NULL,
    "close" time NOT NULL
);
CREATE FUNCTION is_open(r integer) RETURNS boolean AS $$
    DECLARE
        h record;
        t time;
    BEGIN
        SELECT open, close INTO h
        FROM hours WHERE restaurant = r AND dow = EXTRACT(dow FROM now());
        IF NOT FOUND THEN
            RETURN false;
        END IF;
        t := current_time;
        IF h.close <= h.open THEN
            RETURN (t < h.close OR t > h.open);
        ELSE
            RETURN (t > h.open AND t < h.close); 
        END IF;
    END;
$$ LANGUAGE plpgsql;

But that doesn't really work, because, for instance, a restaurant might be open until 2:00 am, at which point I would need to check for the previous dow.

To make matters a little more complicated, I have to deal with holidays:

CREATE TABLE holidays (
    "restaurant" integer NOT NULL REFERENCES restauraunts ON DELETE CASCADE,
    "day" date NOT NULL
);

Which has the same problem - if a restaurant is open from 15:30 to 2:00, that means they are also closed for the block from midnight to two.

I haven't been able to wrap my head around finding a clean, elegant solution for this (several messy ones have come and gone). I need to go take a walk and come back to it - in the mean time, I figured I would let you guys take a crack at it.

**Duplicate question from yesterday:** http://stackoverflow.com/questions/4464898/best-way-to-store-working-hours-and-query-it-efficiently — JNK, Dec 17 '10 at 20:29
doesn't answer my question, which is how to deal with shops that are open past midnight — colinmarc, Dec 17 '10 at 20:40
Add a bit field `ClosePastMidnight`, if it's set to `1` add a day. — JNK, Dec 17 '10 at 20:51
+1 to JNK's suggestion; an alternative would be to check whether or not `close` is before `open` (if so, then the shop is open past midnight). — Matt Ball, Dec 17 '10 at 20:58
I don't need to use a bit flag - I already check for `h.close <= h.open` in the question. The problem is that I'm coming from the direction of using the *current* `dow`, so it gets really messy really quickly. I also thought about storing just opening time and the length of time from that point, but that didn't work either. — colinmarc, Dec 17 '10 at 21:31

score 1 · Answer 1 · answered Dec 17 '10 at 22:44

Seems kind of wrong to answer my own question, but I've found something that seems to work, as messy as it is:

CREATE FUNCTION is_open(r integer) RETURNS boolean AS $$
    DECLARE
        t time;
        yesterday date;
        dow_today integer;
        dow_yesterday integer;
    BEGIN
        t := current_time;
        yesterday := current_date - 1;
        dow_today := EXTRACT(dow FROM current_date);
        dow_yesterday := EXTRACT(dow FROM yesterday);
        PERFORM * FROM hours
        WHERE restaurant = r AND ((
            dow = dow_today
            AND NOT EXISTS(
                SELECT * FROM holidays 
                    WHERE restaurant = r AND day = current_date
            ) AND (
                (open < close AND t > open AND t < close)
                OR (open >= close AND t > open)
            )
        ) OR (
            open >= close AND dow = dow_yesterday
            AND NOT EXISTS(
                SELECT * FROM holidays 
                WHERE restaurant = r AND day = yesterday
            ) AND t < close
        ));   
        RETURN FOUND;
    END;
$$ LANGUAGE plpgsql;

score 0 · Answer 2 · edited May 23 '17 at 12:04

0

To sum up the comments:

1 - Use the general query structure from this question.

2 - Add a bit flag to your table for ClosePastMidnight or OpenPastMidnight (whichever works best for your way of thinking) that indicates close is on next calendar day, and adjust your logic accordingly.

edited May 23 '17 at 12:04

Community

1
1

answered Dec 17 '10 at 21:07

JNK

63,321
15
122
138

I appreciate the help, but please see my comment above – colinmarc Dec 17 '10 at 21:32

nate c · Answer 3 · 2010-12-17T22:55:42.410

0

Your table design has missing information that becomes apparent when you try to disambiguate what close and opening means. Such as 12 to 2. Does that mean 2 hours or 14? Without making assumptions there is no way to solve it.

Use intervals:

For example if your restaurant is from 1:30PM to 1:30AM this could check if now() is inbetween:

test=# select  
  now() > (now()::date || ' 13:30')::timestamp 
  and now() < (now()::date || ' 13:30')::timestamp + interval '12 hours';

 ?column?
----------
 t
(1 row)

That way only the starting dow is important and you should not have any wraparound problems.

edited Dec 17 '10 at 22:55

answered Dec 17 '10 at 22:22

nate c

8,802
2
27
28

what notation is that? I've never seen `now()::date` before – colinmarc Dec 17 '10 at 22:38
also, this still suffers from the problem that current_date or now() is not going to match the `dow` column in the `hours` table. – colinmarc Dec 17 '10 at 22:42
Just check it twice. In my example your constructing proper dates. Pick the dow. Decriment it back one. Can you construct an interval with now() in it? Check it again with current day of week. – nate c Dec 17 '10 at 22:52
12:00 to 2:00? that could only mean 14 hours, unless you meant 12:00 to 14:00... There's no ambiguity because the times are in 24-hour format. – colinmarc Dec 17 '10 at 22:58

Storing Open Hours/Holidays

3 Answers3