Jump to content


Photo

Comparing Times Php & Mysql


  • Please log in to reply
11 replies to this topic

#1 Interista

Interista

    Member

  • Members
  • PipPip
  • 20 posts

Posted 07 November 2012 - 04:05 PM

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 .

#2 Zane

Zane

    Advanced Member

  • Moderators
  • 3,782 posts
  • LocationMurphy, NC

Posted 07 November 2012 - 04:13 PM

:psychic: Give us an example of how you are doing it now...
btn_donate_SM.gif  Want to thank me?  Contribute to my PayPal piggy-bank
 
401299.png

#3 Barand

Barand

    Sen . ( ile || sei )

  • Gurus
  • 13,885 posts
  • LocationCheshire, UK

Posted 07 November 2012 - 04:18 PM

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

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts
|baaSelect| generate js and php code for dynamic linked dropdowns

 


#4 Interista

Interista

    Member

  • Members
  • PipPip
  • 20 posts

Posted 07 November 2012 - 04:24 PM

:psychic: 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

#5 Barand

Barand

    Sen . ( ile || sei )

  • Gurus
  • 13,885 posts
  • LocationCheshire, UK

Posted 07 November 2012 - 04:47 PM

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

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts
|baaSelect| generate js and php code for dynamic linked dropdowns

 


#6 Zane

Zane

    Advanced Member

  • Moderators
  • 3,782 posts
  • LocationMurphy, NC

Posted 07 November 2012 - 04:56 PM

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 by Zane, 07 November 2012 - 04:58 PM.

btn_donate_SM.gif  Want to thank me?  Contribute to my PayPal piggy-bank
 
401299.png

#7 Interista

Interista

    Member

  • Members
  • PipPip
  • 20 posts

Posted 07 November 2012 - 05:04 PM

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 :$

#8 Zane

Zane

    Advanced Member

  • Moderators
  • 3,782 posts
  • LocationMurphy, NC

Posted 07 November 2012 - 05:09 PM

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.

Edited by Zane, 07 November 2012 - 05:11 PM.

btn_donate_SM.gif  Want to thank me?  Contribute to my PayPal piggy-bank
 
401299.png

#9 Interista

Interista

    Member

  • Members
  • PipPip
  • 20 posts

Posted 07 November 2012 - 05:58 PM

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 .. :(

#10 Zane

Zane

    Advanced Member

  • Moderators
  • 3,782 posts
  • LocationMurphy, NC

Posted 07 November 2012 - 06:05 PM

This link should help you out then
http://stackoverflow...thin-time-range
btn_donate_SM.gif  Want to thank me?  Contribute to my PayPal piggy-bank
 
401299.png

#11 Barand

Barand

    Sen . ( ile || sei )

  • Gurus
  • 13,885 posts
  • LocationCheshire, UK

Posted 07 November 2012 - 06:05 PM

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

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts
|baaSelect| generate js and php code for dynamic linked dropdowns

 


#12 Barand

Barand

    Sen . ( ile || sei )

  • Gurus
  • 13,885 posts
  • LocationCheshire, UK

Posted 07 November 2012 - 06:39 PM

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 |
+--------+----------+----------+

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts
|baaSelect| generate js and php code for dynamic linked dropdowns

 





0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users

Cheap Linux VPS from $5
SSD Storage, 30 day Guarantee
1 TB of BW, 100% Network Uptime

AlphaBit.com