PostgreSQL 时区问题

浅浅的花香味﹌ 2022-11-05 12:53 257阅读 0赞

**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 可以获取时区信息

<table> 
 <tbody> 
  <tr> 
   <td><code>TZ</code></td> 
   <td>大写时区缩写 (only supported in&nbsp;<code>to_char</code>)</td> 
  </tr> 
  <tr> 
   <td><code>tz</code></td> 
   <td>小写时区缩写 (only supported in&nbsp;<code>to_char</code>)</td> 
  </tr> 
  <tr> 
   <td><code>TZH</code></td> 
   <td>时区 hours</td> 
  </tr> 
  <tr> 
   <td><code>TZM</code></td> 
   <td>时区 minutes</td> 
  </tr> 
  <tr> 
   <td><code>OF</code></td> 
   <td>时区与UTC的偏移量 (only supported in&nbsp;<code>to_char</code>)</td> 
  </tr> 
 </tbody> 
</table>

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

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zODg4MzMzOA_size_16_color_FFFFFF_t_70][]

重启 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 adw.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)
    );

数据如下：

<table> 
 <tbody> 
  <tr> 
   <th style="text-align:left;">id</th> 
   <th style="text-align:left;">title</th> 
   <th style="text-align:left;width:268px;">url</th> 
   <th style="text-align:left;width:233px;">created_at</th> 
   <th style="text-align:left;">updated_at</th> 
  </tr> 
  <tr> 
   <td style="text-align:right;">1</td> 
   <td>百度</td> 
   <td style="width:268px;">HTTPS://WWW.BAIDU.COM</td> 
   <td style="text-align:right;width:233px;">2021/3/3 8:50:46.417</td> 
   <td style="text-align:right;">2021/3/3 8:50:46.417</td> 
  </tr> 
  <tr> 
   <td style="text-align:right;">2</td> 
   <td>CSDN</td> 
   <td style="width:268px;">HTTPS://WWW.CSDN.NET/</td> 
   <td style="text-align:right;width:233px;">2021/3/10 2:00:25.437</td> 
   <td style="text-align:right;">2021/3/10 2:00:25.437</td> 
  </tr> 
  <tr> 
   <td style="text-align:right;">3</td> 
   <td>ES6</td> 
   <td style="width:268px;">HTTPS://ES6.RUANYIFENG.COM/</td> 
   <td style="text-align:right;width:233px;"><span style="color:#f33b45;">2021/3/10 16:50:46.442</span></td> 
   <td style="text-align:right;">2021/3/10 16:50:46.442</td> 
  </tr> 
 </tbody> 
</table>

查询 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'

查询结果：

<table> 
 <tbody> 
  <tr> 
   <th style="text-align:left;">id</th> 
   <th style="text-align:left;">title</th> 
   <th style="text-align:left;">url</th> 
   <th style="text-align:left;">created_at</th> 
  </tr> 
  <tr> 
   <td style="text-align:right;">3</td> 
   <td>ES6</td> 
   <td>HTTPS://ES6.RUANYIFENG.COM/</td> 
   <td><span style="color:#f33b45;">2021-03-10 08:50:46 +00</span></td> 
  </tr> 
 </tbody> 
</table>

可以发现只检索出了 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'

<table> 
 <tbody> 
  <tr> 
   <th style="text-align:left;">id</th> 
   <th style="text-align:left;">title</th> 
   <th style="text-align:left;">url</th> 
   <th style="text-align:left;">created_at</th> 
  </tr> 
  <tr> 
   <td style="text-align:right;">3</td> 
   <td>ES6</td> 
   <td>HTTPS://ES6.RUANYIFENG.COM/</td> 
   <td>2021-03-10 08:50:46 +00</td> 
  </tr> 
  <tr> 
   <td style="text-align:right;">2</td> 
   <td>CSDN</td> 
   <td>HTTPS://WWW.CSDN.NET/</td> 
   <td>2021-03-09 18:00:25 +00</td> 
  </tr> 
 </tbody> 
</table>

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zODg4MzMzOA_size_16_color_FFFFFF_t_70]: /images/20221023/29cc94863afd43e99a5798e1c2a584bd.png