最后一天的SQL项目

In my code, I am trying to find items in an activities table that are within the last day. This query is not returning any results, are there any problems with it? Is there a better query?

在我的代码中，我试图在活动表中找到最后一天内的项目。此查询未返回任何结果，是否有任何问题？有更好的查询吗？

$curday = time() - (24*3600);
$query = "SELECT * FROM activities WHERE userid = '$userid' AND  'timestamp' > '$curday'";

4 个解决方案

#1

There are two choices here, you can get and format the date through PHP or use SQL language to do it. I prefer to do it within the SQL, it also allows me to use the same query in a MySQL client.

这里有两个选择，你可以通过PHP获取和格式化日期，或者使用SQL语言来完成它。我更喜欢在SQL中执行它，它还允许我在MySQL客户端中使用相同的查询。

This question is essentially the same thing: MySQL SELECT last few days?

这个问题基本上是一回事：MySQL SELECT过去几天？

This would be the new query: $query = "SELECT * FROM activities WHERE userid = '$userid' AND 'timestamp' > DATE_ADD(CURDATE(), INTERVAL -1 DAY)";

这将是新的查询：$ query =“SELECT * FROM activities WHERE userid ='$ userid'AND'timestamp'> DATE_ADD（CURDATE（），INTERVAL -1 DAY）”;

#2

you can try with unix function 'mktime' to get value of yesterday .. as

您可以尝试使用unix函数'mktime'来获取昨天的值.. as

$curday = mktime(0,0,0,date("m"),date("d")-1,date("Y"));

for reference

以供参考

if your database will mysql only then you can extract yesterday in sql itself..

如果你的数据库只有mysql那么你可以在sql本身提取昨天..

SELECT * FROM activities 
WHERE userid = '$userid' 
AND  timestamp >  DATE_SUB(CONCAT(CURDATE(), ' 00:00:00'), INTERVAL 1 DAY)

one more thing if timestamp is your column name don't put this column inside single quote ..

如果时间戳是您的列名，还有一件事情不要将此列放在单引号中。

#3

What you can use is DATE_SUB. This can be used as follows

您可以使用的是DATE_SUB。这可以如下使用

SELECT * FROM activities 
WHERE userid = '$userid' 
AND  timestamp > date_sub(current_date, interval 1 day)

This way you don't need to work with current date in PHP

这样您就不需要在PHP中使用当前日期

#4

-2

in Informix it would be (TODAY - 1) if the column is type DATE

如果列是DATE类型，那么在Informix中它将是（TODAY - 1）

#1