Find records with a date field in the last 24 hours
MysqlSqlDateMysql Problem Overview
In my SQL query how do i make it find the records in the last 24 hours? Eg
SELECT * FROM news WHERE date < 24 hours
I usually do it by setting a variable to date() - 1 day and comparing it to that but I wondered whether the sql query way was faster?
Mysql Solutions
Solution 1 - Mysql
You simply select dates that are higher than the current time minus 1 day.
SELECT * FROM news WHERE date >= now() - INTERVAL 1 DAY;
Solution 2 - Mysql
SELECT * FROM news WHERE date > DATE_SUB(NOW(), INTERVAL 24 HOUR)
Solution 3 - Mysql
To get records from the last 24 hours:
SELECT * from [table_name] WHERE date > (NOW() - INTERVAL 24 HOUR)
Solution 4 - Mysql
SELECT * from new WHERE date < DATE_ADD(now(),interval -1 day);
Solution 5 - Mysql
SELECT * FROM news WHERE date > DATEADD(d,-1,GETDATE())
Solution 6 - Mysql
There are so many ways to do this. The listed ones work great, but here's another way if you have a datetime field:
SELECT [fields]
FROM [table]
WHERE timediff(now(), my_datetime_field) < '24:00:00'
timediff() returns a time object, so don't make the mistake of comparing it to 86400 (number of seconds in a day), or your output will be all kinds of wrong.
Solution 7 - Mysql
SELECT * FROM news WHERE date < DATEADD(Day, -1, date)