我希望使用MySQL数据库从两个不同的日期获得差异 .
例如:
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年
SQL语法怎么样? MySQL有没有内置函数来产生结果?
这里的表达也适合闰年:
YEAR(date1) - YEAR(date2) - (DATE_FORMAT(date1, '%m%d') < DATE_FORMAT(date2, '%m%d'))
这是有效的,因为如果date1是"earlier in the year"而不是date2,则表达式 (DATE_FORMAT(date1, '%m%d') < DATE_FORMAT(date2, '%m%d')) 是 true ,因为在mysql中, true = 1 和 false = 0 ,所以调整只是减去比较的"truth" .
(DATE_FORMAT(date1, '%m%d') < DATE_FORMAT(date2, '%m%d'))
true
true = 1
false = 0
这给出了测试用例的正确值,除了测试#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;
输出:
+------------+------------+------------+ | 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
我喜欢Bohemian的解决方案,但是如何使用timestampdiff
select date1, date2,timestampdiff(YEAR,date2,date1) from so7749639
sqlfiddle
看起来更容易
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()返回两个日期之间的天数差异 . 这并没有特别考虑闰年,但在这种情况下它可能会起作用:
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)
只需: SELECT TIMESTAMPDIFF(YEAR, date1, date2) AS difference FROM table .
SELECT TIMESTAMPDIFF(YEAR, date1, date2) AS difference FROM table
你可以使用
SELECT ROUND((TO_DAYS(date2) - TO_DAYS(date1)) / 365) ...
如果你想要一个正数,无论哪个日期先于另一个日期,也要用 ABS() 包裹它 .
ABS()
有 ROUND() ,0 . 6年将被视为1年,如果您只想计算整年,则可以使用 FLOOR() . 在这种情况下,0 . 6年将被视为0年,而1 . 9年将被视为1年 .
ROUND()
FLOOR()
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))))
现在有一些关于这些的评论 .
这些结果返回整数年,月和日 . 他们“被淹没了” . 因此,1.4天显示为1天,13 . 9年显示为13年 . 同样, - 1 . 4年将显示为-1年,-13.9个月将显示为-13个月 .
请注意,在很多情况下我使用布尔表达式 . 因为布尔表达式的计算结果为0或1,所以我可以根据条件使用它们从总数中减去或加1 .
这很有效,即使考虑到闰年:
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);
在大多数情况下保持十进制的地板是不正确的 .
7 回答
这里的表达也适合闰年:
这是有效的,因为如果date1是"earlier in the year"而不是date2,则表达式
(DATE_FORMAT(date1, '%m%d') < DATE_FORMAT(date2, '%m%d'))
是true
,因为在mysql中,true = 1
和false = 0
,所以调整只是减去比较的"truth" .这给出了测试用例的正确值,除了测试#3 - 我认为它应该是“3”以与测试#1保持一致:
输出:
见SQLFiddle
我喜欢Bohemian的解决方案,但是如何使用timestampdiff
sqlfiddle
看起来更容易
DATEDIFF()返回两个日期之间的天数差异 . 这并没有特别考虑闰年,但在这种情况下它可能会起作用:
只需:
SELECT TIMESTAMPDIFF(YEAR, date1, date2) AS difference FROM table
.你可以使用
如果你想要一个正数,无论哪个日期先于另一个日期,也要用
ABS()
包裹它 .有
ROUND()
,0 . 6年将被视为1年,如果您只想计算整年,则可以使用FLOOR()
. 在这种情况下,0 . 6年将被视为0年,而1 . 9年将被视为1年 .date1和date2之间的年数:
现在有一些关于这些的评论 .
这些结果返回整数年,月和日 . 他们“被淹没了” . 因此,1.4天显示为1天,13 . 9年显示为13年 . 同样, - 1 . 4年将显示为-1年,-13.9个月将显示为-13个月 .
请注意,在很多情况下我使用布尔表达式 . 因为布尔表达式的计算结果为0或1,所以我可以根据条件使用它们从总数中减去或加1 .
这很有效,即使考虑到闰年:
在大多数情况下保持十进制的地板是不正确的 .