MySQL select only where Timestamp is from last 10 days

Question

I want to limit my query to results that have been entered in the last 10 days. The TIMESTAMP column is called Date. How do I do it?

$result = mysql_query("SELECT * FROM Posts WHERE (City = '$city2') ORDER by Comments DESC LIMIT 5");

Thanks

Solution 1

SELECT *
FROM Comments
WHERE (City = '$city2') AND (`Date` > DATE_SUB(now(), INTERVAL 10 DAY));

Note: calling a column 'Date' is poor practice, since it's a reserved word.

Solution 2

You can use DATEDIFF or, as already posted, DATE_SUB. Also, I suggest not using reserved words like "Date" for column names. Example for your code:

WHERE DATEDIFF(NOW(), `Date`) < 10

Solution 3

Try with date_sub

select * from Comments 
where City = '{$city2}' and 
`Date` > date_sub(now(), interval 10 day)

Comments

[lisovaccaro]

sorry, I posted the wrong query. I tried using this, it looks alright but I don't get any results. However all my posts where inputted yesterday. (1 day away).

[Marc B]

confirm that your date field contains the proper dates, and is actually a datetime field. If it's an int (e.g. unix timestamp), this query won't work and you'll have to use unix_timestamp/from_unixtime to convert as appropriate.

[Samuel Ramzan]

Make sure your hosting server haves your regional time set, otherwise the calculation of time will be based on the server settings on Miami while you are on Hawaii. It happens.

[Samuel Ramzan]

Good code example, although impolite to tell someone they have poor practice. LOL... OLD POST! I know, hope you've develop some nice and sweet manners on the time that haves passed bye. Love You man.

[Ido]

It seems less efficient (about 2x time slower) than DATE_SUB, from some test I ran in PhpMyAdmin.