mysql的日期格式

前言

mysql作为常用的关系型数据库，日期是大家在日常中必不可少的一种格式，本文就 mysql支持的日期格式做一个简要的说明，如若有不对的地方，欢迎各位指正！

目的

测试mysql的两种日期格式

1. TIMESTAMP
2. DATETIME

环境准备

1. mysql:8.0.26
2. docker
3. Datagrip

搭建mysql

本人选用了docker-compose作为启动脚本，如果想要了解 springboot 项目如何通过 Dockerfile 文件构建docker镜像的，可以看下我的其他博客

version: "3.0"
services:
 mysql:
  container_name: mysql
  image: mysql:8.0.26
  ports:
   - "3306:3306"
  environment:
   MYSQL_ROOT_PASSWORD: root123
   # TZ: Asia/Shanghai
   # TZ: Asia/Tokyo
  volumes:
   - ./mysql/data:/var/lib/mysql
   - ./mysql/my.cnf:/etc/mysql/conf.d/my.cnf
   - ./mysql/executedsql/sql:/opt/sql
   - ./mysql/initsql:/docker-entrypoint-initdb.d/
  restart: on-failure

注意:

1. 上面是没有设置时区, 言下之意是 使用的默认时区=UTC

2. 如果想要设置 数据库带时区的，可以通过 TZ: Asia/Shanghai来设置时区（TZ: Asia/Shanghai就是常用的 东八区时间）

3. 可以通过命令行的方式，查看 当前数据库的时区到底是什么

   SHOW VARIABLES LIKE '%zone%';
   

   结果:

   

time_zone：就是数据库的时区，这个参数会影响 SELECT now(); 或者 SELECT current_timestamp();出来的结果

创建SQL脚本

DROP TABLE IF EXISTS test_time;
CREATE TABLE IF NOT EXISTS test_time (
    id          BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY COMMENT '主键',
    create_time TIMESTAMP(3) COMMENT '带时区格式的日期',
    update_time DATETIME COMMENT '不带时区格式的日期',
    time1 TIMESTAMP(3) DEFAULT current_timestamp(3) ON UPDATE current_timestamp(3) COMMENT '带时区的自动更新的日期'
) COMMENT '测试数据库时间格式';

测试

因为SQL脚本中规定了 time1的时间是自动变动的，所以接下来的测试中，都不会自己主动设置这个字段的值

1. 数据库使用 UTC 时区

   INSERT INTO test_time (create_time, update_time, time1) VALUES ('2021-08-14 16:23:35', '2021-08-14 16:23:37');
   

   结果:

   

   注意:

   1. 发现time1 的时间 比 create_time 少 8个小时，可以得出两个结论：① TIMESTAMP 是带时区的 ② SELECT current_timestamp() 得出的是 数据库所在时区的时间

2. 将数据库时间切换为 东八区时间

   # 切换命令
   SET TIME_ZONE ='+8:00';
   
   # 查看下时区
   SHOW VARIABLES LIKE '%zone%';
   

   结果:

   

   查看下之前的数据,结果如下:

   

   注意:

   1. create_time 比之前加了 8小时
   2. update_time 没变
   3. time1 的时间，获取的是 当前时间，即 SELECT current_timestamp();结果集

   结论:

   ① TIMESTAMP 是带时区的，会转换成 UTC 格式进行保存

   ② DATETIME 是不带时区的，存进去什么值就是什么值

   ③ TIMESTAMP 会随着数据库所在的时区变化，而改变搜索出来的结果集，即 会进行时区转换

   ④ 如果使用 ON UPDATE current_timestamp(3) ，这个字段的时间会 获取 数据库所在时区的时间 而改变这个值
   此处存在坑。如果将数据库时区更换了，更新完数据后，再更换数据库时区，在更新数据，此时，时间会给人错觉，时间一会超前一会滞后的，切记，切记

结论

1、TIMESTAMP 是带时区的,数据存储的时候，会按照 当前存储的时区换算成 UTC时区存储。然后 再输出 查询时的 数据库所在时区 的结果

例如:
1、存储时：东八区
存储的值: time-8

2、查询时: 东九区
显示值: time-8+9

2、DATETIME 是不带时区的，存进去是什么就是什么，不会随着时区的变化，而改变输出的结果