首先我們簡單介紹一些時間類型。

DATE 值格式為 'YYYY-MM-DD' 範圍為 '1000-01-01' TO '9999-12-31'

CREATE TABLE dateandtime(a DATE);
INSERT INTO dateandtime(a) VALUES('2020-12-10');

mysql> SELECT * FROM dateandtime;
+------------+
| a          |
+------------+
| 2020-12-10 |
+------------+
1 row in set (0.00 sec)

TIME 值格式為 'HH:MM:SS' 範圍為 '-838:59:59' TO '838:59:59'

CREATE TABLE dateandtime2 (b TIME);
INSERT INTO dateandtime2(b) VALUES('100525');

mysql> SELECT * FROM dateandtime2;
+----------+
| b        |
+----------+
| 10:05:25 |
+----------+
1 rows in set (0.00 sec)

YEAR 類型 位元組為1(1Byte) 值格式為 'YYYY' 範圍為 '1901' TO '2155'

CREATE TABLE year(a YEAR);
INSERT INTO year(a) VALUES('1955');
INSERT INTO year(a) VALUES(2100);

而YEAR比較特殊，我們也可只插入一位或兩位或三位數值來表示。

INSERT INTO year(a) VALUES(0);
 INSERT INTO year(a) VALUES('0');
INSERT INTO year(a) VALUES('008');

mysql> SELECT * FROM year;
+------+
| a    |
+------+
| 1955 |
| 2100 |
| 0000 |
| 2000 |
| 2008 |
+------+

我們在DATE,TIME,YEAR類型插入時皆以字串類型作為資料。

EX:INSERT INTO dateandtime2(b) VALUES('10:05:25');

但當我們以數值類型插入，也是可以的。

EX:INSERT INTO dateandtime2(b) VALUES(100525);

mysql> SELECT * FROM dateandtime2;
+----------+
| b        |
+----------+
| 10:05:25 |
| 10:05:25 |
+----------+
2 rows in set (0.00 sec)

DATETIME & TIMESTAMP

DATETIME 類型 值格式為 'YYYY-MM-DD 'HH:MM:SS'' 範圍為 '1000-01-01 00:00:00' TO '9999-12-31 23:59:59' 也就是DATE類型+TIME類型。

TIMESTAMP 類型 值格式為 'YYYY-MM-DD 'HH:MM:SS'' 範圍為“1970-01-01 00:00:01” UTC ~ “2038-01-19 03:14:07” （和時區有關）

TIMESTAMP&DATETIME區別

1.TIMESTAMP會根據我們時區做改變。DATETIME則不會。
2.TIMESTAMP佔4BYTE空間，DATETIME佔有8BYTE空間。
3.TIMESTAMP不可為空，DATETIME可為空。

如果我們不插入值至TIMESTAMP，預設會顯示當前系統時間。
如同輸入 SELECT NOW(); (可獲取當前時間)
也就是在建立TIMESTAMP時，等同系統自動幫我們設置成這樣。

CREATE TABLE TD(d DATETIME , t TIMESTAMP); 預設等同於下面這段DEFAULT NOW()....
CREATE TABLE TD(d DATETIME , t TIMESTAMP DEFAULT NOW() ON UPDATE NOW());

mysql> DESC TD;
+-------+-----------+------+-----+-------------------+-----------------------------+
| Field | Type      | Null | Key | Default           | Extra                       |
+-------+-----------+------+-----+-------------------+-----------------------------+
| d     | datetime  | YES  |     | NULL              |                             |
| t     | timestamp | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+-------+-----------+------+-----+-------------------+-----------------------------+
2 rows in set (0.00 sec)

當我們改變當前TIMESTAMP所在TABLE裡任何Column的值，TIMESTAMP的值會由於Extra on update的關係而更新一次，實例可應用在如，文章最後更新。

更新TABLE前

mysql> SELECT * FROM TD;
+---------------------+---------------------+
| d                   | t                   |
+---------------------+---------------------+
| 2011-05-12 12:15:55 | 2020-12-22 21:14:57 |
+---------------------+---------------------+
1 row in set (0.00 sec)

更新TABLE後(改變column d的值後)，t的時間變為當前時間。

mysql> SELECT * FROM TD;
+---------------------+---------------------+
| d                   | t                   |
+---------------------+---------------------+
| 2012-05-12 12:15:55 | 2020-12-22 21:23:18 |
+---------------------+---------------------+
1 row in set (0.00 sec)

time_zone

首先我們一樣創建TABLE並插入當前時間值

INSERT INTO test_TD2(t,d) VALUES (NOW(),NOW());

