Dealing with times and after midnight

Question

How would you deal with times and after midnight in PHP / MySQL?

Use INT store minutes or use TIME type field?

Consider the SQL query below:

SELECT * FROM opening_hours  
        WHERE week_day = WEEKDAY(NOW()) + 1 
              AND open_hour =< date_format(now(),'%H:%i') 
              AND close_hour >= date_format(now(),'%H:%i')

open_hour / close_hour fields are TIME type field.

Suppose that open_time is "18:00", close_time is "02:00", current time is "22:41". We have a separate DB record for the close_time (cause it's after midnight), but we will never get it in the result, because the close_time "02:00" is NOT greater than the current time, "22:41".

Also if current time is "01:00", we will get the NEXT day values, because the weekday doesn't match.

So what is the solution?

Would you rather store these values in INT (minutes), so the php is able to process these values directly, without any conversion?

For example...

Current time:

// w = Day of the week, H = 24-hour format, i = Minutes
$timearr = explode(':',date("w:H:i"));
$currenttime = ($timearr[0]) * 1440 + $timearr[1] * 60 + $timearr[2] 

In the database, the stored open/close time values in minutes.

Now suppose, that the current time is "Sun, 01:00" (first day of week), executing the above coversion this value is 60; and the open/close times for last day of week (Saturday) are set to "17:00" and "02:00" (which is actually Sunday), stored in the DB as 9660 and 10200 (Saturday, 26:00). In this case, the above query will not find the record we need (Sat, 17:00, 02:00), because we probably don't have any open_time less than "02:00" (120). To solve this, we convert "Sun, 01:00" to "Sat, 25:00", by adding 7*1440 (a whole week) to the $currenttime, which will result 10140. Then DB query like this:

SELECT open_time,clos_time FROM open_hours 
    WHERE (open_time <= $currenttime 
         AND close_time >= $currenttime)
         OR (open_time <= $currenttime +10080
         AND close_time >= $currenttime + 10080);

Or what is alternative and neater solution?

Answer 1

Storing minute (int(4) unsigned) is the way to go.
However, instead of storing weekday + opening_hour, closing hour (with offset),
you should stored the minute since Monday 12am :-

 Monday  18:00 = (1 - 1)*60 * 18 = 1080
 Tuesday 02:00 = (2 - 1)*60 * 24 + (2 * 60) = 1560
 ...
 // please take note shop could have different operating hour for each day

So, current time is Tuesday 1:30am, which is :-

 // mysql expression
 $expr = (weekday(current_timestamp)+1) * 1440 + (hour(current_timestamp)*60) + minute(current_timestamp)

The SQL :-

 select ... 
 from opening_hours
 where
 open_time  >= $expr and
 close_time <= $expr;

Answer 2

SELECT open_time,close_time FROM open_hours 
    WHERE
         (open_time <= close_time AND
         open_time <= $currenttime AND
         close_time >= $currenttime)
         OR
         (open_time >= close_time AND
         ($currenttime <= close_time OR
         $currenttime >= open_time))

So what I am doing here is if open_time is greater than close_time, then it must span midnight, in which case I check to ensure the current timer is greater than open time, or less than close time so it is ensured to fall in our time span

Open Time ------ Midnight ----- Close Time

If Open time is less than close time, then we know mid-night does not fall in between. As a result we can just check as normal.