CODEWITHSUNDEEP
phpmysqlsql
I'll-Be-Back
I'll-Be-Back

Reputation: 10828

Dealing with times and after midnight

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?

Upvotes: 9

Views: 3162

Answers (3)

ajreal
ajreal

Reputation: 47321

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;

Upvotes: 2

Adam Fowler
Adam Fowler

Reputation: 1751

I dont know what your trying to do, but using unix timestamps has always been the right answer in my option. its easier to calculate, and its always right regardless of time zones.

Maybe you should look into that

Upvotes: 0

Zoidberg
Zoidberg

Reputation: 10323

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.

Upvotes: 2

Related Questions