php - 我如何表达一个条件，以便 sql 语句提取 2 小时前解决的订单？

Question

我需要一个网页来显示处于事件状态并且在 2 小时内发货的商品，我无法弄清楚 2 小时前的部分

我尝试设置一个比当前时间晚 2 小时的日期变量，并比较调度到该变量的时间

$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";

我希望网页显示所有事件订单以及过去 2 小时内解决的任何订单，但是使用此代码，它会显示给定日期的所有已解决订单

Answer 1

我建议您让 MySQL 处理它以防止时区相关问题。例如

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;

另一种方法是使用 UNIX_TIMESTAMP()，它也会给出 UTC 时间戳，就像您的 strtotime('-2 hours') 所做的那样。例如

$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";