Using a converted Unix Timestamp to query the database

Question

I am currently using this:

$rawsql = "SELECT 
*
FROM 
    _erc_foffices n 
INNER JOIN 
    _erc_openings o ON n.id = o.branch_id AND o.dotw = DAYOFWEEK(CURRENT_DATE()) 
INNER JOIN 
    _erc_openings_times t ON o.id = t.opening_id
WHERE 
(
    UNIX_TIMESTAMP(CURRENT_TIMESTAMP()) BETWEEN 
        UNIX_TIMESTAMP(CONCAT(CURRENT_DATE(), ' ', t.open))
    AND UNIX_TIMESTAMP(CONCAT(CURRENT_DATE(), ' ', t.close)) 
) 
AND 
(
    n.id = %d
)
    ;";

to query the database, and return all of the details from the embassy if it is currently open.

Now, rather than the CURRENT_TIMESTAMP(), I need to find out whether the time stored in variable $current_local_time is between the 2 database values. Timetable times need to be stored in local times instead of London's times, so that's why I'm trying to convert them.

I have tried replacing the

WHERE 
    (
        UNIX_TIMESTAMP(CURRENT_TIMESTAMP()) BETWEEN
            UNIX_TIMESTAMP(CONCAT(CURRENT_DATE(), ' ', t.open))
        AND UNIX_TIMESTAMP(CONCAT(CURRENT_DATE(), ' ', t.close)) 
    ) 

with

WHERE 
    (
        UNIX_TIMESTAMP($current_local_time) BETWEEN 
            UNIX_TIMESTAMP(CONCAT(CURRENT_DATE(), ' ', t.open)) 
        AND UNIX_TIMESTAMP(CONCAT(CURRENT_DATE(), ' ', t.close)) 
    ) 

but I just get error messages. Could someone please tell me how I would do this properly?

Thanks for any help

Edit: The $current_local_time variable is taken like so:

date_default_timezone_set('Europe/London');
$time = date("H:i", time());
$myDateTime = new DateTime($time, new DateTimeZone('GMT'));
$myDateTime->setTimezone(new DateTimeZone($timezone));
$current_local_time = $myDateTime->format('H:i:s');

and outputs as whatever the current time in the other country is when echoed.

Edit 2:

I have changed the $current_local_time to this:

$current_local_time = $myDateTime->format('H:i:s');
$timestamp2 = strtotime($current_local_time);

and the where clause to this:

WHERE 
(
    '$timestamp2' BETWEEN 
        UNIX_TIMESTAMP(CONCAT(CURRENT_DATE(), ' ', t.open)) 
    AND UNIX_TIMESTAMP(CONCAT(CURRENT_DATE(), ' ', t.close)) 
) 

This works OK, but if t.close is set at 23:30, it won't actually shut until 23:31. Is there a way to take a minute off of the captured time to sort this out?

Answer 1

The timestamp that you're inserting needs to be quoted as a string, so you need

WHERE 
    (
        UNIX_TIMESTAMP('$current_local_time') BETWEEN 
            UNIX_TIMESTAMP(CONCAT(CURRENT_DATE(), ' ', t.open)) 
        AND UNIX_TIMESTAMP(CONCAT(CURRENT_DATE(), ' ', t.close)) 
    ) 

It's even better to use a SQL framework that lets you do parameter binding, specify the query as follows, and bind the ? to $current_local_time in PHP.

WHERE 
    (
        UNIX_TIMESTAMP(?) BETWEEN 
            UNIX_TIMESTAMP(CONCAT(CURRENT_DATE(), ' ', t.open)) 
        AND UNIX_TIMESTAMP(CONCAT(CURRENT_DATE(), ' ', t.close)) 
    ) 

EDIT: I think you want

(UNIX_TIMESTAMP(CONCAT(CURRENT_DATE(), ' ', t.open))     <=   '$timestamp2'
AND
'$timestamp2'    <    UNIX_TIMESTAMP(CONCAT(CURRENT_DATE(), ' ', t.close)))
--              ^^^ Note the less-than sign

You probably don't need quotes around $timestamp2 anymore.