如何从两个不同的日期获得多年的差异？

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 - 2011-07-18 => 0年
2011-07-20 - 2010-07-20 => 1 year
2011-07-20 - 2010-07-20 => 1年
2011-06-15 - 2008-04-11 => 2 3 years
2011-06-15 - 2008-04-11 => 2 3年
2011-06-11 - 2001-10-11 => 9 years
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有没有内置函数来产生结果？

7 个解决方案

#1

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 true if date1 is "earlier in the year" than date2 and because in mysql, true = 1 and false = 0, so the adjustment is simply a matter of subtracting the "truth" of the comparison.

这是有效的，因为表达式（DATE_FORMAT（date1，'％m％d'）（date2，'％m％d'））如果date1是“年初”而不是date2，则为真，因为在mysql中，true>

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

请参见SQLFiddle

#2

I like the solution by Bohemian, but what about using timestampdiff

我喜欢Bohemian的解决方案，但是如何使用timestampdiff

select date1, date2,timestampdiff(YEAR,date2,date1) from so7749639

sqlfiddle

just seems easier.

看起来更容易

#3

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)

#4

Simply by: SELECT TIMESTAMPDIFF(YEAR, date1, date2) AS difference FROM table.

简单地说：SELECT TIMESTAMPDIFF（YEAR，date1，date2）AS FROM FROM table。

#5

you could just use

你可以用

SELECT ROUND((TO_DAYS(date2) - TODAYS(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年。

#6

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.

这些结果返回整数年，月和日。他们“被淹没了”。因此，1.4天将显示为1天，13。9年将显示为13年。同样， - 1。4年将显示为 - 1年，-13.9个月将显示为-13个月。
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.

请注意，在许多情况下我使用布尔表达式。因为布尔表达式的计算结果为0或1，所以我可以根据条件使用它们从总计中减去或加1。

#7

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.

在大多数情况下保持十进制的地板是不正确的。

#1