-1

I'm building a PHP application to manage business operating hours, including cases where the closing time extends past midnight. I'm considering various ways to store these hours in MySQL and need advice on the best approach/best practices.

Challenges:

Some businesses operate past midnight, complicating data storage and retrieval. Efficiently querying current time against stored business hours is critical. Here is my current table that does not account for past midnight operation:

+--------------+------------+------+-----+---------+----------------+
| Field        | Type       | Null | Key | Default | Extra          |
+--------------+------------+------+-----+---------+----------------+
| id           | int        | NO   | PRI | NULL    | auto_increment |
| account_id   | int        | NO   | MUL | NULL    |                |
| weekday      | int        | NO   |     | NULL    |                |
| open         | time       | YES  |     | NULL    |                |
| close        | time       | YES  |     | NULL    |                |
+--------------+------------+------+-----+---------+----------------+

Proposed Solutions:

  1. Start Time and Duration: Store opening time and duration of operation.

  2. Is_overnight Flag: Use an is_overnight boolean field to indicate operations extending into the next day.

  3. Additional Entry for Overnight Hours: Implement PHP logic to check if the closing time is past midnight and, if so, insert an additional entry for the next day. This method splits the operating hours across two days where necessary.

Are there any better approaches or modifications to the above methods that can enhance scalability and manageability? Which method is preferred? This seems like it would be a common problem for many businesses such as Google Business, Yelp, etc. what is their solution?

5
  • 1
    Please define "efficiently", in particular whether you care about query speed or data storage size. And no, you probaby can't have both. Commented Apr 17 at 7:51
  • 2
    There was a similar question some days ago, can't find it quickly now. Handling time correctly is nontrivial - if you need to take DST into account, a night opening time from 10PM to 6AM may be 7, 8, or 9 hours, depending on whether it is on a DST-switching night or not. Commented Apr 17 at 8:11
  • There may be a difference between opening hours as presented to a human (e.g. "during DST, Mon-Thu from 16:00 to sunset. On odd weeks also Fri 10:00-14:00"), versus the database representation you're using for querying this info (e.g. "find all stores within a 30km radius that are still open one hour from now"). For prior art on specifying opening hour rules, see OpenStreetMap: wiki.openstreetmap.org/wiki/Key:opening_hours
    – amon
    Commented Apr 17 at 10:24
  • 1
    I did answer a similar one before : softwareengineering.stackexchange.com/questions/310557/…
    – Ewan
    Commented Apr 17 at 13:06
  • In some applications, I'm used to seeing "25:00". The idea is that "06:00 2024-04-17" means 6am Wednesday morning, and "30:00 2024-04-16" means "6 hours post-midnight on Tuesday night", which may have a legally or operationally distinct meaning, e.g. for hours worked in a day. Unfortunately, I'm not sure that helps here, though.
    – Kaia
    Commented Apr 17 at 22:42

2 Answers 2

3

I've done this before and it's complex. You might have missed some edge case requirements

  1. Days longer than 24h
  2. Businesses with stores in different time zones.
  3. Daylight Savings where you have more than one 1am or skip an hour
  4. Closing at lunch

There is an accounting concept of a "Business Day" which Opens and Closes, you might have heard people talking about "Closing Out" where they take all the money out of the tills and count it up.

A business day is often longer than 24h. Either the business runs over night into a non-working day, or employees "Close Out" the following morning due to lack of time.

You can also have "Business Years" with more or less than 365 days that don't start on jan 1st

Opening and Closing times are best stored as Hours and Minutes of the day, that's how people think about them, and converted to local time for individual dates.

1

The best solution will depend on the kind of query your database will need to answer typically. If a typical query is like "is this business open now"/"when does this business open/close next", then the easiest option to answer those queries is your third approach.

This approach does indeed require some additional logic for handling the data input when it crosses midnight (typically closing time is before opening time). If you make sure the closing/opening times where the split happens at midnight are NULL, you can also easily filter them out in the queries.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.