)mysql日期時間函數1

MySQL 日期跟時間函數（一）
作者： Mr.Zhang
MySQL 日期跟時間的樣式
在MySQL 中有很多有用處的日期跟時間函數。我們常常看到許多應用於日期的計算是在代碼
方面上進行的，但是其實他們完全可以使用MySQL中內建的函數來實現。在我們實際的查看MySQL
的時間跟日期函數之前，先讓我們看看MySQL 的儲存內容。
DATETIME YYYY-MM-DD HH:MM:SS
DATE YYYY-MM-DD
TIMESTAMP YYYYMMDDHHSSMM
TIME HH:MM:SS
YEAR YYYY

TIMESTAMP 欄儲存了所有的14 個字元，但是你其實可以自己定義他的顯示方式。例如如果
你按照TIMESTAMP(2)的方式來定義，那麼只會顯示出2 位表示的年份，但是所有的資料仍然是被
儲存的。如果日後你想要列出所有的資料的時候，你只需要改變下限定條件，所有的內容就會顯現
出來。下表就是不同的定義以及表現的結果。

TIMESTAMP(14) YYYYMMDDHHMMSS
TIMESTAMP(12) YYMMDDHHMMSS
TIMESTAMP(10) YYMMDDHHMM
TIMESTAMP(8) YYYYMMDD
TIMESTAMP(6) YYMMDD
TIMESTAMP(4) YYMM
TIMESTAMP(2) YY

MySQL 對於所讀入的日期的格式是非常寬容的。雖然MySQL 有使用很廣的約定表現方式，你
仍然可以改用你喜歡的方式來表現。例如，你建立如下一個表：

CREATE TABLE time_table(dt DATETIME);
既可以使用如下約定的方式插入一條記錄：
INSERT INTO time_table(dt) VALUES('2003-03-31 11:22:12')；
也可以象這樣用’+’和’=’來實現：
INSERT INTO time_table(dt) VALUES('2003=03=31 11+22+12')；
當然，雖然這種方法可行，但是如果沒有必要，還是推薦你使用約定的方式。
簡單的日期計算
日期計算非常簡單。我們首先要查看的一個函數是YEAR() 。For example:
mysql> SELECT YEAR('2003-03-31');
+--------------------+
| YEAR('2003-03-31') |
+--------------------+
| 2003 |
+--------------------+
我們可以用“+”，“-”運算子，直接進行一些簡單的運算。例如你要察看該日期5 年後是哪一年，
you can use：
mysql> SELECT YEAR('2003-03-31')+5;
+----------------------+
| YEAR('2003-03-31')+5 |
+----------------------+
| 2008 |
+----------------------+
那麼同理，5 年前的日期：
mysql> SELECT YEAR('2003-03-31')-5;
+----------------------+
| YEAR('2003-03-31')-5 |
+----------------------+
| 1998 |
+----------------------+
當然，你也可能有不知道具體時間的時候，MySQL 給你提供了NOW() function：
mysql> SELECT NOW();
+---------------------+
| NOW() |
+---------------------+
| 2003-03-31 00:32:21 |
+---------------------+
或者CURRENT_DATE() function 只用來提供年月日：
mysql> SELECT CURRENT_DATE();
+----------------+
| CURRENT_DATE() |
+----------------+
| 2003-03-31 |
+----------------+
當然也有其他的時間間隔存取子： MONTH(), DAYOFMONTH(), HOUR(), MINUTE() and
SECOND(). For example：

mysql> SELECT MONTH(NOW()) AS m,
DAYOFMONTH(NOW()) AS d,
HOUR(NOW()) AS h,
MINUTE(NOW()) AS m,
SECOND(NOW()) AS s;
+------+------+------+------+------+
| m | d | h | m | s |
+------+------+------+------+------+
| 3 | 31 | 1 | 53 | 38 |
+------+------+------+------+------+

