Mysql: select all records from a specific month using PHP

Question

I have a mysql database table named POST .
One of the fields is the timestamp set by default to the current timestamp on the mysql server when creating the record.

I need an SQL query to retrieve all rows with a timestamp for a specific month. That is, all entries for March or all entries for November, etc.

Answer 1

you need to use monthname ()

 select * from thetable where monthname(date_field) = 'February' 

or month (), but this function will return a number

 select * from thetable where month(date_field) = 2 

Answer 2

Using:

 SELECT p.* FROM POST p WHERE p.date BETWEEN '2011-03-01' AND DATE_SUB('2011-04-01', INTERVAL 1 SECOND) 

This will use the index if it exists in POST.date , if the function (IE: MONTHNAME, MONTH) is not used in the POST.date column.

Answer 3

You can use the name of the month ():

 select * from post where monthname(date) = 'February'