Search code examples
mysqlunix-timestamp

Mysql date condition shows incorrect results

SELECT * 
FROM memories 
WHERE date > unix_timestamp(DATE(NOW()) - INTERVAL 1 DAY) 
GROUP BY user_id 
ORDER BY date DESC

I use this query to get memories from 1 day

In the date column, it is all stored as UNIX timestamp, so I use 1583212980 timestamp which is Tuesday, March 3, 2020 5:23:00 AM (30 hours before the current date) to test it.

I get the record returned with a timestamp of 30 hours ago.

How can I check where is the problem here?

Solution

  • The DATE() is removing the time from the NOW() function and therefore when you subtract the 1 Day it subtracts a day from 2020-03-04 00:00:00

    Instead do 

    SELECT * 
FROM memories 
WHERE date > unix_timestamp(NOW() - INTERVAL 1 DAY) 
GROUP BY user_id 
ORDER BY date DESC