PostgreSQL now（）和夏时制 [英] PostgreSQL now() and daylight saving

问题描述

我们在PostgreSQL 9.0.10中存在夏令时问题。在尝试使用OS时区文件结构时，我们用指向OS时区文件的链接替换了PostgreSQL的文件（并重命名为原始文件）

We are having an issue with daylight savings in PostgreSQL 9.0.10. In the attempt to use the OS timezone files structure, we replace the PostgreSQL's files with a link to OS timezone files (and renamed the original ones)

$ ls -l /usr/local/pgsql/share/timezone/Brazil
/usr/local/pgsql/share/timezone/Brazil -> /usr/share/zoneinfo/Brazil/

查询数据库，一切正常：

Querying the database, everything looks ok:

=> SELECT * FROM pg_timezone_names where name like '%Brazil%';
           name            | abbrev | utc_offset | is_dst
---------------------------+--------+------------+--------
 Brazil.original/DeNoronha | FNT    | -02:00:00  | f
 Brazil.original/East      | BRST   | -02:00:00  | t
 Brazil.original/West      | AMT    | -04:00:00  | f
 Brazil.original/Acre      | AMT    | -04:00:00  | f
 Brazil/DeNoronha          | -02    | -02:00:00  | f
 Brazil/East               | BRT    | -03:00:00  | f
 Brazil/West               | -04    | -04:00:00  | f
 Brazil/Acre               | -05    | -05:00:00  | f

=> show timezone;
  TimeZone
-------------
Brazil/East

巴西/东部，如上所示，为-03，DST为false。但是然后我们得到了：

"Brazil/East", as shown above, is -03, with DST false. But then we get this:

=> select now(), now() AT TIME ZONE 'Brazil/East';
              now              |          timezone
-------------------------------+----------------------------
2018-10-21 11:25:51.300744-02 | 2018-10-21 11:25:51.300744

我们希望看到 2018-10-21 10：25：51.300744-03，而不是
-02中的此日期。

We were expecting to see "2018-10-21 10:25:51.300744-03" and not this date in "-02".

关于我们在这里缺少什么的任何想法？

Any ideas about what we are missing here?

推荐答案

替换PostgreSQL安装中的文件不是正确和受支持的处理方式，因此得到奇怪的结果也就不足为奇了。

Replacing files in a PostgreSQL installation is not the correct and supported way to deal with that, so it is not surprising that you get strange results.

您应该重建PostgreSQL并对其进行配置使用-with-system-tzdata ，如文档。

You should rebuild PostgreSQL and configure it using --with-system-tzdata, as stated in the documentation.

但是，您当然不应该使用9.0版，因为它不支持大约3年。

But of course you shouldn't be using version 9.0, this has been out of support for about 3 years.

这篇关于PostgreSQL now（）和夏时制的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！