DAYOFMONTH() function 是命名規範的一個例外，因為他用了其他的方式返回日期。
DAYOFMONTH() 返回的1-31 的天數,DAYNAME() 返回的是現實中的哪一天， DAYOFWEEK()
返回的是1 (Sunday) to 7(Saturday) ， DAYOFYEAR() 返回的是1 到366。
Some examples：
mysql> SELECT DAYNAME('2000-01-01');
+-----------------------+
| DAYNAME('2000-01-01') |
+-----------------------+
| Saturday |
+-----------------------+
mysql> SELECT DAYOFWEEK('2000-01-01');
+-------------------------+
| DAYOFWEEK('2000-01-01') |
+-------------------------+
| 7 |
+-------------------------+
mysql> SELECT DAYOFYEAR('2000-12-31');
+-------------------------+
| DAYOFYEAR('2000-12-31') |
+-------------------------+
| 366 |
+-------------------------+
計算年齡
最常用的日期計算就是年齡計算，不幸得是，並沒有什麼函數可以直接實現這個功能，所以這
就需要我們稍微的動下腦筋，現在讓我們來做一些簡單的數學計算--你會發現我從目前時間開始(31
March 2003)。首先，我們簡單的從當前日期減掉出生日期：
mysql> SELECT YEAR(CURRENT_DATE)-YEAR('1971-01-01');
+---------------------------------------+
| YEAR(CURRENT_DATE)-YEAR('1971-01-01') |
+---------------------------------------+
| 32 |
+---------------------------------------+
如果你直接回複這個數字，那麼明顯的，計算是正確的。某個出生於1971 年1 月1 日的人到
2003 年3 月31 日是32 歲。但是如果是出生在12 月31 日的人呢：
mysql> SELECT YEAR(CURRENT_DATE)-YEAR('1971-12-31');
+---------------------------------------+
| YEAR(CURRENT_DATE)-YEAR('1971-12-31') |
+---------------------------------------+
| 32 |
+---------------------------------------+
年齡出錯了，這個人還沒有到32 歲。問題在於計算的時候只計算了年份，他只用年份相減，
忽視了月份和日期，最好的比較方式還是要用日期和月份先進行比較，看是否出生日期小於當前日
期， 如果是這樣,那隻需要計算年份，否則就需要減去一部分年份。這個聽起來非常狡詐，但是事
實上不是。讓我們慢慢理解一下，MySQL 中對於TRUE 用1 表示。FALSE 用0 表示的方式對我們
有很大的協助，For example：
mysql> SELECT 23>19;
+-------+
| 23>19 |
+-------+
| 1 |
+-------+
mysql> SELECT 23<19;
+-------+
| 23<19 |
+-------+
| 0 |
+-------+
23 >19(true)， so MySQL 返回'1'， 並且23 不小於19(false)， so MySQL 返回'0'。現在我們
需要一種方式來返回'MM-DD' 的日期形式， 幸運的是，現在我們有一個可以直接使用的函數了，
RIGHT()， 它用於返回從右開始的定義的位元的數值。

For example：
mysql> SELECT RIGHT('abcdef',2);
+-------------------+
| RIGHT('abcdef',2) |
+-------------------+
| ef |
+-------------------+
這樣就返回了從右開始數，2 位的數值'ef '。當然如果你要求的位元超過紀錄的位元，將顯示
全部內容：
mysql> SELECT RIGHT('abcdef',9);
+-------------------+
| RIGHT('abcdef',9) |
+-------------------+
| abcdef |
+-------------------+
所以為了返回'MM-DD' 的樣式，你需要返回5 位元據，for example：
mysql> SELECT RIGHT(CURRENT_DATE(),5);
+-------------------------+
| RIGHT(CURRENT_DATE(),5) |
+-------------------------+
| 03-31 |
+-------------------------+
現在我們就有了計算一個年齡的所有的資料，可以用下面的方法來計算了：
mysql> SELECT YEAR(CURRENT_DATE()) - YEAR('1971-12-31')
- (RIGHT(CURRENT_DATE(),5)<'12-31') AS age;
+------+
| age |
+------+
| 31 |
+------+
現在讓我們來看一些不太常見的函數。首先我們來看看MySQL 為這些函數定義的可以接受的
資料類型。
SECOND ss
MINUTE mm
HOUR hh
DAY DD
MONTH MM
YEAR YY
MINUTE_SECOND mm:ss
HOUR_MINUTE hh:mm
DAY_HOUR DD:hh
YEAR_MONTH YYYY-MM
HOUR_SECOND hh:ss
DAY_MINUTE DD hh:mm
DAY_SECOND DD hh:mm:ss
就是像你看到的這麼多。事實上，在你調用程式以前，MySQL 還有很多事情可以做。
用DATE_ADD()和DATE_SUB() 增加或減少時間和日期
DATE_ADD() function 和ADDDATE() function 具有相同的作用，都是用來在給定的日期和時間上增加一個特定的日期段或時間段，DATE_ADD(日期時間，間隔用日期時間型資料表示的時間
間隔)。
例如，為了確定2003 年七月13 日14 天后的日期，you can use：
mysql> SELECT DATE_ADD('2003-07-13', INTERVAL 14 DAY);
+-----------------------------------------+
| DATE_ADD('2003-07-13', INTERVAL 14 DAY) |
+-----------------------------------------+
| 2003-07-27 |
+-----------------------------------------+