How can I word a condition so that an sql statement pulls orders that were resolved up to 2 hours ago?

Question

I need a web page to display items that are active and that have been dispatched less than 2 hours ago, I can't figure out the less than 2 hours ago part

I've tried to set a date variable that is 2 hours behind the current time and comparing the time dispatched to the that variable

$DateVar = date('Y-m-d h:i', strtotime('-2 hours'));

$sql="SELECT Id, TransactionId, WONumber, DispatchText, SentBy, Vendor, Dispatched,
             Acknowledged, TimeDispatched, Time
      FROM `DumpsterDispatch`
      WHERE (Dispatched IS NULL
      OR Acknowledged = 'N'
      OR TimeDispatched >= \"$DateVar\")
      AND Id > 1490
      ORDER BY Id DESC LIMIT 100";

I expect the web page to display all active orders and any order resolved within the past 2 hours, however with this code it displays all of the resolved orders from the given day

You should not be injecting time from PHP into SQL. MySQL has something called `INTERVAL 2 HOUR` which you can use to count time. See: [Add 2 hours to current time in MySQL?](https://stackoverflow.com/q/589652/1839439) — Dharman, Jul 16 '19 at 22:46

ArendE · Accepted Answer · 2019-07-18T09:18:35.650

1

I would suggest you'd let MySQL handle it to prevent timezone related issues. E.g.

SELECT Id, TransactionId, WONumber, DispatchText, SentBy, Vendor, Dispatched, Acknowledged, TimeDispatched, Time 
FROM `DumpsterDispatch`
WHERE (
    Dispatched IS NULL 
    OR Acknowledged = 'N' 
    OR TimeDispatched >= DATE_SUB(NOW(), INTERVAL 2 HOUR) 
    AND Id > 1490 
)
ORDER BY Id DESC LIMIT 100;

Another way is using UNIX_TIMESTAMP(), it will also give the UTC timestamp, like your strtotime('-2 hours') is doing. E.g.

$dateVar = strtotime('-2 hours');
$sql = "SELECT Id, TransactionId, WONumber, DispatchText, SentBy, Vendor, Dispatched, Acknowledged, TimeDispatched, Time
        FROM `DumpsterDispatch` 
        WHERE (
            Dispatched IS NULL 
            OR Acknowledged = 'N' 
            OR UNIX_TIMESTAMP(TimeDispatched) >= {$dateVar} 
        )
        AND Id > 1490
        ORDER BY Id DESC LIMIT 100";

edited Jul 18 '19 at 09:18

answered Jul 16 '19 at 21:13

ArendE

957
1
8
14

I implemented the code snippet with UNIX_TIMESTAMP() but it will now display orders from up to 6 hours ago rather than just the 2 I wanted – Ducky Bangerter Jul 17 '19 at 12:53
Can you show the insert query? How is `TimeDispatched` set? – ArendE Jul 17 '19 at 18:55
Yeah, TimeDispatched holds a date with the format YYYY-mm-dd h:i – Ducky Bangerter Jul 18 '19 at 13:46
I meant how you insert the query; If you use `INSERT INTO table(column) VALUES (NOW());` it should work. I think the time zones are playing tricks on you, since you get 6 hours instead of 2.. You can check in PHP with [date_default_timezone_get](https://www.php.net/manual/en/function.date-default-timezone-get.php) and in MySQL with [SELECT @@GLOBAL.time_zone, @@SESSION.time_zone;](https://dev.mysql.com/doc/refman/5.5/en/time-zone-support.html). If it is SYSTEM, go to your Console (Linux) and run `date`. It's important that you understand this well, or you might run into issues later (e.g. DST) – ArendE Jul 18 '19 at 13:54

score 0 · Answer 2 · answered Jul 18 '19 at 13:27

I figured it out!

    $DateVar = date('Y-m-d h:i', strtotime('-2 hours'));

    $sql="SELECT data
    FROM `table`
    WHERE (Dispatched IS NULL
    OR Acknowledged = 'N'
    OR TimeDispatched >= \"$DateVar\")
    AND Id > 1490
    ORDER BY Id DESC LIMIT 100";

How can I word a condition so that an sql statement pulls orders that were resolved up to 2 hours ago?

2 Answers2