时间戳

首页 > 解决方案 > postgeSQL 中的时区

sql - postgeSQL 中的时区

问题描述

我使用 postgeSQL 12.3。默认时区是“美国/太平洋”。当我使用该Now()函数时，postgres 会正确返回当前时间和日期，但时区与我的不同。我通过命令将时区更改为“非洲/开罗”

SET TIMEZONE = 'Africa/Cairo';

之后，我Now()再次调用了该函数。它返回了错误的日期和时间，但时区正确。我该如何解决这个错误？我希望新时间（更改时区后的时间）与旧时间（更改时区之前的时间）相同。换句话说，我只想将时区从 -7 更改为 +02。我应该怎么办？

这是更改时区之前的输出

在此处输入图像描述

这是将时区更改为“非洲/开罗”后的输出

在此处输入图像描述

标签： sqlpostgresql

解决方案

我在“美国/太平洋”，所以：

select now();
              now               
--------------------------------
 07/09/2020 17:23:19.817048 PDT

test(5432)=> SET TIMEZONE = 'Africa/Cairo';
SET

test(5432)=> select now();
              now               
--------------------------------
 07/10/2020 02:24:02.617442 EET
(1 row)

test(5432)=> select now() at time zone  'US/Pacific';
          timezone          
----------------------------
 07/09/2020 17:24:21.577493
(1 row)

更新。时间戳示例，timestamptz：

ts_test 
                        Table "public.ts_test"
 Column |            Type             | Collation | Nullable | Default 
--------+-----------------------------+-----------+----------+---------
 ts_tz  | timestamp with time zone    |           |          | 
 ts     | timestamp without time zone |           |          | 
 ts_txt | character varying           |           |          | 

show timezone;
  TimeZone  
------------
 US/Pacific

test(5432)=# insert into ts_test values(localtimestamp, localtimestamp, localtimestamp);
INSERT 0 1
test(5432)=# select * from ts_test ;
             ts_tz              |             ts             |           ts_txt           
--------------------------------+----------------------------+----------------------------
 07/09/2020 17:57:26.005347 PDT | 07/09/2020 17:57:26.005347 | 07/09/2020 17:57:26.005347

test(5432)=# SET TIMEZONE = 'Africa/Cairo';
SET
test(5432)=# insert into ts_test values(localtimestamp, localtimestamp, localtimestamp);
INSERT 0 1
test(5432)=# select * from ts_test ;
             ts_tz              |             ts             |           ts_txt           
--------------------------------+----------------------------+----------------------------
 07/10/2020 02:57:26.005347 EET | 07/09/2020 17:57:26.005347 | 07/09/2020 17:57:26.005347
 07/10/2020 02:57:44.661465 EET | 07/10/2020 02:57:44.661465 | 07/10/2020 02:57:44.661465
(2 rows)

test(5432)=# insert into ts_test values('07/09/2020 5:45', '07/09/2020 5:45', '07/09/2020 5:45');
INSERT 0 1
test(5432)=# select * from ts_test ;
             ts_tz              |             ts             |           ts_txt           
--------------------------------+----------------------------+----------------------------
 07/10/2020 02:57:26.005347 EET | 07/09/2020 17:57:26.005347 | 07/09/2020 17:57:26.005347
 07/10/2020 02:57:44.661465 EET | 07/10/2020 02:57:44.661465 | 07/10/2020 02:57:44.661465
 07/09/2020 05:45:00 EET        | 07/09/2020 05:45:00        | 07/09/2020 5:45

test(5432)=# SET TIMEZONE = default;
SET
test(5432)=# show timezone;
  TimeZone  
------------
 US/Pacific
(1 row)

test(5432)=# insert into ts_test values('07/09/2020 5:45', '07/09/2020 5:45', '07/09/2020 5:45');
INSERT 0 1
test(5432)=# select * from ts_test ;
             ts_tz              |             ts             |           ts_txt           
--------------------------------+----------------------------+----------------------------
 07/09/2020 17:57:26.005347 PDT | 07/09/2020 17:57:26.005347 | 07/09/2020 17:57:26.005347
 07/09/2020 17:57:44.661465 PDT | 07/10/2020 02:57:44.661465 | 07/10/2020 02:57:44.661465
 07/08/2020 20:45:00 PDT        | 07/09/2020 05:45:00        | 07/09/2020 5:45
 07/09/2020 05:45:00 PDT        | 07/09/2020 05:45:00        | 07/09/2020 5:45
(4 rows)

推荐阅读