Best Practices for Storing Operational Hours Extending Past Midnight in MySQL

Question

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?

Answer 1

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.

Answer 2

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.