南大通用GBase 8a通过生日计算年龄的方法

GBase 8a提供了各种日期函数，可以通过timestampdiff函数计算出2个日期相差的年数，从生日和指定日期的差距计算出年龄。本文包括了足年计算的年龄和按月四舍五入的年龄。

参考

GBase 8a数据库timestampdiff函数计算两个日期相差的年月日星期小时分秒毫秒等

只计算足年的年龄

如下样例中的日期可以改成now()表示当前日期。从例子中可以看到，

• 第一个是10年多1天，记10年。
• 第二个是10年整
• 第三个是差1天10年，计9年。

gbase> select birthday,TIMESTAMPDIFF(year,birthday,'2021-12-12') age from a1;
+------------+------+
| birthday   | age  |
+------------+------+
| 2011-11-11 |   10 |
| 2011-12-12 |   10 |
| 2011-12-13 |    9 |
+------------+------+
3 rows in set (Elapsed: 00:00:00.01)

时分秒影响结果

达到10年后，如果再多1秒，就变成9年了。

gbase> select TIMESTAMPDIFF(year,'2011-12-12','2021-12-12');
+-----------------------------------------------+
| TIMESTAMPDIFF(year,'2011-12-12','2021-12-12') |
+-----------------------------------------------+
|                                            10 |
+-----------------------------------------------+
1 row in set (Elapsed: 00:00:00.00)

gbase> select TIMESTAMPDIFF(year,'2011-12-12 00:00:01','2021-12-12');
+--------------------------------------------------------+
| TIMESTAMPDIFF(year,'2011-12-12 00:00:01','2021-12-12') |
+--------------------------------------------------------+
|                                                      9 |
+--------------------------------------------------------+
1 row in set (Elapsed: 00:00:00.00)

gbase> select TIMESTAMPDIFF(year,'2011-12-12 00:00:01','2021-12-12 00:00:01');
+-----------------------------------------------------------------+
| TIMESTAMPDIFF(year,'2011-12-12 00:00:01','2021-12-12 00:00:01') |
+-----------------------------------------------------------------+
|                                                              10 |
+-----------------------------------------------------------------+
1 row in set (Elapsed: 00:00:00.00)

精确到月的四舍五入年龄

0-5个月的忽略，6-11个月的算1年。

拿到月份

gbase> select birthday,TIMESTAMPDIFF(month,birthday,'2021-12-12') age from a1;
+------------+------+
| birthday   | age  |
+------------+------+
| 2011-11-11 |  121 |
| 2011-12-12 |  120 |
| 2011-12-13 |  119 |
+------------+------+
3 rows in set (Elapsed: 00:00:00.01)

同样的，即使只少一天，也少1个月。

gbase> select TIMESTAMPDIFF(month,'2011-06-11','2021-12-12');
+------------------------------------------------+
| TIMESTAMPDIFF(month,'2011-06-11','2021-12-12') |
+------------------------------------------------+
|                                            126 |
+------------------------------------------------+
1 row in set (Elapsed: 00:00:00.01)

gbase> select TIMESTAMPDIFF(month,'2011-06-12','2021-12-12');
+------------------------------------------------+
| TIMESTAMPDIFF(month,'2011-06-12','2021-12-12') |
+------------------------------------------------+
|                                            126 |
+------------------------------------------------+
1 row in set (Elapsed: 00:00:00.00)

gbase> select TIMESTAMPDIFF(month,'2011-06-13','2021-12-12');
+------------------------------------------------+
| TIMESTAMPDIFF(month,'2011-06-13','2021-12-12') |
+------------------------------------------------+
|                                            125 |
+------------------------------------------------+
1 row in set (Elapsed: 00:00:00.00)

月份除以12得到年份

gbase> select birthday,TIMESTAMPDIFF(month,birthday,'2021-12-12')/12 age from a1;
+------------+---------+
| birthday   | age     |
+------------+---------+
| 2011-11-11 | 10.0833 |
| 2011-12-12 | 10.0000 |
| 2011-12-13 |  9.9167 |
+------------+---------+
3 rows in set (Elapsed: 00:00:00.01)

四舍五入ROUND得到年龄

根据这个规则，都是10岁。也就是超过9岁6个月的(比如9岁11个月)，也计算为10岁。

gbase> select birthday,round(TIMESTAMPDIFF(month,birthday,'2021-12-12')/12) age from a1;
+------------+------+
| birthday   | age  |
+------------+------+
| 2011-11-11 |   10 |
| 2011-12-12 |   10 |
| 2011-12-13 |   10 |
+------------+------+
3 rows in set (Elapsed: 00:00:00.01)