DBA MySQL时间类型
基本介绍
类型一览
MySQL中所有的关于时间的类型，如下表所示：
| 日期时间类型 | 占用空间 | 日期格式 | 最小值 | 最大值 | 零值表示 | 描述 |
|---|---|---|---|---|---|---|
| DATETIME | 8 bytes | YYYY-MM-DD HH:MM:SS | 1000-01-01 00:00:00 | 9999-12-31 23:59:59 | 0000-00-00 00:00:00 | 获取年月日时分秒毫秒 |
| TIMESTAMP | 4 bytes | YYYY-MM-DD HH:MM:SS | 1970-01-01 08:00:01 | 2038-01-19 03:14:07 | 00000000000000 | 获取年月日时分秒毫秒 |
| DATE | 4 bytes | YYYY-MM-DD | 1000-01-01 | 9999-12-31 | 0000-00-00 | 获取年月日 |
| TIME | 3 bytes | HH:MM:SS | -838:59:59 | 838:59:59 | 00:00:00 | 获取时分秒 |
| YEAR | 1 bytes | YYYY | 1901 | 2155 | 0000 |
MySQL保存日期格式使用YYYY-MM-DD HH:MM:SS的ISO 8601标准。
向数据表储存日期与时间必须使用ISO格式。
DATATIME&TIMESTAMP
在实际应用的很多场景中，MySQL的这两种日期类型都能够满足我们的需要，存储精度都为秒，但在某些情况下，会展现出他们各自的优劣。下面就来总结一下两种日期类型的区别。
| DATATIME | TIMESTAMP | |
|---|---|---|
| 时间范围 | 1001年——9999年 | 1970年——2038年 |
| 时区依赖 | 存储时间与时区无关，显示值也与时区无关 | 存储时间与时区有关，显示值也与时区有关 |
| 存储空间 | 8字节 | 4字节 |
| 默认值 | 5.6.5之前是null，之后也是CURRENT_TIMESTAMP()函数 | 当前时间，CURRENT_TIMESTAMP()函数，并且每次update操作时都会自动更新时间 |
常用操作
自动生成&自动更新
对于时间类型的字段，如果我们想要在创建表和更新记录时让其自动更新，可以用以下方式进行创建表：
# 时间类型与外键约束的字段都拥有ON UPDATE的字段选项
# 也可以使用NOW()函数，加括号。
CREATE TABLE temp (
id INT PRIMARY KEY AUTO_INCREMENT COMMENT "编号",
ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT "TIMESTAMP，创建时自动填入，更新时自动更新",
dt DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT "DATETIME，创建时自动填入，更新时自动更新"
) ENGINE innodb CHARSET utf8mb4 COLLATE utf8mb4_general_ci;
这样的话操作起来就会自动创建时间以及自动更新：
# 插入数据
M > INSERT INTO temp(id) VALUES (1),(2),(3);
# 自动插入当前时间
M > SELECT * FROM temp;
+----+---------------------+---------------------+
| id | ts | dt |
+----+---------------------+---------------------+
| 1 | 2021-02-17 00:09:53 | 2021-02-17 00:09:53 |
| 2 | 2021-02-17 00:09:53 | 2021-02-17 00:09:53 |
| 3 | 2021-02-17 00:09:53 | 2021-02-17 00:09:53 |
+----+---------------------+---------------------+
# 自动更新
M > UPDATE temp SET id=11 WHERE id=1;
M > SELECT * FROM temp;
+----+---------------------+---------------------+
| id | ts | dt |
+----+---------------------+---------------------+
| 2 | 2021-02-17 00:09:53 | 2021-02-17 00:09:53 |
| 3 | 2021-02-17 00:09:53 | 2021-02-17 00:09:53 |
| 11 | 2021-02-17 00:11:04 | 2021-02-17 00:11:04 |
+----+---------------------+---------------------+
插入字符串
对时间类型的自动插入字符串时，一定要注意格式必须全部正确才能进行插入。
查询格式化
格式化参数
格式化参数及描述如下表所示：
| 格式 | 描述 |
|---|---|
| %a | 缩写星期名 |
| %b | 缩写月名 |
| %c | 月，数值 |
| %D | 带有英文前缀的月中的天 |
| %d | 月的天，数值(00-31) |
| %e | 月的天，数值(0-31) |
| %f | 微秒 |
| %H | 小时 (00-23) |
| %h | 小时 (01-12) |
| %I | 小时 (01-12) |
| %i | 分钟，数值(00-59) |
| %j | 年的天 (001-366) |
| %k | 小时 (0-23) |
| %l | 小时 (1-12) |
| %M | 月名 |
| %m | 月，数值(00-12) |
| %p | AM 或 PM |
| %r | 时间，12-小时（hh:mm:ss AM 或 PM） |
| %S | 秒(00-59) |
| %s | 秒(00-59) |
| %T | 时间, 24-小时 (hh:mm:ss) |
| %U | 周 (00-53) 星期日是一周的第一天 |
| %u | 周 (00-53) 星期一是一周的第一天 |
| %V | 周 (01-53) 星期日是一周的第一天，与 %X 使用 |
| %v | 周 (01-53) 星期一是一周的第一天，与 %x 使用 |
| %W | 星期名 |
| %w | 周的天 （0=星期日, 6=星期六） |
| %X | 年，其中的星期日是周的第一天，4 位，与 %V 使用 |
| %x | 年，其中的星期一是周的第一天，4 位，与 %v 使用 |
| %Y | 年，4 位 |
| %y | 年，2 位 |
格式化函数
两个用于查询时对时间格式进行格式化的函数。
| 函数 | 描述 |
|---|---|
| DATE_FORMAT(字段，格式化内容) | 对字段进行年月日时分秒的格式化 |
| TIME_FORMAT(字段，格式化内容) | 对字段进行时分秒的格式化 |
操作演示：
M > SELECT date_format(ts,"%Y年%m月%d %H时%i分%s秒"),time_format(dt,"%Y年%m月%d %H时%i分%s秒") FROM temp LIMIT 1;
+------------------------------------------------+------------------------------------------------+
| date_format(ts,"%Y年%m月%d %H时%i分%s秒") | time_format(dt,"%Y年%m月%d %H时%i分%s秒") |
+------------------------------------------------+------------------------------------------------+
| 2021年02月17 00时09分53秒 | 0000年00月00 00时09分53秒 |
+------------------------------------------------+------------------------------------------------+
时间获取
函数介绍
三个不需要传参的函数：
| 函数 | 描述 |
|---|---|
| NOW() | 当前时间，完整的年月日时分秒 |
| CURRENT_DATE() | 当前日期，年月日 |
| CURRENT_TIME() | 当前时间，时分秒 |
其他的需要传参的函数：
| 函数 | 描述 |
|---|---|
| YEAR() | 年（范围从1000 到 9999） |
| MONTH() | 月（范围从1到12） |
| DAY() | 日（范围从1开始） |
| HOUR() | 时（范围从0到23） |
| MINUTE() | 分（范围从0到59） |
| SECOND() | 秒（范围从0到59） |
| TIME() | 只获取时间，即时分秒。 |
| WEEK() | 一年中的第几周，从1开始计数 |
| QUARTER() | 一年中的季度，从1开始计数 |
| DAYOFYEAR() | 一年中的第几天（从1开始） |
| DAYOFMONTH() | 月份中天数（从1开始） |
| DAYOFWEEK() | 星期天（1）到星期六（7） |
| WEEKDAY() | 星期一（0）到星期天（6） |
| TO_DAYS() | 从元年到现在的天数（忽略时间部分） |
| FROM_DAYS() | 根据天数得到日期（忽略时间部分） |
| TIME_TO_SEC() | 时间转为秒数（忽略日期部分） |
| SEC_TO_TIME() | 根据秒数转为时间（忽略日期部分） |
| UNIX_TIMESTAMP() | 根据日期返回秒数（包括日期与时间） |
| FROM_UNIXTIME() | 根据秒数返回日期与时间（包括日期与时间） |
| DATEDIFF() | 两个日期相差的天数（忽略时间部分，前面日期减后面日期） |
| TIMEDIFF() | 计算两个时间的间隔（忽略日期部分） |
| TIMESTAMPDIFF() | 根据指定单位计算两个日期时间的间隔（包括日期与时间） |
| LAST_DAY() | 该月的最后一天 |
当前时间
示例操作，获取当前时间：
M > SELECT NOW();
+---------------------+
| NOW() |
+---------------------+
| 2021-02-17 00:37:04 |
+---------------------+
时间截取
示例演示，获取当前时间中的分钟数：
M > SELECT MINUTE(NOW());
+---------------+
| MINUTE(NOW()) |
+---------------+
| 24 |
+---------------+
示例演示，获取今天是星期几：
M > SELECT DAYOFWEEK(NOW());
+------------------+
| DAYOFWEEK(NOW()) |
+------------------+
| 4 |
+------------------+
时间转换
示例演示，当前时间转秒数：
M > SELECT SEC_TO_TIME(TIME_TO_SEC(NOW())) AS "当前秒数转时间", TIME_TO_SEC(NOW()) AS ”当前时间转秒数“;
+-----------------------+-----------------------------+
| 当前秒数转时间 | ”当前时间转秒数“ |
+-----------------------+-----------------------------+
| 00:39:55 | 2395 |
+-----------------------+-----------------------------+
时间计算
函数介绍
时间计算相关的函数：
| 函数 | 描述 |
|---|---|
| ADDTIME() | 添加时间，负数为减少，只对时间类型有效 |
| TIMESTAMP() | 添加时间，负数为减少，只对时间类型有效 |
| DATE_ADD() | 根据单位添加时间，支持单位有YEAR/MONTH/DAY/HOUR/MINUTE/SECOND/HOUR_MINUTE/DAY_HOUR/DAY_MINUTE/DAY_SECOND/HOUR_MINUTE/HOUR_SECOND（负数时等于DATE_SUB) |
| DATE_SUB() | DATE_ADD的反函数 |
操作演示
示例演示：
# 7小时前的日期
M > SELECT ADDTIME(NOW(), "-7:00:00");
+----------------------------+
| ADDTIME(NOW(), "-7:00:00") |
+----------------------------+
| 2021-02-16 17:44:50 |
+----------------------------+
# 7天后的日期
M > SELECT DATE_ADD(NOW(), INTERVAL 7 DAY);
+---------------------------------+
| DATE_ADD(NOW(), INTERVAL 7 DAY) |
+---------------------------------+
| 2021-02-24 00:45:33 |
+---------------------------------+
# 7天前的日期
M > SELECT DATE_SUB(NOW(), INTERVAL 7 DAY);
+---------------------------------+
| DATE_SUB(NOW(), INTERVAL 7 DAY) |
+---------------------------------+
| 2021-02-10 00:46:12 |
+---------------------------------+
# 20小时10分钟后的日期
M > SELECT DATE_ADD(NOW(),INTERVAL '20:10' HOUR_MINUTE);
+----------------------------------------------+
| DATE_ADD(NOW(),INTERVAL '20:10' HOUR_MINUTE) |
+----------------------------------------------+
| 2021-02-17 20:56:41 |
+----------------------------------------------+
# 2天8小时后的日期
M > SELECT DATE_ADD(NOW(),INTERVAL '2 8' DAY_HOUR);
+-----------------------------------------+
| DATE_ADD(NOW(),INTERVAL '2 8' DAY_HOUR) |
+-----------------------------------------+
| 2021-02-19 08:47:12 |
+-----------------------------------------+
# 获取本月最后一天日期
M > SELECT LAST_DAY(NOW());
+-----------------+
| LAST_DAY(NOW()) |
+-----------------+
| 2021-02-28 |
+-----------------+
# 获取本月第一天日期
M > SELECT DATE_SUB(NOW(),INTERVAL DAYOFMONTH(NOW())-1 DAY);
+--------------------------------------------------+
| DATE_SUB(NOW(),INTERVAL DAYOFMONTH(NOW())-1 DAY) |
+--------------------------------------------------+
| 2021-02-01 00:48:17 |
+--------------------------------------------------+
