postgresql storing opening hours spanning midnight

Question

I have an app in which I need to display opening hours. It's mostly for pubs, so they will be open (once corona goes away) after midnight.

CREATE TABLE openinghours (
  id uuid NOT NULL,
  day smallint NOT NULL,
  starttime time NOT NULL,
  endtime time NOT NULL
);

Currently, when the pub owner enters that the pub is open on friday from 08PM till 04AM, I store this in the database as 2 different timestamp:

// friday = 5
// saturday = 6
INSERT INTO openinghours (day, starttime, endtime) VALUES(5, 2000,2359);
INSERT INTO openinghours (day, starttime, endtime) VALUES(6, 0000,0400);

I'm not sure this is the right solution though. I'm looking for a solution to store this as one line in the database, something like this:

// friday = 5
INSERT INTO openinghours (day, starttime, endtime) VALUES(5, 2000, 0400);

I'm storing times without time zone but in local time. So when the pub is open from 08PM till 04AM in London, I store it as 2000/0400 (with a reference to the timezone it's stored in, in this case Europe/London).

With the following query, I can check if the current time is between a specific starttime and endtime.

SELECT
    to_char(CURRENT_TIMESTAMP, 'HH24MI') as currenttime,
    to_char(starttime, 'HH24MI') as starttime,
    to_char(endtime, 'HH24MI') as endtime
FROM
    data.test_opening_hours
WHERE
    timezone('Europe/London', CURRENT_TIME)::time BETWEEN starttime AND endtime

Explanation: I get the current_timestamp, the starttime and the endtime (in military time notition) from the rows where the current time (in time zone Europe/London) is between starttime and endtime. Europe/London is the timezone in which the start/endtime are stored. So if the local time in London (timezone('Europe/London', CURRENT_TIME)::time) is between the start/endtime in the row.

The problem I get, is when storing start-endtime intervals that span over midnight.

EXAMPLE: current time NOW in London is 10:20PM. Let's say I have this row in the database:

ID     | day      |  starttime    | endtime
-----------------------------------------------
1      | 3        | 20:00:00      | 04:00:00

This row is not fetched, because for this row, the current time is not between the start and end time.

QUESTION

How can I store time intervals spanning midnight in the database (postgresql) and query against them with the current time?

EDIT: sample data set

CREATE TABLE openinghours (
  id bigint NOT NULL,
  day smallint NOT NULL,
  starttime time NOT NULL,
  endtime time NOT NULL
);
ALTER TABLE openinghours ADD CONSTRAINT pk PRIMARY KEY (id);

INSERT INTO openinghours (id, day, starttime, endtime) VALUES 
(1, 1, '2000', '0200'),
(2, 4, '1200', '1800'),
(3, 5, '1200', '1800'),
(4, 6, '1800', '0400'),
(5, 7, '1800', '0400');

This example dataset is for a pub thats open on:

• wednesday 12PM - 06PM
• thursday 12PM - 06PM
• friday 06PM - 04AM
• saturday 06PM - 04AM
• sunday 08PM - 02AM

EDIT 2: POSSIBLE SOLUTION

I've found this possible solution. It seems that it works (for now).

SELECT
    *
FROM
    openinghours
WHERE
    CASE
        WHEN starttime < endtime THEN
            day = to_char(timezone('Europe/London', CURRENT_TIMESTAMP), 'D')::int
            AND
            timezone('Europe/London', CURRENT_TIME)::time BETWEEN starttime AND endtime
        WHEN starttime >= endtime THEN
            (
                day = to_char(timezone('Europe/London', CURRENT_TIMESTAMP), 'D')::int
                AND
                timezone('Europe/London', CURRENT_TIME)::time >= starttime
            )
            OR
            (
                day = to_char(timezone('Europe/London', CURRENT_TIMESTAMP), 'D')::int-1
                AND
                timezone('Europe/London', CURRENT_TIME)::time <= endtime
            )
        END

Explanation: With the CASE statement, I check if the starttime is greater than the endtime or not. If the start time < end time, it's on the same day. If starttime > endtime, the end time is on the next day.

If starttime < endtime: just check if the day is correct (the current day number in local time) and check if the current time (local) is between the starttime and endtime).

If starttime >= endtime: check if the current day number (local) is correct and if the current time (local) is greater than the starttime OR check if the current day (local) minus 1 (because I need to check for previous days) is lower than the endtime.

I have done a lot of testing and it seems to work. Even if I replace the timezones.

Thank you everybody for your help. Have a nice christmas and newyear!

Answer 1

Here is a possible solution:
Store starttime as time and the duration as interval in the openinghours table. The inserted values also change.

CREATE TABLE openinghours (
  id uuid NOT NULL,
  day smallint NOT NULL,
  starttime time NOT NULL,
  duration interval NOT NULL
);

INSERT INTO openinghours (day, starttime, duration)
VALUES
 (3, '12:00', interval '6 hours'),
 (4, '12:00', interval '6 hours'),
 (5, '18:00', interval '10 hours'),
 (6, '18:00', interval '10 hours'),
 (7, '20:00', interval '6 hours');

Then the check whether the pub is open now may look like this. Spanning midnight does not matter anymore.

select
    to_char(current_timestamp, 'HH24MI') as currenttime,
    to_char(starttime, 'HH24MI') as starttime,
    to_char(starttime + duration, 'HH24MI') as endtime
from openinghours     
where current_timestamp between current_date+starttime and current_date+starttime+duration
and extract (ISODOW from case when current_time < '04:00' then current_date - 1 else current_date end) = day;
-- if it's very early in the morning then look at the opening hours of the previous day

You can of course modify the query to use a particular timestamp instead of current_timestamp and cast it to date to use instead of current_date.

NB
date + time yields timestamp

Answer 2

The below code is a translation to Postgresql of another answer of mine to a similar problem for MySql.
The trick is to split each row where starttime > endtime in 2 rows:

WITH cte AS (
  SELECT *, day % 7 daynr, starttime time1, endtime time2 FROM openinghours
  WHERE starttime < endtime 
  UNION ALL
  SELECT *, day % 7, starttime, '235959' FROM openinghours
  WHERE starttime > endtime 
  UNION ALL
  SELECT *, (day + 1) % 7, '000000', endtime 
  FROM openinghours
  WHERE starttime > endtime
)  
SELECT id, day, starttime, endtime 
FROM cte
WHERE daynr = EXTRACT(DOW FROM NOW())
  AND NOW() BETWEEN CURRENT_DATE + time1 AND CURRENT_DATE + time2

See the demo.

Answer 3

A first draft. Finds that Tuesday is only day pub is not open.

WITH 
    days AS 
    (SELECT 
        day, CASE WHEN starttime < endtime THEN day ELSE day  + 1  END AS end_day  
    FROM 
        openinghours)
SELECT 
    distinct on(day, end_day) day, end_day, d 
FROM 
    days 
RIGHT JOIN 
    (SELECT 
        generate_series(1, 8)) AS 
    day_no(d) 
ON 
    d <@ int4range(day, end_day, '[]') 
WHERE 
    day is null 
ORDER BY 
    day;

day  | end_day | d 
------+---------+---
 NULL |    NULL | 3