在某一天内选择MySQL数据库中的项目

I want to create a graph of items on a certain day using data from a MySQL database. I have the query

我想使用MySQL数据库中的数据创建某一天的项目图表。我有查询

SELECT * FROM activities 
WHERE timestamp BETWEEN UNIX_TIMESTAMP() - 24 * 3600 * 7 
    AND UNIX_TIMESTAMP() - 24 * 3600 * 6

to select items from x days ago (where the 7 and 6 can vary in order to select the day), but it's returning an empty set in my database for some reason.

从x天前选择项目（其中7和6可以变化以选择日期），但由于某种原因它在我的数据库中返回一个空集。

3 个解决方案

#1

Try below:

试试以下：

 SELECT * FROM activities
 WHERE  date(timestamp) between start_date
 and DATE_SUB(start_date,interval x day))

Replace start_date with your startdate and x with interval

将start_date替换为startdate，将x替换为interval

#2

Check warnings (SHOW WARNINGS). It probably looks like this:

检查警告（显示警告）。它可能看起来像这样：

Level: Warning Code: 1292 Message: Incorrect datetime value: '1332194471' for column 'timestamp' at row 1

级别：警告代码：1292消息：日期时间值不正确：'1332194471'表示第1行的'timestamp'列
In case it is DATETIME or TIMESTAMP column:

如果它是DATETIME或TIMESTAMP列：

SELECT * FROM activities WHERE DATE(timestamp) = DATE(NOW() - INTERVAL x DAY);

SELECT * FROM活动WHERE DATE（timestamp）= DATE（NOW（） - INTERVAL x DAY）;

#3

can you try this:

你能试试这个：

SELECT * FROM 'activities' WHERE 'timestamp'< UNIX_TIMESTAMP() - 24 * 3600 * 7 AND 'timestamp'> UNIX_TIMESTAMP() - 24 * 3600 * 6

I am really not sure but, it may work.

我真的不确定，但它可能会奏效。

#1