研究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;
}