PostgreSQL 时区问题

PostgreSQL时区

SQL 标准通过"+"或者"-"是否存在来区分 timestamp without time zone 和 timestamp with time zone 文本。因此，

TIMESTAMP '2021-03-06 18:02:00'

是一个 timestamp without time zone，而

TIMESTAMP '2021-03-06 18:02:00 +08'

是一个 timestamp with time zone。

+08：表示 时区与全球统一时间 UTC 偏移量为 8 小时

AT TIME ZONE 构造允许把时间戳转换成不同的时区与timezone(zone, timestamp) 函数等效

SELECT current_timestamp AT TIME ZONE 'HKT'
-- 2021/3/6 18:17:26.277
SELECT current_timestamp AT TIME ZONE 'MST'
-- 2021/3/6 3:17:49.921
SELECT timezone('HKT', current_timestamp)
-- 2021/3/6 18:17:26.277
SELECT timezone('MST', current_timestamp)
-- 2021/3/6 3:17:49.921

PostgreSQL时间格式化函数

to_char 将时间戳转成字符串

SELECT to_char(current_timestamp, 'YYYY-MM-DD HH24:MI:SS');
-- 2021-03-06 17:03:45

to_char 可以获取时区信息

TZ	大写时区缩写 (only supported in to_char)
tz	小写时区缩写 (only supported in to_char)
TZH	时区 hours
TZM	时区 minutes
OF	时区与UTC的偏移量 (only supported in to_char)

SELECT to_char(current_timestamp, 'TZ | tz | TZH | TZM | OF')
-- HKT | hkt | +08 | 00 | +08

to_date 将时间戳转成字符串

SELECT to_date('2021-3-6', 'YYYY-MM-DD');
-- 2021/3/6

to_timestamp 将时间戳转成字符串

SELECT to_timestamp('2021-3-6 19:02:00 +08', 'YYYY-MM-DD HH24:MI:SS TZH');
-- 2021/3/6 19:02:00

那么如何修改数据库时区呢？

查看时区

show time zone;

查看可选时区

select * from pg_timezone_names;

设置时区

set time zone 'Japan';

 通过这种方式设置时区只是暂时有效，重新连接数据库后会发现又恢复到原来的时区了。

永久修改：打开 PostgreSQL 配置文件 C:\Program Files\PostgreSQL\13\data\postgresql.conf

修改 timezone

 

重启 PostgreSQL 服务

使用 moment 进行日期格式化时的时区问题

const moment = require('moment');
console.log(moment().format());
console.log(moment().format('YYYY-MM-DD HH:mm Z'));
// 2021-03-06T20:13:54+08:00
// 2021-03-06 20:13 +08:00

默认自动带时区，加 Z 也可带时区。

案例：

这是项目上遇到的问题，主要是数据库时区是 UTC，然后在 where 条件执行 created_at > '2021/3/10 1:00:25.437' 下面时，和预想的数据不一样。

这是表结构：

create table image (
  id serial not null
  , title character varying(255)
  , url character varying(255)
  , created_at timestamp(6) with time zone not null
  , updated_at timestamp(6) with time zone not null
  , primary key (id)
);

数据如下：

id	title	url	created_at	updated_at
1	百度	HTTPS://WWW.BAIDU.COM	2021/3/3 8:50:46.417	2021/3/3 8:50:46.417
2	CSDN	HTTPS://WWW.CSDN.NET/	2021/3/10 2:00:25.437	2021/3/10 2:00:25.437
3	ES6	HTTPS://ES6.RUANYIFENG.COM/	2021/3/10 16:50:46.442	2021/3/10 16:50:46.442

SQL 如下： 

select
    id
    , title
    , url
    , to_char(created_at, 'YYYY-MM-DD HH24:MI:SS TZH') AS created_at 
from
    image 
where
    created_at > '2021/3/10 1:00:25.437'

查询结果：

id	title	url	created_at
3	ES6	HTTPS://ES6.RUANYIFENG.COM/	2021-03-10 08:50:46 +00

可以发现只检索出了 ES6，这是因为检索时是按 UTC 时间检索的，数据库显示时间是 2021/3/10 16:50:46.442 但实际时间是 2021-03-10 08:50:46 +00

解决办法：

追加时区 created_at > '2021/3/10 1:00:25.437 +08'

select
    id
    , title
    , url
    , to_char(created_at, 'YYYY-MM-DD HH24:MI:SS TZH') AS created_at 
from
    image 
where
    created_at > '2021/3/10 1:00:25.437 +08'

id	title	url	created_at
3	ES6	HTTPS://ES6.RUANYIFENG.COM/	2021-03-10 08:50:46 +00
2	CSDN	HTTPS://WWW.CSDN.NET/	2021-03-09 18:00:25 +00