Mysql date condition shows incorrect results

Question

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?

`DATE(NOW())` may be replaced with `CURRENT_DATE` or any of its synonims. *I get the record returned with a timestamp of 30 hours ago.* You 1) truncate current datetime to date (i.e. you substract some hours/minutes/seconds) 2) then sbstract one day additionally. If current time is above 06:00 it is not a surprise that the record is returned. — Akina, Mar 04 '20 at 12:01

score 0 · Accepted Answer · answered Mar 04 '20 at 12:02

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

Mysql date condition shows incorrect results

1 Answers1