Hive SQL 通过生日精确计算年龄

Hive SQL 通过生日精确计算年龄

SELECT IF(DATEDIFF(CURRENT_DATE, CONCAT(SUBSTR(CURRENT_DATE, 0, 4), SUBSTR('2015-03-04', 5, 7))) >= 0,
          DATEDIFF(CURRENT_DATE, '2015-03-04') / 365,
          DATEDIFF(CURRENT_DATE, '2015-03-04') / 365 - 1);

select if(datediff(CURRENT_DATE, CONCAT(substr(CURRENT_DATE, 0, 4), substr('2015-03-04', 5, 7))) >= 0,
          (substr(CURRENT_DATE, 0, 4) - substr('2015-03-04', 0, 4)),
          (substr(CURRENT_DATE, 0, 4) - substr('2015-03-04', 0, 4) - 1));


SELECT FLOOR((CAST(CONCAT(SUBSTR(CURRENT_DATE, 0, 4), SUBSTR(CURRENT_DATE, 6, 2), SUBSTR(CURRENT_DATE, 9)) AS INT) -
              CAST(CONCAT(SUBSTR('2015-03-04', 0, 4), SUBSTR('2015-03-04', 6, 2), SUBSTR('2015-03-04', 9)) AS INT)) /
             10000);

MySQL 通过生日精确计算年龄

SELECT YEAR(CURRENT_DATE()) - YEAR(birthday) - (DAYOFYEAR(CURRENT_DATE()) < DAYOFYEAR(birthday)) AS age
FROM table_name;

参考

https://blog.youkuaiyun.com/XiangFei_Niu/article/details/88262998