Comparing Times Php & Mysql

[Interista]

Hello,

 

I have a restaurants opening times hours stored in MySQL DB for each restaurant as Time . ex 10:00:00 - 22:00:00

the problem is when I compare 7:30 with 22:30 , 7:30 would be more than 22:30 !

 

So I want to get current time and compare it with the stored one .

[Zane]

Give us an example of how you are doing it now...

[Barand]

Are you storing the time as VARCHAR instead of TIME fields ?

[Interista]

Give us an example of how you are doing it now...

 

 

global $currentTime;
$currentTime = gmdate('G:i:s', time() + (3 * 3600));
$restTime    = mysql_query("SELECT `r_ofrom` , `r_oto` , `rid` , `r_status` FROM `restaurant` ");
while ($get_times = mysql_fetch_array($restTime)) {
   $openFrom   = $get_times['r_ofrom'];
   $openTo     = $get_times['r_oto'];
   $restID     = $get_times['rid'];
   $restStatus = $get_times['r_status'];
   if ($currentTime > $openFrom && $currentTime < $openTo) {
       if ($restStatus == 'C')
           mysql_query("update restaurant SET r_status='O' where rid='$restID'");

   } else {
       if ($restStatus == 'O')
           mysql_query("update restaurant SET r_status='C' where rid='$restID'");

   }
}

 

Are you storing the time as VARCHAR instead of TIME fields ?

 

No not as VARCHAR , I 'm storing the time as TIME . ex 20:00:00

[Barand]

Does it help if you use "H:i:s" (02:30:45) instead of "G:i:s" (2:30:45)

[Zane]

There's no reason to store whether a restaurant is open or closed when you already have the open and close times in there. Using SQL you can tell whether it is open or close with the TIME() BETWEEN syntax combined with a CASE statement.

 

 

SELECT `r_ofrom` , `r_oto` , `rid` ,
CASE WHEN (TIME(NOW()) BETWEEN r_ofrom AND r_oto) THEN true
ELSE false
END as is_open
FROM `restaurant`

Then you will not have to do a double query... and the open/close value will be in something like $row['is_open']

 

Using a ternary statement, in PHP, you can display text or images to show its status...

echo $row[´r_name´] . " is " . $row['is_open'] ? "open" : "closed";

Edited November 7, 2012 by Zane

[Interista]

Does it help if you use "H:i:s" (02:30:45) instead of "G:i:s" (2:30:45)

no , it's not

same problem :$

[Zane]

Your PHP comparison will not work because gmdate and date or any other get date/time function will always return a string. In order to actually compare them with >'s or

Edited November 7, 2012 by Zane

[Interista]

Your PHP comparison will not work because gmdate and date or any other get date/time function will always return a string. In order to actually compare them with >'s or <'s you would have to use strtotime() to convert it to an integer. Nonetheless, you can accomplish this much easier in SQL, as I have pointed out in my last post.

 

I have tried ur way , it works but the only problem with time like that .

if the restaurant open from 10:00:00 AM to the next day 03:00:00 AM ..

[Zane]

This link should help you out then

http://stackoverflow.com/questions/6237898/storing-time-ranges-in-mysql-and-determine-if-current-time-is-within-time-range

[Barand]

I worked for years in the transport industry. Night schedules for buses might start at 22:00 and run till 06:00. We used a 32 hour clock so if a timetable started at 22:00 then 06:00 was held as 30:00. It worked for us anyway. Of course, when we we printed the timetable we subtracted 24 hours from any times greater than 24:00

[Barand]

mysql> select * from shop;
+--------+----------+----------+
| idshop | opens    | closes   |
+--------+----------+----------+
|	  1 | 08:00:00 | 18:00:00 |
|	  2 | 08:00:00 | 23:00:00 |
|	  3 | 20:00:00 | 02:00:00 |
|	  4 | 19:00:00 | 01:00:00 |
|	  5 | 08:00:00 | 00:00:00 |
+--------+----------+----------+

 

mysql> SELECT * FROM test.shop
   -> WHERE '22:00' BETWEEN opens AND
   ->	 CASE WHEN closes < opens THEN addtime(closes , '24:00:00') ELSE closes END;
+--------+----------+----------+
| idshop | opens    | closes   |
+--------+----------+----------+
|	  2 | 08:00:00 | 23:00:00 |
|	  3 | 20:00:00 | 02:00:00 |
|	  4 | 19:00:00 | 01:00:00 |
|	  5 | 08:00:00 | 00:00:00 |
+--------+----------+----------+