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