MySQL 时间类型资料之基本操作

首先我们简单介绍一些时间类型。

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                |+-----------------------------------+