描述及实现
表达函数:
f(birthday)={nowDate−birthday(岁)nowDate−birthday≥1年nowDate−birthday(月)1年>nowDate−birthday≥1月nowDate−birthday(天)1月>nowDate−birthday≥0天f(birthday)=begin{cases} nowDate-birthday (岁) & nowDate-birthday ≥ 1年 \ nowDate-birthday (月) & 1年 > nowDate-birthday ≥ 1月 \ nowDate-birthday (天) & 1月 >nowDate-birthday ≥ 0天 \ end{cases}f(birthday)=⎩⎨⎧nowDate−birthday(岁)nowDate−birthday(月)nowDate−birthday(天)nowDate−birthday≥1年1年>nowDate−birthday≥1月1月>nowDate−birthday≥0天
- nowDate:现在日期
- birthday:出生年月日
SQL代码:
select
case
when TIMESTAMPDIFF(year,'birthday',CURDATE()) != 0
then CONCAT(TIMESTAMPDIFF(year, 'birthday', CURDATE()), '岁')
when TIMESTAMPDIFF(month,'birthday',CURDATE()) != 12 * TIMESTAMPDIFF(year,'birthday',CURDATE())
then CONCAT(TIMESTAMPDIFF(month, 'birthday', CURDATE())-12 * TIMESTAMPDIFF(year, 'birthday', CURDATE()), '月')
else CONCAT(ABS(TIMESTAMPDIFF(day, 'birthday', CURDATE())), '天')
end as age,
from
dual;
实践及效果
示例代码:
select
CURDATE() as currentDate,
date_format('2023-1-14','%Y-%m-%d') as useYear,
case
when TIMESTAMPDIFF(year,'2023-1-14',CURDATE()) != 0
then CONCAT(TIMESTAMPDIFF(year, '2023-1-14', CURDATE()), '岁')
when TIMESTAMPDIFF(month, '2023-1-14', CURDATE()) != 12*TIMESTAMPDIFF(year, '2023-1-14', CURDATE())
then CONCAT(TIMESTAMPDIFF(month, '2023-1-14', CURDATE())-12*TIMESTAMPDIFF(year, '2023-1-14', CURDATE()), '月')
else CONCAT(ABS(TIMESTAMPDIFF(day, '2023-1-14', CURDATE())), '天')
end as diffYear,
date_format('2023-12-14','%Y-%m-%d') as useMonth,
case
when TIMESTAMPDIFF(year ,'2023-12-14',CURDATE()) != 0
then CONCAT(TIMESTAMPDIFF(year, '2023-12-14', CURDATE()), '岁')
when TIMESTAMPDIFF(month, '2023-12-14', CURDATE()) != 12*TIMESTAMPDIFF(year, '2023-12-14', CURDATE())
then CONCAT(TIMESTAMPDIFF(month, '2023-12-14', CURDATE())-12*TIMESTAMPDIFF(year, '2023-12-14', CURDATE()), '月')
else CONCAT(ABS(TIMESTAMPDIFF(day, '2023-12-14', CURDATE())), '天')
end as diffMonth,
date_format('2024-1-14','%Y-%m-%d') as useDay,
case
when TIMESTAMPDIFF(year,'2024-1-14',CURDATE()) != 0
then CONCAT(TIMESTAMPDIFF(year, '2024-1-14', CURDATE()), '岁')
when TIMESTAMPDIFF(month, '2024-1-14', CURDATE()) != 12*TIMESTAMPDIFF(year, '2024-1-14', CURDATE())
then CONCAT(TIMESTAMPDIFF(month, '2024-1-14', CURDATE())-12*TIMESTAMPDIFF(year, '2024-1-14', CURDATE()), '月')
else CONCAT(ABS(TIMESTAMPDIFF(day, '2024-1-14', CURDATE())), '天')
end as diffDay
from
dual;
示例效果:
使用总结
对于涉及到人的年龄表来说,由于年龄会随着时间变化而变化,一般不合适直接存储年龄字段。所以就需要通过存储的出生年月日,进行动态计算。