在MySQL中从出生日期算起年龄 [英] Calucate age from birth date in mysql

问题描述

我在mysql中有一个带有出生日期列的表，该表另存为unix时间戳(bigint).

I have a table in mysql with birth date column that saved as unix timestamp (bigint).

我想这样写查询:

SELECT ... as `age` FROM `tableName`

还有

SELECT * FROM `tableName` WHERE ... > 30

3个点是从出生日期算起年龄的函数.

When the 3 dots is the function that calculate the age from the birth date.

我知道函数TIMESTAMPDIFF，但是如果我将生日保存为unix时间戳，那就不好了.

I know about the function TIMESTAMPDIFF, but it's not good if I save the birth date as unix timestamp.

我该怎么办?

谢谢

推荐答案

来自MySQL的

From MySQL's date and time functions, we can combine TIMESTAMPDIFF, NOW, and FROM_UNIXTIME.

假设dob是代表生日的Unix时间戳:

Supposing that dob is a Unix timestamp representing date of birth:

 TIMESTAMPDIFF(YEAR, FROM_UNIXTIME(dob), NOW())

从那里开始，基于列值添加WHERE子句非常简单.

From there, it's simple enough to add a WHERE clause based on the column value.

SQLFiddle上的实时演示.

这篇关于在MySQL中从出生日期算起年龄的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！