我如何写一个条件，以便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";

Answer 2

我想通了！

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