使用chameleon工具进行数据迁移，mysql列类型timestamp会被转换为timestamp without time zone
timestamp和timestamptz是openGauss中用来表示日期和时间的数据类型，其中timestamptz是timestamp with time zone的别名，即带时区信息的timestamp。
这两个数据类型在openGauss中存储时都占用8字节的存储空间。
opengauss最新版本3.1.1，timestamp默认就是with time zone和timestamptz几乎相同（timestamp(0)和timestamp的区别）
timestamp with time zone在进行select查询时，会转换成当前session对应的时区的时间，后面显示的时区也会跟着变化
timestamp要想不加时区就得额外声明WITHOUT TIME ZONE
TIMESTAMP[§] [WITHOUT TIME ZONE]
日期和时间
p表示小数点后的精度，取值范围为0~6
8字节
TIMESTAMP[§] [WITH TIME ZONE]
日期和时间，带时区。TIMESTAMP的别名为TIMESTAMPTZ
p表示小数点后的精度，取值范围为0~6
8字节
1、创建一张表
create table timestamp_example(
id integer primary key,
enter_time timestamp,
leave_time timestamptz
);
2、查看表信息
database_test=> \d+ timestamp_example
Table "joe.timestamp_example"
Column | Type | Modifiers | Storage | Stats target | Description
------------+-----------------------------+-----------+---------+--------------+-------------
id | integer | not null | plain | |
enter_time | timestamp(0) with time zone | | plain | |
leave_time | timestamp with time zone | | plain | |
Indexes:
"timestamp_example_pkey" PRIMARY KEY, btree (id) TABLESPACE pg_default
Has OIDs: no
Options: orientation=row, compression=no
#可以看到TIMESTAMP默认情况下，创建的是timestamp(0) with time zone带时区的
3、插入数据
insert into timestamp_example values (1, now(), now());
database_test=> select * from timestamp_example;
id | enter_time | leave_time
----+------------------------+------------------------
1 | 2023-06-28 00:58:10+08 | 2023-06-28 00:58:10+08
(1 row)
4、查询数据
database_test=> select * from timestamp_example where enter_time='2023-06-28 00:58:10+08';
id | enter_time | leave_time
----+------------+------------
(0 rows)
database_test=> select * from timestamp_example where enter_time='2023-06-28 00:58:10';
id | enter_time | leave_time
----+------------------------+------------------------
1 | 2023-06-28 00:58:10+08 | 2023-06-28 00:58:10+08
(1 row)
5、总结
虽然显示的时候+08时区
但是真正查询的时候匹配的还是不加时区的数据
1、建表
drop table if exists timestamp_example;
create table timestamp_example(
id integer primary key,
first_time timestamp without time zone,
second_time timestamp,
three_time timestamptz
);
2、查看表信息
database_test=> \d+ timestamp_example
Table "joe.timestamp_example"
Column | Type | Modifiers | Storage | Stats target | Description
-------------+-----------------------------+-----------+---------+--------------+-------------
id | integer | not null | plain | |
first_time | timestamp without time zone | | plain | |
second_time | timestamp(0) with time zone | | plain | |
three_time | timestamp with time zone | | plain | |
Indexes:
"timestamp_example_pkey" PRIMARY KEY, btree (id) TABLESPACE pg_default
Has OIDs: no
Options: orientation=row, compression=no
3、插入数据
insert into timestamp_example values (1, now(), now(), now());
insert into timestamp_example values (2, now(), now(), now());
insert into timestamp_example values (3, now(), now(), now());
4、查看数据
database_test=> select * from timestamp_example;
id | first_time | second_time | three_time
----+---------------------+------------------------+------------------------
1 | 2023-06-28 01:27:34 | 2023-06-28 01:27:34+08 | 2023-06-28 01:27:34+08
2 | 2023-06-28 01:27:34 | 2023-06-28 01:27:34+08 | 2023-06-28 01:27:34+08
3 | 2023-06-28 01:27:35 | 2023-06-28 01:27:35+08 | 2023-06-28 01:27:35+08
(3 rows)
#可以看到不声明timestamp without time zone的都显示加了时区
5、查看一下存储空间
database_test=> select sum(pg_column_size(first_time)) as first_size,sum(pg_column_size(second_time)) as
second_size,sum(pg_column_size(three_time)) as three_size from timestamp_example;
first_size | second_size | three_size
------------+-------------+------------
24 | 24 | 24
(1 row)
#可以看到3者占据的存储空间是一致的
6、session临时修改时区
database_test=> SET TIMEZONE='Asia/Shanghai';
SET
database_test=> select * from timestamp_example;
id | first_time | second_time | three_time
----+---------------------+------------------------+------------------------
1 | 2023-06-28 01:27:34 | 2023-06-28 01:27:34+08 | 2023-06-28 01:27:34+08
2 | 2023-06-28 01:27:34 | 2023-06-28 01:27:34+08 | 2023-06-28 01:27:34+08
3 | 2023-06-28 01:27:35 | 2023-06-28 01:27:35+08 | 2023-06-28 01:27:35+08
(3 rows)
database_test=> SET TIMEZONE='Asia/Shanghai';
SET
database_test=> show timezone;
TimeZone
---------------
Asia/Shanghai
(1 row)
database_test=> select * from timestamp_example;
id | first_time | second_time | three_time
----+---------------------+------------------------+------------------------
1 | 2023-06-28 01:27:34 | 2023-06-28 01:27:34+08 | 2023-06-28 01:27:34+08
2 | 2023-06-28 01:27:34 | 2023-06-28 01:27:34+08 | 2023-06-28 01:27:34+08
3 | 2023-06-28 01:27:35 | 2023-06-28 01:27:35+08 | 2023-06-28 01:27:35+08
(3 rows)
database_test=> SET TIMEZONE='Asia/Tokyo';
SET
database_test=> show timezone;
TimeZone
------------
Asia/Tokyo
(1 row)
database_test=> select * from timestamp_example;
id | first_time | second_time | three_time
----+---------------------+------------------------+------------------------
1 | 2023-06-28 01:27:34 | 2023-06-28 02:27:34+09 | 2023-06-28 02:27:34+09
2 | 2023-06-28 01:27:34 | 2023-06-28 02:27:34+09 | 2023-06-28 02:27:34+09
3 | 2023-06-28 01:27:35 | 2023-06-28 02:27:35+09 | 2023-06-28 02:27:35+09
(3 rows)
7、退出重新登录
[omm@gsdb01 dn01]$ gsql -d database_test -p 26000 -r -U joe -W Mysql@123456
gsql ((openGauss 3.1.1 build 70980198) compiled at 2023-01-06 09:27:09 commit 0 last mr )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.
database_test=> show timezone;
TimeZone
----------
PRC
(1 row)
database_test=> select * from timestamp_example;
id | first_time | second_time | three_time
----+---------------------+------------------------+------------------------
1 | 2023-06-28 01:27:34 | 2023-06-28 01:27:34+08 | 2023-06-28 01:27:34+08
2 | 2023-06-28 01:27:34 | 2023-06-28 01:27:34+08 | 2023-06-28 01:27:34+08
3 | 2023-06-28 01:27:35 | 2023-06-28 01:27:35+08 | 2023-06-28 01:27:35+08
(3 rows)
8、总结
可以看到timestamp with time zone的数据，在进行select查询时会转换成当前session对应的时区的时间
