【MySQL】解决mysql出现的时区问题

前言

目录

1.mysql时区及时间戳概念
2.BRIGHTHOUSE引擎的表概念
3.bug案例分析

Mysql时区及时间戳概念

要记住，时间与时区有关，但是时间戳与时区是没有关系的，时间戳都是一致性的，只不过因为时区的不一致导致展示的时间是不一样

UTC/时间戳的概念

utc的概念的话直接看这个文章：https://blog.csdn.net/weixin_39759989/article/details/111811252?utm_medium=distribute.pc_relevant.none-task-blog-baidujs_title-4&spm=1001.2101.3001.4242
也没什么必要重新再写了

mysql时区的概念

在本地使用的话，系统默认使用的是utc+8的时区，就是北京时间
在mysql中，若用timestamp类型来存储的话，默认是存储utc时区的时间戳，但是在展示的时候，会默认使用当前系统的时区来展示时间，如存储的时间戳为1356969600，这个时候会默认以utc+8的时区来展示，就是2013-01-01
查看mysql时区的sql语句如下

mysql> show variables like "%time_zone";
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| system_time_zone | CEST   |
| time_zone        | SYSTEM |
+------------------+--------+

BRIGHTHOUSE引擎的表概念

    对于mysql表引擎的概念，大家就看看这篇博文，写得挺好的：https://www.cnblogs.com/sunsky303/p/8274586.html。
我就懒得复制黏贴了，还是自己写原创好一点。
这里的话主要说一下
  BRIGHTHOUSE引擎的概念：
    brighthouse是infobright数据库的关键引擎。infobright 数据库是基于mysql的，它的设计主要是用于大规模的数据仓库和分析优化。(等下业务场景就要说下infobright了)
    基本上用的就是infobright的社区版，对于社区版的话，社区版的不支持 insert,update,delete对数据操作和alter对结构操作，数据存储高压缩一般为18：1，不能与MyISAM,InnoDB等引擎进行连接查询 , 数据必须使用 LOAD 方式写入。
但是有一个优点，就是比inndb快几十倍！

bug案例分析

当前有一个业务场景，客户有一张在mysql的基础表，有个功能叫抽取到高速表(高速表自身配置)，这里配置了infobright为高速表，然后将mysql的基础表数据抽取出来，插入到高速表中，之后的查询都从高速表中获取，从而达到快速的查询数据
mysql中有一列为(使用datetime存储)

+------------------+--------+
| date             | Value  |
+------------------+--------+
| orderdate        | 2013-01-01 00:00:00|
+------------------+--------+

抽取到infobright中为(使用timestamp存储)

+------------------+--------+
| date             | Value  |
+------------------+--------+
| orderdate        | 2013-01-01 00:00:00|
+------------------+--------+

可以看到显示还是为 2013-01-01 00:00:00 (主要是因为显示时会默认以当前时区展示时间)

转化为时间戳时看以下图例分享

然后发现一个问题：

1、在普通的mysql引擎如InnoDB下的话，无论是否嵌套或嵌套多少层，时间戳都为：1356969600(2013-01-01)

2、若使用BRIGHTHOUSE引擎的话，只有一个查询的时候，时间戳为1356969600(2013-01-01)，若orderdate在内嵌查询中，外层查询查询到的时间戳就会为1356940800(2012-12-31 16:0:0)

根据以上问题猜测，有可能在查询时间戳的时候，在BRIGHTHOUSE引擎中，一个查询查询时间戳会使用mysql设置的时区(当前为系统时区)，若使用了外层查询的时候，直接使用了utc时区

所以感觉这也可以解析一个问题，为什么直接查询时候，dateTime转换时间戳是2013年(utc +8)，而在外层查询的时候dateTime转换的时间戳是2012年(utc)。因为在外层默认使用了utc，导致了时间戳少8个小时，转换时间也出现了问题。

这个回答的话：https://stackoverflow.com/questions/409286/should-i-use-the-datetime-or-timestamp-data-type-in-mysql。有回答到时间戳是以utc来存储，通过函数获取的话会根据当前时区获取时间戳。但是至于为什么BRIGHTHOUSE引擎外层查询使用函数的的时候会默认使用utc，这个就没搞懂mysql原理了

解决方案

其实对于解决方案的话，没什么太好的解决方案(除非往深层跟到mysql的原理)
然后就使用了如下方案解决
使用CONVERT_TZ函数去获取时间，先将时间转化为当前时区，使获取的时间是当前时区的时间。就如上面的sql