在给定日期时间前10分钟检索记录

46scxncf  于 2021-06-23  发布在  Mysql
关注(0)|答案(2)|浏览(466)

需要查找从给定日期到时间过去10分钟的记录。从下面的查询中,它会在给定日期前10分钟获取记录,而给定日期又会选择前一天或前一天的所有记录。
因为我期待着检索所有的记录,从给定的日期,是从10分钟前。但不是所有的记录,即前几天提到的给定日期。

$sql = "SELECT otb.*
FROM oc_table_book otb
LEFT JOIN oc_order o
ON (o.order_id = otb.order_id)
WHERE otb.preorder_status_id = '3' AND 
otb.booked_date <= '" . $this->db->escape($table_data['booked_date']) . 
"' - INTERVAL 10 MINUTE ORDER BY otb.booked_date";

尝试使用dateadd()无效

$sql = "SELECT otb.*
FROM oc_table_book otb
LEFT JOIN oc_order o
ON (o.order_id = otb.order_id)
WHERE otb.preorder_status_id = '3' AND 
otb.booked_date <= dateadd(minute, -10, '" . $this->db->escape($table_data['booked_date']) . 
")";

结果

Fatal error: Uncaught Exception: Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''2018-01-19 07:30:00)' at line 6<br />Error No: 1064<br />SELECT otb.* FROM oc_table_book otb LEFT JOIN oc_order o ON (o.order_id = otb.order_id) WHERE otb.preorder_status_id = '3' AND otb.booked_date <= dateadd(minute, -10, '2018-01-19 07:30:00) in /var/www/html/silverspoon/system/library/db/mysqli.php on line 40
shyt4zoc

shyt4zoc1#

sql语法有错误
尝试此查询:

SELECT otb.* FROM oc_table_book otb
LEFT JOIN oc_order o
ON o.order_id = otb.order_id
WHERE otb.preorder_status_id = '3' AND 
otb.booked_date <= dateadd(minute, -10,".$this->db->escape($table_data['booked_date']).")
lndjwyie

lndjwyie2#

你似乎想要:

SELECT otb.*
FROM oc_table_book otb JOIN  -- I don't see why LEFT JOIN is necessary
     oc_order o
     ON o.order_id = otb.order_id
WHERE otb.preorder_status_id = 3 AND -- probably an integer
      otb.booked_date <= ? - INTERVAL 10 MINUTE AND
      otb.booked_date >= DATE(?)
ORDER BY otb.booked_date;

注意使用 ? . 这是一个参数占位符。您应该使用它将常量值传递到查询中。

相关问题