f 用sql 計算年齡 ~ 迪貝之家

Pages

用sql 計算年齡

 

研究MySQL 專書MySQL Cookbook
有一段內容提到了這個問題
我原本想說,這有什麼好談的~~
阿不就兩個時間的相減取出年份而已
其實不然,這是該段討論所描述的
Age determination is a type of date-interval calculation. However, you cannot simply
compute a difference in days and divide by 365 because leap years throw off the calculation.
(It is 365 days from 1995-03-01 to 1996-02-29, but that is not a year in age terms.)
Dividing by 365.25 is slightly more accurate, but still not correct for all dates.
To calculate ages, use the TIMESTAMPDIFF() function. Pass it a birth date, a current date,
and the unit in which you want the age expressed:
TIMESTAMPDIFF(unit,birth,current)
TIMESTAMPDIFF() handles the calculations necessary to adjust for differing month and
year lengths and relative positions of the dates within the calendar year.
得考量到閏年......
我上網查了有關Postgres MSSQL 及Oracle 的相關語法
才發覺......難怪MySQL這麼多人在用
尤其是Internet上的web 網站
因為任何一個帳號都是會員資料
坦白說,科技來自於人性
我以2000-02-29這個日期來列舉這四個資料庫的語法

MySQL :
select TIMESTAMPDIFF(YEAR,'2000-02-29',curdate());

Postgres :
select date_part('year',age(current_date,'2000-02-29'));

MSSQL:
select DATEDIFF(YY,'2000-02-29',getdate()) -
CASE
dateadd(YY,DATEDIFF(YY,'2000-02-29',getdate()),'2000-02-29') > getdate() then 1
Oracle : 我查了半天,甚至到metalink去查過
甲骨文可能認為這是開發人員要自己解決的
SELECT TRUNC(TO_NUMBER( TO_DATE('29-02-2020','DD-MM-YYYY') - TO_DATE('29-02-2000','DD-MM-YYYY')) / 365.25) AS AGE FROM DUAL;

商用資料庫還真沒opensource 資料庫來得好用,就這一點來看的話
但上述語法,準確度如何?? 嘿嘿嘿....I am not sure!!!
我沒實際implement的經驗
所以還是得看各位在business logic 的實際需求
我想老半天,大概就是退休年金相關軟體在年齡計算上有最大的concern
參考資料 :
Posgres Date/Time Functions and Operators
How to Calculate Age in SQL Server
####2020/09/25########
這個問題,今天在看處理validate date的部分時
仔細地想了一下
好像被書的內容搞得有點太複雜了
我個人認為是碰到2/29出生的人
如果非閏年要滿足歲
就是要跳到3/1 才算吧~~
以今年2020來看
是閏年,2/29後就滿足歲
明年2021年就是要3/1 後才滿歲
問題就在於判斷閏年的邏輯
這是書上perl 的邏輯
sub is_leap_year
{
my $year = $_[0];
return ($year % 4 == 0) && ((($year % 100) != 0) || ($year % 400) == 0);
}


sub days_in_month
{
my ($year, $month) = @_;
my @day_tbl = (31, 28, 31, 30, 31, 30, 31, 31, 30, 31, 30, 31);
my $days = $day_tbl[$month-1];
# add a day for Feb of leap years
$days++ if $month == 2 && is_leap_year ($year);
return $days;
}