MySQL与openGauss 时间操作比较分析

在进行数据迁移校验时，发现mysql和opengauss 对时间处理不一致问题，针对时间处理我们采用了多种处理方案，对问题的处理过程我们进行了总结和记录。

仰望星空@脚踏实地

2800人浏览 · 2022-08-27 12:01:59

仰望星空@脚踏实地 · 2022-08-27 12:01:59 发布

表创建以及数据打桩

-- mysql
CREATE TABLE `a` (
      `b_datetime` DATETIME(6) NULL DEFAULT NULL
)
COLLATE='utf8mb4_0900_ai_ci'
ENGINE=InnoDB
;

INSERT INTO a VALUES('2022-07-19 11:44:50.674000');
INSERT INTO a VALUES(NULL);

-- opengauss

CREATE TABLE b (
    b_datetime timestamp without time zone
)
WITH (orientation=row, compression=no);    
INSERT INTO b (b_datetime)VALUES (timestamp '2022-07-19  11:44:50.674000');
INSERT INTO a VALUES(NULL)


-- mysql时间字段datetime 迁移到opengauss 时间字段类型为timestamp

原始数据查询

#MySQL
select * from a;
+----------------------------+
| b_datetime                 |
+----------------------------+
| 2022-07-19 11:44:50.674000 |
| NULL                       |
+----------------------------+
2 rows in set (0.00 sec)
===============================================================
#openGauss
select * from test.a;
       b_datetime
-------------------------
2022-07-19 11:44:50.674


(2 rows)

查询结果：

mysql 时间输出年-月-日时:分:秒.微妙

opengauss 时间输出年-月-日时:分:秒.微妙 (微妙低位0被抹除)

TO_CHAR与DATE_FORMAT 时间转换为字符串格式化

#MySQL
SELECT DATE_FORMAT(`b_datetime`, '%Y-%m-%d %H:%i:%s.%f') from `test`.`a`;
+---------------------------------------------------+
| DATE_FORMAT(`b_datetime`, '%Y-%m-%d %H:%i:%s.%f') |
+---------------------------------------------------+
| 2022-07-19 11:44:50.674000                        |
| NULL                                              |
+---------------------------------------------------+
2 rows in set (0.00 sec)
===============================================================
#openGauss
SELECT  to_char(b_datetime, 'YYYY-MM-DD HH24:MI:SS.FF') from "test"."a" ;
          to_char
----------------------------
2022-07-19 11:44:50.674000

(2 rows)

查询结果：时间查询结果一致，但是双方对NULL处理方式产生差异。

时间转字符串，对NULL值进行格式化处理（0000-00-00 00:00:00）

#MySQL
select  IFNULL(b_datetime, CAST('0000-00-00 00:00:00' AS date)) from `test`.`a`;
+---------------------------------------------------------+
| IFNULL(b_datetime, CAST('0000-00-00 00:00:00' AS date)) |
+---------------------------------------------------------+
| 2022-07-19 11:44:50.674000                              |
| NULL                                                    |
+---------------------------------------------------------+
2 rows in set, 1 warning (0.00 sec)

IFNULL(v1,v2) ：如果 v1 的值不为 NULL，则返回 v1，否则返回 v2。 懵逼了

select  IFNULL(b_datetime, '0000-00-00 00:00:00') from `test`.`a`;
+-------------------------------------------+
| IFNULL(b_datetime, '0000-00-00 00:00:00') |
+-------------------------------------------+
| 2022-07-19 11:44:50.674000                |
| 0000-00-00 00:00:00                       |
+-------------------------------------------+
2 rows in set (0.00 sec)


IFNULL 内部不进行类型转换 去掉CAST函数 OK了 ，赞一下。

SELECT DATE_FORMAT(IFNULL(b_datetime, '0000-00-00 00:00:00'), '%Y-%m-%d %H:%i:%s.%f') from `test`.`a`;
+--------------------------------------------------------------------------------+
| DATE_FORMAT(IFNULL(b_datetime, '0000-00-00 00:00:00'), '%Y-%m-%d %H:%i:%s.%f') |
+--------------------------------------------------------------------------------+
| 2022-07-19 11:44:50.674000                                                     |
| NULL                                                                           |
+--------------------------------------------------------------------------------+
2 rows in set, 1 warning (0.00 sec)

