Storing Open Hours/Holidays

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 m开发者_如何学JAVAy 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.

---

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;

---

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.

---

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.