如何从两个不同的日期得出年份的差异? [英] How to get the difference in years from two different dates?

问题描述

我想使用MySQL数据库从两个不同的日期得出年份之间的差异.

I want to get the difference in years from two different dates using MySQL database.

例如:

• 2011-07-20-2011-07-18 => 0年
• 2011-07-20-2010-07-20 => 1年
• 2011-06-15-2008-04-11 => 2 3年
• 2011-06-11-2001-10-11 => 9年

• 2011-07-20 - 2011-07-18 => 0 year
• 2011-07-20 - 2010-07-20 => 1 year
• 2011-06-15 - 2008-04-11 => 2 3 years
• 2011-06-11 - 2001-10-11 => 9 years

SQL语法如何? MySQL有内置函数可以产生结果吗?

How about the SQL syntax? Is there any built in function from MySQL to produce the result?

推荐答案

以下表达式也适用于leap年:

Here's the expression that also caters for leap years:

YEAR(date1) - YEAR(date2) - (DATE_FORMAT(date1, '%m%d') < DATE_FORMAT(date2, '%m%d'))

之所以可行，是因为如果date1比date2 和在年份中早"，则表达式(DATE_FORMAT(date1, '%m%d') < DATE_FORMAT(date2, '%m%d'))是true，因为在mysql，true = 1和false = 0中，因此调整为只需减去比较的真相"即可.

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.

这会为您的测试用例提供正确的值，但测试#3除外-我认为应该与测试#1一致为"3":

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:

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;

输出:

+------------+------------+------------+
| 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 |
+------------+------------+------------+

请参见 SQLFiddle

这篇关于如何从两个不同的日期得出年份的差异?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！