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

<<:  pug 基础介绍(1) DAY99

>>:  Day 24 - 天眼CNN 的耳朵和嘴巴 - BERT

爬虫怎麽爬 从零开始的爬虫自学 DAY26 python网路爬虫开爬7-规模扩大

前言 各位早安，书接上回我们将程序码成功加上储存的功能，我们今天要来使它的规模更大更方便使用 开爬-...

Day 30 ~ AI从入门到放弃 - 结语

从刚开始教大家使用Google Colaboratory，也介绍了模型的训练，资料预处理等基础，相信...

Day24【Web】网路传输协定：TCP 与 UDP

TCP，Transmission Control Protocol 中文为「传输控制协定」 UDP，...

DAY01前言:有关30天挑战

一、心得分享: 本人目前持续学习数据分析和AI当中,包括常见的机器学习和深度学习,在学习过程中,才知...

37.use API with Axios

首先，我们要通过 npm/Yarn 或一个 CDN 链接安装 axios。 我们首先创建一个 dat...