先上结论
不同
- timestamp without time zone:
插入时将时间字符串作为零时区的时间转为时间戳并存入
查询时将时间戳转为零时区的时间字符串 - timestamp with time zone:
插入时将时间字符串作为数据库时区的时间转为时间戳并存入
查询时将时间戳转为数据库时区的时间字符串
相同
- 在数据库内部都是以一个不变的时间戳数字存储的, 不论数据库的时区如何变化, 存储的值不会发生改变.
验证(举例说明)
// 准备
CREATE TABLE IF NOT EXISTS public.test_timezone
(
aaa timestamp(6) without time zone,
bbb timestamp with time zone
)
set timezone='Asia/Shanghai';
show timezone; // Asia/Shanghai
// 插入数据
insert into test_timezone values('2021-11-01 00:00:00', '2021-11-01 00:00:00');
// 验证不同点:
// 检查插入的数据的真实时间戳
select floor(extract(epoch from (select aaa from test_timezone order by aaa desc limit 1)))
// 结果: 1635724800, 在浏览器里打开console直接 new Date(1635724800000)得到Mon Nov 01 2021 08:00:00 GMT+0800 (中国标准时间)
select floor(extract(epoch from (select bbb from test_timezone order by bbb desc limit 1)))
// 结果: 1635696000000, Mon Nov 01 2021 00:00:00 GMT+0800 (中国标准时间)
select * from test_timezone;
// 结果: 2021-11-01 00:00:00 | 2021-11-01 00:00:00+08
// 验证相同点
set timezone='UTC';
show timezone; // UTC
select floor(extract(epoch from (select aaa from test_timezone order by aaa desc limit 1)))
// 结果: 还是1635724800, 还是Mon Nov 01 2021 08:00:00 GMT+0800 (中国标准时间)
select floor(extract(epoch from (select bbb from test_timezone order by bbb desc limit 1)))
// 结果: 也还是1635696000, Mon Nov 01 2021 00:00:00 GMT+0800 (中国标准时间)
// 进一步验证不同点
select * from test_timezone;
// 结果: 2021-11-01 00:00:00 2021-10-31 16:00:00+00
纠正网上的错误结论
网上有一篇博客之前对我产生了误导, 这里也贴上说明一下:
名字上看一个是带时区的,另一个是不带时区的,查出来的时间是一样的,只是一个带时区标志,一个不带而已,时区的基准是格林威治时间UTC。
…
(跳到论证阶段)
…
select now()::timestamp with time zone, now()::timestamp without time zone;
文中用"这条sql的输出在任何情况(数据库时区在任意时区)下, 查询结果的两个字段都是一样的值"来证明这个结论是不妥的, 因为now()本身是timestamp with time zone时间类型, 而now()::timestamp without time zone则是将原时间戳强制转换类型, 这个转换改变了时间戳值. 因此两个字段的返回结果虽然看起来一样, 实际正如上面论证, 两个时间的内部时间戳是不一样的.
下面也给出如何证明它的错误:
set timezone='Asia/Shanghai';
insert into test_timezone values((select now()::TIMESTAMP without time zone), (select now()));
// 实际时间: Tue Jan 11 2022 20:07:03
select floor(extract(epoch from (select aaa from test_timezone order by aaa desc limit 1)))
// 1641931874 Wed Jan 12 2022 04:11:14 GMT+0800 (中国标准时间)
select floor(extract(epoch from (select bbb from test_timezone order by bbb desc limit 1)))
// 1641903074 Tue Jan 11 2022 20:11:14 GMT+0800 (中国标准时间)
拓展Java
如果用timestamp without time zone, 正确但不舒服的做法是Java里用Instant类型对应, 并在格式化(比如JsonFormat)时设置时区为上海时区, 插入数据时直接使用Instant.now(). 不舒服是因为数据库管理工具比如navicat里, 查询出来的数据全部都是少8小时的.
不正确但舒服的方法是, Java还是Instant, 但格式化时使用零时区, 插入数据时使用Instant.now().plusHours…
如果用timestamp with time zone, 额, 项目上还没用过, 下班了下班了