CODEWITHSUNDEEP
postgresqldst
andre.dias
andre.dias

Reputation: 81

PostgreSQL now() and daylight saving

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

"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

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?

Upvotes: 1

Views: 1480

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 248175

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.

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

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

Upvotes: 1

Related Questions