MySQL 如何在mysql上获取两个日期之间相差的天数？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2490173/
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 the number of days of difference between two dates on mysql?
提问by Audel
I need to get the number of days contained within a couple of dates on MySQL.
我需要获取 MySQL 上几个日期中包含的天数。
For example:
例如：
- Check in date is
12-04-2010 - Check out date
15-04-2010
- 入住日期是
12-04-2010 - 离开日期
15-04-2010
The day difference would be 3
天差为 3
回答by Pascal MARTIN
What about the DATEDIFFfunction ?
怎么样DATEDIFF功能？
Quoting the manual's page :
引用手册页：
DATEDIFF() returns expr1 – expr2 expressed as a value in days from one date to the other. expr1 and expr2 are date or date-and-time expressions. Only the date parts of the values are used in the calculation
DATEDIFF() 返回 expr1 – expr2 表示为从一个日期到另一个日期的天数。expr1 和 expr2 是日期或日期和时间表达式。计算中仅使用值的日期部分
In your case, you'd use :
在你的情况下，你会使用：
mysql> select datediff('2010-04-15', '2010-04-12');
+--------------------------------------+
| datediff('2010-04-15', '2010-04-12') |
+--------------------------------------+
| 3 |
+--------------------------------------+
1 row in set (0,00 sec)
But note the dates should be written as YYYY-MM-DD, and not DD-MM-YYYYlike you posted.
但请注意，日期应写为YYYY-MM-DD，而不是DD-MM-YYYY像您发布的那样。
回答by Konrad Ga??zowski
Note if you want to count FULL 24h daysbetween 2 dates, datediff can return wrong values for you.
请注意，如果您想计算2 个日期之间的24 小时全天，则 datediff 可能会为您返回错误的值。
As documentation states:
正如文档所述：
Only the date parts of the values are used in the calculation.
计算中仅使用值的日期部分。
which results in
这导致
select datediff('2016-04-14 11:59:00', '2016-04-13 12:00:00')
select datediff('2016-04-14 11:59:00', '2016-04-13 12:00:00')
returns 1 instead of expected 0.
返回 1 而不是预期的 0。
Solution is using select timestampdiff(DAY, '2016-04-13 11:00:01', '2016-04-14 11:00:00');(note opposite order of arguments comparing to datediff).
解决方案是使用select timestampdiff(DAY, '2016-04-13 11:00:01', '2016-04-14 11:00:00');（注意与 datediff 相比的参数顺序相反）。
Some examples:
一些例子：
select timestampdiff(DAY, '2016-04-13 11:00:01', '2016-04-14 11:00:00');returns 0select timestampdiff(DAY, '2016-04-13 11:00:00', '2016-04-14 11:00:00');returns 1select timestampdiff(DAY, '2016-04-13 11:00:00', now());returns how many full 24h days has passed since 2016-04-13 11:00:00 until now.
select timestampdiff(DAY, '2016-04-13 11:00:01', '2016-04-14 11:00:00');返回 0select timestampdiff(DAY, '2016-04-13 11:00:00', '2016-04-14 11:00:00');返回 1select timestampdiff(DAY, '2016-04-13 11:00:00', now());返回从 2016-04-13 11:00:00 到现在已经过去了多少完整的 24h 天。
Hope it will help someone, because at first it isn't that obvious why datediff returns values which seems to be unexpected or wrong.
希望它会对某人有所帮助，因为起初并不清楚为什么 datediff 返回似乎出乎意料或错误的值。
回答by Tobias Cohen
Use the DATEDIFF()function.
使用该DATEDIFF()功能。
Example from documentation:
文档中的示例：
SELECT DATEDIFF('2007-12-31 23:59:59','2007-12-30');
-> 1
回答by theblang
I prefer TIMESTAMPDIFFbecause you can easily change the unit if need be.
我更喜欢TIMESTAMPDIFF因为如果需要，您可以轻松更改单位。
回答by vijayabalan
Get days between Current date to destination Date
获取当前日期到目标日期之间的天数
SELECT DATEDIFF('2019-04-12', CURDATE()) AS days;
output
输出
days
天
335
回答by Developer
SELECT md.*, DATEDIFF(md.end_date, md.start_date) AS days FROM membership_dates md
output::
输出：：
id entity_id start_date end_date days
1 1236 2018-01-16 00:00:00 2018-08-31 00:00:00 227
2 2876 2015-06-26 00:00:00 2019-06-30 00:00:00 1465
3 3880 1990-06-05 00:00:00 2018-07-04 00:00:00 10256
4 3882 1993-07-05 00:00:00 2018-07-04 00:00:00 9130
hope it helps someone in future
希望它可以帮助将来的某个人
