MySQL – Query Records Within 48 Hours

datetimeMySQL

I am storing some data intro a mysql database like this:

ID     |   DATE      |  INFO
-----------------------------
1      | 1389384821  |  blabla
2      | 1389384821  |  blabla2
3      | 1389384821  |  blabla3

DATE is a timestamp generated before adding it into database using time() function in PHP

I want to get all data from this table that was added in the last 24 hours, or 48 hours, 30 days …

I tried

SELECT
info
FROM table
WHERE date > (LOCALTIMESTAMP() - 86400)

Instead of LOCALTIMESTAMP(), I also tried CURDATE().

Where is my mistake?

I am not an expert in SQL, so this might sound a silly question.

WHERE date > (UNIX_TIMESTAMP(NOW()) – 86400) ?? Is this a good solution?

Best Answer

Here is 24 hours

SELECT info FROM table
WHERE date > UNIX_TIMESTAMP(NOW() - INTERVAL 1 DAY);

or

SELECT info FROM table
WHERE date > UNIX_TIMESTAMP(NOW() - INTERVAL 24 HOUR);

or your way

SELECT info FROM table
WHERE date > (UNIX_TIMESTAMP(NOW()) - 86400);

Here is 48 hours

SELECT info FROM table
WHERE date > UNIX_TIMESTAMP(NOW() - INTERVAL 2 DAY);

or

SELECT info FROM table
WHERE date > UNIX_TIMESTAMP(NOW() - INTERVAL 48 HOUR);

or your way

SELECT info FROM table
WHERE date > (UNIX_TIMESTAMP(NOW()) - (86400 * 2));

Here is 30 days

SELECT info FROM table
WHERE date > UNIX_TIMESTAMP(NOW() - INTERVAL 30 DAY);

or your way

SELECT info FROM table
WHERE date > (UNIX_TIMESTAMP(NOW()) - (86400 * 30));

Your last expression WHERE date > (UNIX_TIMESTAMP(NOW()) - 86400) is feasible, but requires you to do the number of seconds difference. My additional suggestions lets MySQL do the date computation.

Related Question