Query MySQL database with timestamp

Question

I have a timestamp field [SubmissionDate], which defaults to current_timestamp, and i was wondering how do i query my database in such a fashion that for example i get only shown all entries submitted on a certain year and month? Something like:

SELECT * FROM DNA_entrys
WHERE `SubmissionDate`.month = February
AND `SubmissionDate`.year = 2004

Should be an elementary operation but i couldn't find a quick answer on that

Imre L · Accepted Answer · 2010-10-01 18:40:19Z

1

SELECT * FROM DNA_entrys
WHERE month(`SubmissionDate`) = 2
AND year(`SubmissionDate`) = 2004

tho i suggest:

SELECT * FROM DNA_entrys
WHERE `SubmissionDate` between '2004-02-01' and ('2004-03-01' - INTERVAL 1 SECOND)

latter allows use of indexes, which makes query faster if you have ithat field indexed.

edited Oct 1, 2010 at 18:40

answered Oct 1, 2010 at 18:31

Imre L

6,29126 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

eumiro · Accepted Answer · 2010-10-01 18:30:26Z

1

Does this work?

SELECT * FROM DNA_entrys
WHERE MONTH(SubmissionDate) = 2
AND YEAR(SubmissionDate) = 2004

answered Oct 1, 2010 at 18:30

eumiro

214k36 gold badges307 silver badges264 bronze badges

Collectives™ on Stack Overflow

Query MySQL database with timestamp

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related