php - 在子查询后使用 BETWEEN 'date' AND 'date'，无法使其正常工作

Question

这个问题已经有答案了:

奥 git _a (27 个回答)

已关闭 6 年前。

这是我正在处理的工作流程...

Get data between time and time -> sort by newest -> filter out the duplicates phonenumbers -> search for three different variables string -> print result using php.

首先我使用了这个，但是它没有按最新记录排序:

  SELECT id, phone, text 
  FROM phones 
  WHERE timestamp BETWEEN '2016-10-01 00:00:00' AND '2016-10-06 23:59:00' 
  GROUP BY user 
  ORDER BY timestamp DESC

经过搜索和测试后，我让它可以与子查询一起使用:

SELECT id, phone, text 
FROM phones WHERE timestamp IN(SELECT
                       max(timestamp) 
                       FROM phones 
                       GROUP BY phone )  
ORDER BY timestamp DESC

但是现在我不确定该函数应该放在哪里:

BETWEEN '2016-10-01 00:00:00' AND '2016-10-06 23:59:00'

我在子查询和主查询之外尝试过。可以这样做吗？

Answer 1

尝试使用 str_to_date

SELECT id, phone, text 
FROM phones 
WHERE date(timestamp)  BETWEEN  str_to_date( '2016-10-01' ,'%Y-%m-%d') 
                              AND str_to_date( '2016-10-06'  '%Y-%m-%d') 
ORDER BY timestamp DESC