Check time from mysql database with PHP

Question

I have two times in mysql DB start 10:00 and end 18:00 now i want to check with PHP if time is not greater or less of this two times how to do it? Thanks Mysql is set to TIME only no datetime, basically i want to check if time now is not between start and end. start represent = from2 and end represent to2. Day_id is week day .this is what is not working

$time_now = date("H:i");
//in DB format 00:00 // time
echo "SELECT * FROM opening_hrs WHERE 
        (TIME(from2) > TIME('".$time_now."')  AND TIME(to2) < TIME('".$time_now."') )
        AND day_id=".$day_id;

any ideas how to check time("H:i"); between to times from DB?

No need to import time from PHP, MySQL has a `NOW()` function to use current time. — El_Vanja, Apr 04 '20 at 12:59
Side note: don't build queries by directly concatenating the parameters, it makes you open to [SQL injection](https://stackoverflow.com/questions/332365/how-does-the-sql-injection-from-the-bobby-tables-xkcd-comic-work). You should use [prepared statements](https://www.php.net/manual/en/mysqli.quickstart.prepared-statements.php) instead. — El_Vanja, Apr 04 '20 at 13:01
Is this correct: "*i want to check if time now is not between start and end*"? Your query is doing the exact opposite. — El_Vanja, Apr 04 '20 at 13:02
How i can use NOW() if server id in US and website is in AU? — Lubos, Apr 04 '20 at 13:05
well thanks for comment "El_Vanja" so a want to only check if time now is between times in DB eg: 12:00 and 16:00 do you have any example? — Lubos, Apr 04 '20 at 13:13

O. Jones · Accepted Answer · 2020-04-04T13:17:01.890

1

Try this query, using MySql's notion of the current time.

SELECT * FROM opening_hrs 
 WHERE TIME(NOW()) >= TIME(from2)
   AND TIME(NOW()) < TIME(to2)
   AND day_id=whatever

Pro tip Be careful to use >= for the beginning of time ranges and < for their ends, to avoid off-by-one errors.

NOW() is a time zone sensitive operation in MySql. If you do this right after you open each MySql connection. you'll get your local time from NOW().

SET time_zone = `Australia/Sydney`;

Obvs, use your own time zone designator.

edited Apr 04 '20 at 13:17

answered Apr 04 '20 at 12:59

O. Jones

103,626
17
118
172

I have set up date_default_timezone_set('Australia/Sydney'); – Lubos Apr 04 '20 at 13:21
do i need something else? in php set ot in php.ini? – Lubos Apr 04 '20 at 13:22
I gave you a pure-MySql way to do your query. For my answer to work for you, no changes to your php configuration are required. I corrected an error in your query pointed out by @El_Vanja, where you had the beginning and end of your range swapped (i've made that mistake countless times). – O. Jones Apr 04 '20 at 13:26

score 0 · Answer 2 · answered Apr 04 '20 at 13:26

I would just use current_time, which is a MySQL built-in that gives you the current time:

select * 
from opening_hrs 
where 
    current_time >= time(:from2)   
    and current_time < time(:to2)
    and day_id = :day_id

Note that you should be using prepared statements rather than concatenating variables into the query string: more about this in this famous SO post.

Check time from mysql database with PHP

2 Answers2