问题描述
我想使用 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 =>
23 年 - 2011-06-11 - 2001-10-11 => 9 年
SQL 语法如何?MySQL 是否有任何内置函数来生成结果?
How about the SQL syntax? Is there any built in function from MySQL to produce the result?
推荐答案
这里的表达式也适用于闰年:
Here's the expression that also caters for leap years:
YEAR(date1) - YEAR(date2) - (DATE_FORMAT(date1, '%m%d') < DATE_FORMAT(date2, '%m%d'))
这是可行的，因为表达式 (DATE_FORMAT(date1, '%m%d') < DATE_FORMAT(date2, '%m%d')) 是 true如果date1比date2早于一年"and因为在mysql中，true = 1 and 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 - 我认为它应该是3"才能与测试 #1 保持一致:
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
这篇关于如何从两个不同的日期获得年份的差异?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持跟版网！