Geek Answers Handbook

Comparing dates by month and year in mysql

I have a table containing data on events and festivals with the following columns, in which the start and end dates are recorded.

  • The date of the beginning
  • end_date

The date format is in YYYY-MM-DD . I need to get event information with the following condition.

  • You need to get all events starting from the current month, and there can be something to say on current dates currentDate+next30days .

I understand the concept of an end date. but I don’t know how I can get data whose start dates are in the current month. To do this, I need to compare the current and current month with the Start_Date column in my database.

Can someone help me indicate how I can do this?

+8
sql mysql
source share
3 answers
 select * from your_table where year(Start_Date) = year(curdate()) and month(Start_Date) = month(curdate()) and end_date <= curdate() + interval 30 day
+13
source share

DateTime functions are your friends:

 SELECT * FROM `event` WHERE (MONTH(NOW()) = MONTH(`Start_Date`)) AND (`End_Date` <= (NOW() + INTERVAL 30 DAY)) AND (YEAR(NOW()) = YEAR(`Start_Date`))
+4
source share

I do not like the other two answers because they do not allow the optimizer to use the index on start_date . For this, the functions must be on the current side of the date.

So, I would go for:

 where start_date >= date_add(curdate(), interval 1 - day(curdate()) day) and start_date < date_add(date_add(curdate(), interval 1 - day(curdate()) day), interval 1 month)

All date functions are on curdate() , which does not affect the ability of MySQL to use the index in this case.

You can also include a condition on end_date :

 where (start_date >= date_add(curdate(), interval 1 - day(curdate()) day) and start_date < date_add(date_add(curdate(), interval 1 - day(curdate()) day), interval 1 month) ) and end_date <= date_add(curdate(), interval 30 day)

This may still use the index.

+3
source share

More articles:


All Articles