CODEWITHSUNDEEP
phpmysql
Will Gill
Will Gill

Reputation: 587

mysql: select all entries from a particular month using PHP

I have a mysql database table called POST.
One of the fields is a timestamp, set to default to the current timestamp on the mysql server when the record is created.

I need a SQL query to extract all rows that have a timestamp within a particular month. That is, all records for March, or all records for November, etc.

Upvotes: 7

Views: 15814

Answers (3)

OMG Ponies
OMG Ponies

Reputation: 332491

Use:

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

This will use an index if one exists on POST.date, when using a function (IE: MONTHNAME, MONTH) on the POST.date column will not.

Upvotes: 6

Andreas Wong
Andreas Wong

Reputation: 60506

You can use monthname():

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

Upvotes: 1

Raffael
Raffael

Reputation: 20045

you have 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

Upvotes: 16

Related Questions