DATE_FORMAT(IFNULL()) 格式化 错误，未到预期结果，郁闷下。DATE_FORMAT 格式化0000-00-00 00:00:00时间返回NULL

SELECT if(`b_datetime` is NULL, '0000-00-00 00:00:00' , DATE_FORMAT(`b_datetime`, '%Y-%m-%d %H:%i:%s.%f')) datetime from `test`.`a`;
+----------------------------+
| datetime                   |
+----------------------------+
| 2022-07-19 11:44:50.674000 |
| 0000-00-00 00:00:00        |
+----------------------------+
2 rows in set (0.00 sec)

使用if(,,)函数，目标达成。赞美自己

===============================================================
#openGauss

SELECT  nvl(to_char(b_datetime, 'YYYY-MM-DD HH24:MI:SS.FF') ,'0000-00-00 00:00:00') datetime from "test"."a" ;
          datetime
----------------------------
2022-07-19 11:44:50.674000
0000-00-00 00:00:00
(2 rows)

#备注
NULLIF(value1,value2)函数：在mysql和opengauss中都存在，用法一致。只有当value1和value2相等时，NULLIF才返回NULL。否则它返回value1。
IFNULL 函数只有MySQL有，IFNULL(v1,v2) 如果 v1 的值不为 NULL，则返回 v1，否则返回 v2。
NVL函数只有opengauss有，NVL(value1,value2) 如果value1为NULL则返回value2，如果value1非NULL，则返回value1。

日期转为时间戳

#MySQL
SELECT UNIX_TIMESTAMP(b_datetime),b_datetime from `test`.`a`;
+----------------------------+----------------------------+
| UNIX_TIMESTAMP(b_datetime) | b_datetime                 |
+----------------------------+----------------------------+
|          1658202290.674000 | 2022-07-19 11:44:50.674000 |
|                       NULL | NULL                       |
+----------------------------+----------------------------+
2 rows in set (0.00 sec)

SELECT UNIX_TIMESTAMP(b_datetime)*1000,b_datetime from `test`.`a`;
+---------------------------------+----------------------------+
| UNIX_TIMESTAMP(b_datetime)*1000 | b_datetime                 |
+---------------------------------+----------------------------+
|            1658202290674.000000 | 2022-07-19 11:44:50.674000 |
|                            NULL | NULL                       |
+---------------------------------+----------------------------+
2 rows in set (0.00 sec)

SELECT UNIX_TIMESTAMP(b_datetime)*1000000,b_datetime from `test`.`a`;
+------------------------------------+----------------------------+
| UNIX_TIMESTAMP(b_datetime)*1000000 | b_datetime                 |
+------------------------------------+----------------------------+
|            1658202290674000.000000 | 2022-07-19 11:44:50.674000 |
|                               NULL | NULL                       |
+------------------------------------+----------------------------+
2 rows in set (0.00 sec)

===============================================================
#openGauss

select extract(epoch from b_datetime) datetime,b_datetime from "test"."a";
    datetime    |       b_datetime
----------------+-------------------------
1658231090.674 | 2022-07-19 11:44:50.674
                |
(2 rows)

select extract(epoch from b_datetime)*1000 datetime,b_datetime from "test"."a";
   datetime    |       b_datetime
---------------+-------------------------
1658231090674 | 2022-07-19 11:44:50.674
               |
(2 rows)

select extract(epoch from b_datetime)*1000000 datetime,b_datetime from "test"."a";
      datetime      |       b_datetime
--------------------+-------------------------
1.658231090674e+15 | 2022-07-19 11:44:50.674
                    |
(2 rows)

# 数据分析：

opengauss在微妙转换时，达到16位会进行数据转换，转为科学计数

另mysql和opengauss 再讲时间转换为毫秒时，产生数据差异：

opengauss 1658231090674

mysql 1658202290674.000000

select (1658231090674 - 1658202290674) time_diff ,(1658231090674 - 1658202290674)/(1000*60*60*8) _8hour;
time_diff | _8hour
-----------+--------
  28800000 |      1
(1 row)

opengauss 和mysql 转换时间戳，基于时区处理。这里要考虑数据库时间的时区属性。