One easy way to select earlier dates in MySQL

I recently read a post asking how to select entries in MySQL based on a date field. The original poster only wanting to choose those records that were newer than 7 days old.

This seems quite a common challenge that new PHP coders have, so here is an easy way to achieve it.

  • Firstly, check the date field is indeed a date (or datetime) field and not a varchar. Date comparisons cannot be done on varchar fields
  • Use the MySQL function: DATE_SUB(date, INTERVAL expr units)

One example is:

SELECT myField FROM myTable WHERE myDateField >= DATE_SUB(CURDATE(), INTERVAL 7 DAY);

However if the date field you want to use is actually a DateTime field, the above code may not work as the time throws things. In this case, you need to just be using the date part. For this we can use the MySQL DATE() command. So, the query now looks like this:

SELECT myField FROM myTable WHERE DATE(myDateField) >= DATE_SUB(CURDATE(), INTERVAL 7 DAY);

Here is the link for the official docs on DATE_SUB() and DATE_ADD();

http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_date-add

 


 

If you found this post helpful, sign up to my newsletter and get PHP tips sent directly to your inbox: