mysql get date interval of 30 days

Very often we need to extract last 30 days, 7 days data from mysql DB. Here we will show easy way to get the data of given interval.

SELECT DATE_FORMAT(create_date, '%m/%d/%Y') FROM mytable WHERE create_date BETWEEN CURDATE() - INTERVAL 30 DAY AND CURDATE()

Also note that CURDATE() returns only the DATE portion of the date, so if you store create_date as a DATETIME with the time portion filled, this query will not select the today’s records.

In this case, you’ll need to use NOW instead:

SELECT DATE_FORMAT(create_date, ‘%m/%d/%Y’)
FROM mytable
WHERE create_date BETWEEN NOW() – INTERVAL 30 DAY AND NOW()

Posts to Review:

What is the best way to reduce the size of ibdata in mysql? MySQL substring function myisamchk — MyISAM Table-Maintenance Utility mysql query get date of 1 month interval