CODEWITHSUNDEEP
mysqlselect
1Up
1Up

Reputation: 1044

Select Range of Items by Year and Month

I'm attempting to create a select statement which gets items by year and month.

This is what I have so far: 

SELECT * FROM sales WHERE YEAR(Date) = 2013 AND MONTH(?) = 'June'

I can't merely select date ranges because different months have different number days. It would be ideal to select months by their number (ie, January being 1) or a similar approach.

How is this worked out in a mysql statement?

The fields are datetime fields such as 2012-12-01 00:00:00

Upvotes: 2

Views: 158

Answers (1)

VMai
VMai

Reputation: 10346

Have a look at the performance and write your condition as

SELECT * FROM sales 
WHERE 
    Date >= '2013-06-01 00:00:00' 
AND
    Date < '2013-07-01 00:00:00'

for the example month: June of 2013

so MySQL can use an index on the column date. You will get exactly all rows with a date in the June of 2013, even those in the first second, but not those in the first second of the July of 2013.

You see, that you don't need to know the number of days of the particular month, because you will ever use the first of both months.

You could use a bit of date calculation too:

SELECT * FROM sales 
WHERE 
    Date >= '2013-06-01 00:00:00' 
AND
    Date < '2013-06-01 00:00:00' + INTERVAL 1 MONTH

so you need only to know the start and the length of the interval.

Note

The most important part of my answer is to use the column Date without using a function on this column, so MySQL can use an index.

Upvotes: 2

Related Questions