MySQL 如何从两个不同的日期获得年份的差异？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7749639/
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 difference in years from two different dates?
提问by aslingga
I want to get the difference in years from two different dates using MySQL database.
我想使用 MySQL 数据库从两个不同的日期获得年数的差异。
for example:
例如：
- 2011-07-20 - 2011-07-18 => 0 year
- 2011-07-20 - 2010-07-20 => 1 year
- 2011-06-15 - 2008-04-11 =>
23 years - 2011-06-11 - 2001-10-11 => 9 years
- 2011-07-20 - 2011-07-18 => 0 年
- 2011-07-20 - 2010-07-20 => 1 年
- 2011-06-15 - 2008-04-11 =>
23 年 - 2011-06-11 - 2001-10-11 => 9 年
How about the SQL syntax? Is there any built in function from MySQL to produce the result?
SQL 语法如何？MySQL 是否有任何内置函数来生成结果？
回答by Bohemian
Here's the expression that also caters for leap years:
以下是也适用于闰年的表达式：
YEAR(date1) - YEAR(date2) - (DATE_FORMAT(date1, '%m%d') < DATE_FORMAT(date2, '%m%d'))
This works because the expression (DATE_FORMAT(date1, '%m%d') < DATE_FORMAT(date2, '%m%d'))is trueif date1 is "earlier in the year" than date2 andbecause in mysql, true = 1and false = 0, so the adjustment is simply a matter of subtracting the "truth" of the comparison.
这是有效的，因为表达式(DATE_FORMAT(date1, '%m%d') < DATE_FORMAT(date2, '%m%d'))是true如果 date1 比 date2“早于一年”，并且因为在 mysql, true = 1and 中false = 0，所以调整只是减去比较的“真相”。
This gives the correct values for your test cases, except for test #3 - I think it should be "3" to be consistent with test #1:
这为您的测试用例提供了正确的值，除了测试 #3 - 我认为它应该是“3”才能与测试 #1 保持一致：
create table so7749639 (date1 date, date2 date);
insert into so7749639 values
('2011-07-20', '2011-07-18'),
('2011-07-20', '2010-07-20'),
('2011-06-15', '2008-04-11'),
('2011-06-11', '2001-10-11'),
('2007-07-20', '2004-07-20');
select date1, date2,
YEAR(date1) - YEAR(date2)
- (DATE_FORMAT(date1, '%m%d') < DATE_FORMAT(date2, '%m%d')) as diff_years
from so7749639;
Output:
输出：
+------------+------------+------------+
| date1 | date2 | diff_years |
+------------+------------+------------+
| 2011-07-20 | 2011-07-18 | 0 |
| 2011-07-20 | 2010-07-20 | 1 |
| 2011-06-15 | 2008-04-11 | 3 |
| 2011-06-11 | 2001-10-11 | 9 |
| 2007-07-20 | 2004-07-20 | 3 |
+------------+------------+------------+
See SQLFiddle
回答by pgee70
I like the solution by Bohemian, but what about using timestampdiff
我喜欢 Bohemian 的解决方案，但是如何使用timestampdiff
select date1, date2,timestampdiff(YEAR,date2,date1) from so7749639
just seems easier.
只是看起来更容易。
回答by sanmai
mysql> SELECT FLOOR(DATEDIFF('2011-06-11','2001-10-11')/365);
+------------------------------------------------+
| FLOOR(DATEDIFF('2011-06-11','2001-10-11')/365) |
+------------------------------------------------+
| 9 |
+------------------------------------------------+
1 row in set (0.00 sec)
DATEDIFF()returns difference in days between two dates. This does not specifically take leap years into account but it may work in such cases:
DATEDIFF()返回两个日期之间的天数差。这并没有特别考虑闰年，但它可能适用于以下情况：
mysql> SELECT FLOOR(DATEDIFF('2007-07-11','2004-07-11')/365);
+------------------------------------------------+
| FLOOR(DATEDIFF('2007-07-11','2004-07-11')/365) |
+------------------------------------------------+
| 3 |
+------------------------------------------------+
1 row in set (0.00 sec)
回答by B? Loong A Nh?i
Simply by: SELECT TIMESTAMPDIFF(YEAR, date1, date2) AS difference FROM table.
简单地说：SELECT TIMESTAMPDIFF(YEAR, date1, date2) AS difference FROM table。
回答by stivlo
you could just use
你可以用
SELECT ROUND((TO_DAYS(date2) - TO_DAYS(date1)) / 365) ...
Also wrap it with ABS()if you want always a positive number, no matter which date precedes the other.
ABS()如果您始终想要一个正数，也可以将其包装起来，无论哪个日期在另一个日期之前。
With ROUND(), 0.6 years will be considered 1 year, if instead you want to count only the full years, you can use FLOOR(). In this case 0.6 year will be considered 0 years, and 1.9 years will be considered 1 year.
使用ROUND()，0.6 年将被视为 1 年，如果您只想计算整年，则可以使用FLOOR(). 在这种情况下，0.6 年将被视为 0 年，1.9 年将被视为 1 年。
回答by Wazy
Number of years between date1 and date2:
date1 和 date2 之间的年数：
IF((YEAR(date2) - YEAR(date1)) > 0, (YEAR(date2) - YEAR(date1)) - (MID(date2, 6, 5) <
MID(date1, 6, 5)), IF((YEAR(date2) - YEAR(date1)) < 0, (YEAR(date2) - YEAR(date1)) +
(MID(date1, 6, 5) < MID(date2, 6, 5)), (YEAR(date2) - YEAR(date1))))
Now for some comments about these.
现在对这些进行一些评论。
These results return integer number of years, months, and days. They are "floored." Thus, 1.4 days would display as 1 day, and 13.9 years would display as 13 years. Likewise, -1.4 years would display as -1 year, and -13.9 months would display as -13 months.
Note that I use boolean expressions in many cases. Because boolean expressions evaluate to 0 or 1, I can use them to subtract or add 1 from the total based on a condition.
这些结果返回整数年、月和日。他们是“地板”。因此，1.4 天将显示为 1 天，而 13.9 年将显示为 13 年。同样，-1.4 年将显示为 -1 年，-13.9 个月将显示为 -13 个月。
请注意，我在许多情况下使用布尔表达式。因为布尔表达式的计算结果为 0 或 1，所以我可以使用它们根据条件从总数中减去或加 1。
回答by Steve Burke
This works well, even taking in account for leap years:
这很有效，即使考虑到闰年：
select floor((cast(date_format('2016-02-14','%Y%m%d') as int) - cast(date_format('1966-02-15','%Y%m%d') as int)/10000);
Keep the floor as a decimal will be incorrect most of the time.
将地板保留为小数在大多数情况下是不正确的。
