MySQL select today and last 30 days records

If you need to select records from MySQL table records with date today and last 30 days records.
based on the Server date.

MySQL select today and last 30 days records

SELECT * FROM posts WHERE 
date_of_create BETWEEN CURDATE() - INTERVAL 30 DAY AND CURDATE();

In case if your field data type were DATETIME format you need to convert your datetime format into date

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

MySQL Date Functions

Working with date in MySQL is very important for date based query processing. today we going to get some important MySQL date functions frequently used in projects.

Here is the date functions with example queries from Article table  have the column date_of_post

CURDATE() - Return the current date

Example:

SELECT * FROM articles WHERE date_of_post = CURDATE()

 

DAY()   - Return day number from the date column

Example:
SELECT * FROM article WHERE DAY(date_of_post) = 1

 

MONTH() - Return Month number from the date column

Example:
SELECT * FROM article WHERE MONTH(date_of_post) = 2

 

YEAR() – Return year number from the date column

Example:
SELECT * FROM article WHERE MONTH(date_of_post) = 2011

 

LAST_DAY() -  Return Last date as number, From the month of the date column

Example:
SELECT * FROM article WHERE DAY(date_of_post) = LAST_DAY(date_of_post)

 

DAYNAME() - Return day name as string from the date column

SELECT DAYNAME (date_of_post) AS `day_of_post` FROM article

 

MONTHNAME() - Return Month name as string from date column

Example:
SELECT MONTHNAME(date_of_post) as `month_of_post` FROM article

 

QUARTER() - Return Quarter Number as 1 to 4 Format as number

Example:
SELECT QUARTER(date_of_post) AS `post_quarter` FROM article

 

WEEK() - Return Week Number Number (0-53)

Example:
SELECT WEEK(date_of_post) AS `week ` FROM article