表创建以及数据打桩
-- 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:%