我們也可以更改時區，首先先顯示我們SQL的變數，並篩選與time_zone相關的變數。
可以利用SELECT TIMEDIFF(NOW(), UTC_TIMESTAMP);找出我們當前UTC時間。

mysql> SELECT TIMEDIFF(NOW(), UTC_TIMESTAMP);
+--------------------------------+
| TIMEDIFF(NOW(), UTC_TIMESTAMP) |
+--------------------------------+
| 08:00:00 |
+--------------------------------+
1 row in set (0.01 sec)

可發現為以UTC時間為基礎 +8小
如果我們直接利用SET修改時區的值，在SET time_zone="-12:00";

原本時間

mysql> SELECT * FROM test_TD2;
+---------------------+---------------------+
| d     | t   |
+---------------------+---------------------+
| 2020-12-22 14:27:17 | 2020-12-22 22:27:17 |
+---------------------+---------------------+
1 row in set (0.00 sec)

修改時區後時間

mysql> SELECT * FROM test_TD2;
+---------------------+---------------------+
| d   | t  | 
+---------------------+---------------------+
| 2020-12-22 14:27:17 | 2020-12-22 02:27:17 |
+---------------------+---------------------+
1 row in set (0.00 sec)
會發現時間錯誤。
因為TIMESTAMP是以UTC為基礎。再將時區減去時，才會正確。

所以我們要先將set time_zone='+8:00'; 改為 set time_zone='+0:00'; 也就是變為UTF時間基礎
修改前

mysql> SELECT * FROM test_TD2;
+---------------------+---------------------+
| d                   | t                   |
+---------------------+---------------------+
| 2020-12-22 14:27:17 | 2020-12-22 14:27:17 |
+---------------------+---------------------+
1 row in set (0.00 sec)

修改後

mysql> SELECT * FROM test_TD2;
+---------------------+---------------------+
| d                   | t                   |
+---------------------+---------------------+
| 2020-12-22 14:27:17 | 2020-12-22 02:27:17 |
+---------------------+---------------------+
1 row in set (0.00 sec)

可發現時區減去12時(14-12=2)已經正確。

時間函數

CREATE TABLE func(A YEAR,B DATE,C TIME,D DATETIME ,E TIMESTAMP);
INSERT INTO func(a,b,c,d,e) VALUES (2020,20201210,220050,NOW(),NOW());

mysql> SELECT * FROM func;
+------+------------+----------+---------------------+---------------------+
| A    | B          | C        | D                   | E                   |
+------+------------+----------+---------------------+---------------------+
| 2020 | 2020-12-10 | 22:00:50 | 2020-12-23 22:01:45 | 2020-12-23 22:01:45 |
+------+------------+----------+---------------------+---------------------+
1 row in set (0.00 sec)

簡單介紹幾個時間的函數。

SELECT CURTIME(); 獲取當前時間
+-----------+
| CURTIME() |
+-----------+
| 22:02:44  |
+-----------+

SELECT CRUDATE(); 獲取當前日期
+------------+
| CURDATE()  |
+------------+
| 2020-12-23 |
+------------+

SELECT NOW(); 獲取當前日期+時間
+---------------------+
| NOW()               |
+---------------------+
| 2020-12-23 22:03:54 |
+---------------------+

SELECT DAYOFWEEK(B) FROM func; 返回日期為星期幾
(1 = Sunday, 2 = Monday, ., 7 = Saturday...)
+--------------+
| DAYOFWEEK(B) |
+--------------+
|            5 |
+--------------+

SELECT DAYOFMONTH(B) FROM func; 返回月份日期的天數
+---------------+
| DAYOFMONTH(B) |
+---------------+
|            10 |
+---------------+

SELECT DAYOFYEAR(B) FROM func; 返回此日期為當年的第幾天
+--------------+
| DAYOFYEAR(B) |
+--------------+
|          345 |
+--------------+

SELECT MONTHNAME(B) FROM func; 返回日期星期名稱
+--------------+
| MONTHNAME(B) |
+--------------+
| December     |
+--------------+

DATE_FORMAT 格式化我們的日期，輸入不同值，日期會以不同方式呈現。
ex : %D 可以轉換日為( 1號 11號..=> st / 2號 12號 ..=> nd / 3號 13號.. => rd /其餘 => th )
%M 顯示月份名稱 %Y顯示年分 等等....

SELECT DATE_FORMAT(hire_date,"%D %M %Y") FROM employee WHERE id="2";
+-----------------------------------+
| DATE_FORMAT(hire_date,"%D %M %Y") |
+-----------------------------------+
| 10th December 2015                |
+-----------------------------------+