侧边栏壁纸
博主头像
叩钉壹刻博主等级

7分技术,3分管理,2分运气

  • 累计撰写 28 篇文章
  • 累计创建 13 个标签
  • 累计收到 0 条评论

目 录CONTENT

文章目录

MySQL实现出生年月转年龄

鹿心肺语
2024-01-14 / 0 评论 / 1 点赞 / 65 阅读 / 3119 字

描述及实现

表达函数

image-20240114500211

  • 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;

示例效果

image-20240114140731

使用总结

对于涉及到人的年龄表来说,由于年龄会随着时间变化而变化,一般不合适直接存储年龄字段。所以就需要通过存储的出生年月日,进行动态计算。

1

评论区