MySQL 如何在mysql选择查询中获取两个日期之间的日期列表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9295616/
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
How to get list of dates between two dates in mysql select query
提问by Sangeetha Krishnan
I want list of dates lies between two dates by select query. For example:
我希望通过选择查询的日期列表位于两个日期之间。例如：
If i give '2012-02-10' and '2012-02-15' I need the result.
如果我给出 '2012-02-10' 和 '2012-02-15' 我需要结果。
date
----------
2012-02-10
2012-02-11
2012-02-12
2012-02-13
2012-02-14
2012-02-15
How can i get?
我怎样才能得到？
回答by
Try:
尝试：
select * from
(select adddate('1970-01-01',t4.i*10000 + t3.i*1000 + t2.i*100 + t1.i*10 + t0.i) selected_date from
(select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0,
(select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t1,
(select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t2,
(select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t3,
(select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t4) v
where selected_date between '2012-02-10' and '2012-02-15'
-for date ranges up to nearly 300 years in the future.
- 日期范围可达未来近 300 年。
[Corrected following a suggested edit by UrvishAtSynapse.]
[根据 UrvishAtSynapse 的建议编辑进行了更正。]
回答by hexparrot
set @i = -1;
SELECT DATE(ADDDATE('2012-02-10', INTERVAL @i:=@i+1 DAY)) AS date FROM `table`
HAVING
@i < DATEDIFF('2012-02-15', '2012-02-10')
This will return your result set exactly as prescribed. This query only requires you change the two different dates in datediff and adddate.
这将完全按照规定返回您的结果集。此查询只需要您更改 datediff 和 adddate 中的两个不同日期。
回答by NateTallman
The accepted answer didn't work for me in MySQL 5.5. I updated the query to work for me:
接受的答案在 MySQL 5.5 中对我不起作用。我更新了查询以适合我：
select * from
(select adddate('1970-01-01',t4*10000 + t3*1000 + t2*100 + t1*10 + t0) selected_date from
(select 0 t0 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0,
(select 0 t1 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t1,
(select 0 t2 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t2,
(select 0 t3 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t3,
(select 0 t4 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t4) v
where selected_date between '2012-02-10' and '2012-02-15'
回答by Mark Byers
You can create a table containing all the dates you might ever need to use:
您可以创建一个包含您可能需要使用的所有日期的表格：
date
2000-01-01
2000-01-02
2000-01-03
...etc..
2100-12-30
2100-12-31
Then query that table as follows:
然后按如下方式查询该表：
SELECT date
FROM dates
WHERE date BETWEEN '2012-02-10' AND '2012-02-15'
回答by Nugget
Take a look at this post : Get a list of dates between two dates
看看这篇文章：获取两个日期之间的日期列表
Check the stored procedure that Ron Savagedid, this seems to correspond to what you need !
检查Ron Savage所做的存储过程，这似乎符合您的需要！
回答by blankabout
SELECT * FROM tablexxx WHERE datecol BETWEEN '2012-02-10' AND '2012-02-15';
