Javascript Question posted in
A very good W3school tutorial can be found here.

Javascript – Should I save the opening hours as string or as a date time?

I am using Xata as my database and it has a datetime column type which I am using at the moment for the opening hours of a restaurant. But it is not always convenient when I use it only for the hours and minutes. This gives problems when sorting because I always have to extract the time from the date and I also have time zone issues I have to deal with. What is the best practice in this situation? Continue using the datetime data type or switch to string? I am displaying the opening hours in the following format "hh:mm" for each day.

Tags:

3

Answers


  1. 0

    You probably don’t want to use datetime for this use-case, because it represents a time on a specific day, whereas your opening times are recurring every week.

    If your database has a time type, it’s more appropriate. If not, use a number (minutes since midnight) or (or string) + a separate field for the timezone. The time you store should be local to the restaurant, not translated to UTC. This is because you don’t want the opening times to shift by an hour by the time DST comes around.

    An extra thing you may run into in the future is that some places will have a closing time past midnight, but from an ‘opening times’ and accounting perspective it’s usually counted as the day before. So does 5:00 mean 5am the current day, or the next? So the simplest may be to store the opening time + how long the restaurant is open for to avoid this problem.

    Taking a look at the Xata docs, it seems there’s no time type, so my vote would go for a table with day_of_the_week, and opening_time and opening_duration typed as int expressed as minutes since midnight and minutes since opening respectively.

    Source: I’m a former member of the CalConnect internet calendaring standards group.

    Login or Signup to reply.
  2. 0

    Never store temporal data as strings.
    Use appropriate date/time data types – including date, time, timestamp, timestamptz, and interval. Or maybe a range type based on one of these.

    Which are appropriate depends on the full (undisclosed) use case.

    If your opening hours never cross midnight, time may be fine. Else, timestamp may be best.

    Related:

    Login or Signup to reply.
  3. 0

    Always a nice "problem" and one that is easy to solve: Just store every day that your property is open. With just 365 days a year, you can only create 11000 records for the next 30 years. With 10.000 restaurants, it would take just 110 million records. This is why databases were invented in the first place!

    Something like this works like a charm and could easily convert into a reservation system, including constraints to avoid double reservations.

    CREATE TABLE restaurant(
    id_restaurant INT PRIMARY KEY GENERATED ALWAYS AS IDENTITY
    , restaurant_name TEXT NOT NULL
    -- other columns and some unique constraint on some combination of columns
);

CREATE TABLE restaurant_hours(
    id_restaurant_hours INT PRIMARY KEY GENERATED ALWAYS AS IDENTITY
    , id_restaurant INT NOT NULL REFERENCES restaurant(id_restaurant) ON DELETE CASCADE
    , hours tsrange NOT NULL -- tsrange type!
);

INSERT INTO restaurant(restaurant_name) 
VALUES ('Mano') 
RETURNING id_restaurant;

INSERT INTO restaurant_hours(id_restaurant, hours)
SELECT
    1 -- the id that has been created for the restaurant
    , tsrange( ts
        , CASE
                WHEN EXTRACT(dow from ts) = 0 THEN ts + INTERVAL '6 hours'  -- Sunday
                    ELSE ts + INTERVAL '8 hours'
            END
        , '[)'
    ) AS daily_openinghours
FROM generate_series('2024-01-01 17:00'::timestamp, '2053-12-31 17:00', INTERVAL '1 day') g(ts)
WHERE NOT  (EXTRACT(MONTH  FROM ts) = 1 AND EXTRACT(DAY FROM ts) = 1) -- always closed
ORDER BY ts;

DELETE
FROM restaurant_hours
WHERE   EXTRACT(MONTH  FROM lower(hours)) = 7
AND     EXTRACT(DAY  FROM lower(hours)) = 4; -- Closed on the 4th of July

UPDATE  restaurant_hours
SET     hours = tsrange(lower(hours), lower(hours) + interval '6 hours') -- close early
WHERE   EXTRACT(MONTH  FROM lower(hours)) = 12
AND     EXTRACT(DAY  FROM lower(hours)) = 24; -- Christmas eve

SELECT *
FROM restaurant_hours
    JOIN restaurant USING (id_restaurant)
WHERE   restaurant_name = 'Mano'
AND     hours @> '2024-02-24 23:00'::timestamp; -- open, Saturday

SELECT *
FROM restaurant_hours
    JOIN restaurant USING (id_restaurant)
WHERE   restaurant_name = 'Mano'
AND     hours @> '2024-02-25 23:00'::timestamp; -- closed, Sunday

    With this you will never have problems with exceptions, every day you can set something different. You can delete a date, later on open that day again, change the time, etc. And searching is easy, just use the standard timestamp and tsrange functions.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search