MySQL 在mysql中按月和年比较日期
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19206205/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
comparing dates by month and year in mysql
提问by Umesh Awasthi
I have a table containing data about events and festivals with following columns recording their start and end dates.
我有一个包含有关事件和节日的数据的表格，其中以下列列记录了它们的开始和结束日期。
- Start_Date
- End_Date
- 开始日期
- 结束日期
date format is in YYYY-MM-DD. I need to fetch event details with the following condition.
日期格式为YYYY-MM-DD. 我需要使用以下条件获取事件详细信息。
- Need to fetch all events which start with a current month and there end dates can be anything say
currentDate+next30days.
- 需要获取以当前月份开始的所有事件，并且可以有任何结束日期
currentDate+next30days。
I am clear about end date concept. but not sure how I can fetch data whose start dates are in a current month.
For this, I need to compare current year and current month against the Start_Datecolumn in my database.
我很清楚结束日期的概念。但不确定如何获取开始日期在当月的数据。为此，我需要将当前年份和当前月份与Start_Date数据库中的列进行比较。
Can anyone help me to point out as how I can do that?
谁能帮我指出我该怎么做？
回答by juergen d
select * from your_table
where year(Start_Date) = year(curdate())
and month(Start_Date) = month(curdate())
and end_date <= curdate() + interval 30 day
回答by Gordon Linoff
I don't like either of the other two answers, because they do not let the optimizer use an index on start_date. For that, the functions need to be on the current date side.
我不喜欢其他两个答案中的任何一个，因为它们不允许优化器在start_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 the date functions are on curdate(), which does not affect the ability of MySQL to use an index in this case.
所有日期函数都打开curdate()，在这种情况下，这不会影响 MySQL 使用索引的能力。
You can also include the condition on end_date:
您还可以包括条件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 can still take advantage of an index.
这仍然可以利用索引。
回答by BlitZ
DateTimefunctionsare your friends:
DateTime函数是你的朋友：
SELECT
*
FROM
`event`
WHERE
(MONTH(NOW()) = MONTH(`Start_Date`))
AND
(`End_Date` <= (NOW() + INTERVAL 30 DAY))
AND
(YEAR(NOW()) = YEAR(`Start_Date`))
回答by vanman
Comparing the year and month separately feels messy. I like to contain it in one line. I doubt it will make a noticeable difference in performance, so its purely personal preference.
分别比较年份和月份感觉很混乱。我喜欢将它包含在一行中。我怀疑它会对性能产生显着的影响，所以这纯粹是个人喜好。
select * from your_table
where LAST_DAY(Start_Date) = LAST_DAY(curdate())
and end_date <= curdate() + interval 30 day
So all I'm doing is using the last_day function to check the last day of the month of each date and then comparing this common denominator. You could also use
所以我所做的就是使用 last_day 函数来检查每个日期的月份的最后一天，然后比较这个公分母。你也可以使用
where DATE_FORMAT(Start_Date ,'%Y-%m-01') = DATE_FORMAT(curdate(),'%Y-%m-01')
