CODEWITHSUNDEEP
mysqldatetime
BeeDog
BeeDog

Reputation: 1895

How to form MySQL query to retrieve values dated to next month?

I have a table with a column dedicated solely to DATETIME values (e.g. "2010-11-12 12:34:56"). I'm trying to figure out how to select all rows from the table where the month is the month after the current month, while also taking into account for year changes (that is, if the current month is December in 2010, I want to select all values that are dated January 2011).

I have a hard time figuring out how to do it. This is my current query for selecting all values for the remainder of the current month:

$query = "SELECT * FROM mytable WHERE uid = " . $_SESSION['uid'] . " AND EXTRACT(YEAR FROM NOW()) = EXTRACT(YEAR FROM date_time) AND EXTRACT(MONTH FROM NOW()) = EXTRACT(MONTH FROM date_time) AND EXTRACT(DAY FROM NOW()) < EXTRACT(DAY FROM date_time) ORDER BY date_time";

I tried using the guide at http://dev.mysql.com/doc/refman/5.0/en/date-calculations.html (CURDATE and MOD), but I couldn't get it to work. :(

Any help is very appreciated!

Upvotes: 1

Views: 2357

Answers (1)

littlegreen
littlegreen

Reputation: 7420

... WHERE YEAR(date_time) = YEAR(DATE_ADD(NOW(), INTERVAL 1 MONTH)) 
AND MONTH(date_time) = MONTH(DATE_ADD(NOW(), INTERVAL 1 MONTH))

This one would be faster because it can utilize available indexes on the datetime field:

... WHERE date_time BETWEEN 
    DATE_SUB(DATE_ADD(NOW(), INTERVAL 1 MONTH),INTERVAL (DAY(NOW())-1) DAY)
    AND
    DATE_SUB(DATE_ADD(NOW(), INTERVAL 2 MONTH),INTERVAL (DAY(NOW())) DAY)

Upvotes: 2

Related Questions