Clever Geek Handbook

Last day SQL items

In my code, I am trying to find the items in the activity table that are within the last day. This query returns no 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'";
+3


source to share


4 answers


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

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



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

+1


source


you can try using unix function "mktime" to get the value yesterday. as

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

for reference



if your database is mysql then you can extract yesterday to 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 that column inside a single quote.

+1


source


What you can use is DATE_SUB. It can be used like this

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

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

0


source


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

-2


source






More articles:


All Articles