CODEWITHSUNDEEP
mysql
JulianJ
JulianJ

Reputation: 1315

Return rows for next month, MYSQL

I have a mysql table which stores users' availability, stored in 'start' and 'end' columns as date fields.

I have a form where other users can search through the 'availabilty' with various periods like, today, tomorrow and next week . I'm trying to figure out how to construct the query to get all the rows for users who are available 'next month'.

The 'start' values maybe from today and the 'end' value might might be three months away but if next month falls between 'start' and 'end' then I would want that row returned. The nearest I can get is with the query below but that just returns rows where 'start' falls within next month. Many thanks,

sql= "SELECT * FROM mytable WHERE start BETWEEN DATE_SUB(LAST_DAY(DATE_ADD(NOW(), INTERVAL 1 MONTH)),INTERVAL DAY(LAST_DAY(DATE_ADD(NOW(), INTERVAL 1 MONTH)))-1 DAY) AND LAST_DAY(DATE_ADD(NOW(), INTERVAL 1 MONTH))";

Upvotes: 0

Views: 128

Answers (1)

Carsten Massmann
Carsten Massmann

Reputation: 28196

As you are interested in anything that happens in the full month following the current date you could try something like this:

SELECT * FROM mytable WHERE
FLOOR(start/100000000)<=FLOOR(NOW()/100000000)+1 AND
FLOOR(  end/100000000)>=FLOOR(NOW()/100000000)+1

This query make use of the fact that datetime values are stored in MySql internally as a number like 

SELECT now()+0
--> 20150906130640

where the digits 09 refer to the current month. FLOOR(NOW()/100000000) filters out the first digits of the number (in this case:201509). The WHERE conditions now simply test whether the start date is anywhere before the end of the next month and the end date is at least in or after the period of the next month.

(In my version I purposely left out the condition that start needs to be "after today", since a period that has started earlier seems in my eyes still applicable for your described purpose. If, however, you wanted that condition included too you could simply add an AND start > now() at the end of your WHERE clause.)

Edit

As your SQLfiddle is set-up with a date instead of a (as I was assuming) datetime column your dates will be represented differently in mumeric format like 20150907 and a simple division by 100 will now get you the desired month-number for comparison (201509):

SELECT * FROM mytable WHERE  
FLOOR(start/100)<=FLOOR(NOW()/100000000)+1 AND
FLOOR(  end/100)>=FLOOR(NOW()/100000000)+1

The number returned by NOW() is still a 14-digit figure and needs to be divided by 100000000. See your updated fiddle here: SQLfiddle

I also added another record ('Charlie') which does not fulfill your requirements.

Update

To better accommodate change-of-year scenarios I updated my SqlFiddle. The where clause is now based on 12*YEAR(..)+MONTH(..) type functions.

Upvotes: 1

Related Questions