Обсуждение: BUG #15127: epoch lies 1 hour ahead

The following bug has been logged on the website:

Bug reference:      15127
Logged by:          Claus Regelmann
Email address:      rgc@rgc1.inka.de
PostgreSQL version: 10.2
Operating system:   Linux
Description:

Hi,
I think there is a bug in 10.2.
Compared to my old 9.1.18 installation, extracted epoch values lie 1h
ahead.

Claus
--------------------------------------------------------
xxx@eragon-rgc:~# psql -U postgres pmacct
psql (10.2)
Type "help" for help.

pmacct=# show time zone;
   TimeZone   
---------------
 Europe/Berlin
(1 row)

pmacct=# select *, extract(epoch from stamp_inserted),
abstime(stamp_inserted) from acct order by stamp_inserted desc limit 5;
     ip_src     |     ip_dst     | port_src | port_dst | ip_proto | packets
| bytes |   stamp_inserted    |    stamp_updated    | date_part  |
abstime        

----------------+----------------+----------+----------+----------+---------+-------+---------------------+---------------------+------------+------------------------
 192.168.4.242  | 192.53.103.108 |      123 |      123 |       17 |       1
|    76 | 2018-03-22 11:50:00 | 2018-03-22 11:55:01 | 1521719400 |
2018-03-22 11:50:00+01
 192.53.103.108 | 192.168.4.242  |      123 |      123 |       17 |       1
|    76 | 2018-03-22 11:50:00 | 2018-03-22 11:55:01 | 1521719400 |
2018-03-22 11:50:00+01
 192.168.4.242  | 192.53.103.103 |      123 |      123 |       17 |       1
|    76 | 2018-03-22 11:45:00 | 2018-03-22 11:50:01 | 1521719100 |
2018-03-22 11:45:00+01
 192.53.103.103 | 192.168.4.242  |      123 |      123 |       17 |       1
|    76 | 2018-03-22 11:45:00 | 2018-03-22 11:50:01 | 1521719100 |
2018-03-22 11:45:00+01
 192.168.4.242  | 185.222.211.35 |       22 |    59771 |        6 |       1
|    44 | 2018-03-22 11:40:00 | 2018-03-22 11:45:01 | 1521718800 |
2018-03-22 11:40:00+01
(5 rows)

pmacct=# \q
xxx@eragon-rgc:~# date -d @1521719400
Thu Mar 22 12:50:00 CET 2018
xxx@eragon-rgc:~#
------------------------------------------------------
xxx@rgc1:~$ psql -d pmacct -U postgres
psql (9.1.18)
Type "help" for help.

pmacct=# show time zone;
   TimeZone   
---------------
 Europe/Berlin
(1 row)

pmacct=# select *, extract(epoch from stamp_inserted),
abstime(stamp_inserted) from acct order by stamp_inserted desc limit 5;
     ip_src     |     ip_dst     | port_src | port_dst | ip_proto | packets
| bytes |   stamp_inserted    |    stamp_updated    | date_part  |
abstime        

----------------+----------------+----------+----------+----------+---------+-------+---------------------+---------------------+------------+------------------------
 10.1.1.73      | 169.45.214.238 |    50396 |     5222 |        6 |       9
|  1039 | 2018-03-22 11:50:00 | 2018-03-22 11:55:01 | 1521715800 |
2018-03-22 11:50:00+01
 169.45.214.238 | 10.1.1.73      |     5222 |    50396 |        6 |       5
|   320 | 2018-03-22 11:50:00 | 2018-03-22 11:55:01 | 1521715800 |
2018-03-22 11:50:00+01
 192.168.4.240  | 217.160.82.45  |    59365 |       53 |       17 |       1
|    90 | 2018-03-22 11:50:00 | 2018-03-22 11:55:01 | 1521715800 |
2018-03-22 11:50:00+01
 192.168.4.240  | 217.160.80.193 |    33014 |       53 |       17 |       1
|    73 | 2018-03-22 11:50:00 | 2018-03-22 11:55:01 | 1521715800 |
2018-03-22 11:50:00+01
 192.168.4.240  | 156.154.127.65 |    57709 |       53 |       17 |       1
|    74 | 2018-03-22 11:50:00 | 2018-03-22 11:55:01 | 1521715800 |
2018-03-22 11:50:00+01
(5 rows)

pmacct=# \q
xxx@rgc1:~$ date -d @1521715800
Thu Mar 22 11:50:00 CET 2018
xxx@rgc1:~$

=?utf-8?q?PG_Bug_reporting_form?= <noreply@postgresql.org> writes:
> I think there is a bug in 10.2.
> Compared to my old 9.1.18 installation, extracted epoch values lie 1h
> ahead.

Hm.  I get

regression=# select extract(epoch from timestamptz '2018-03-22 11:50:00+01');
 date_part  
------------
 1521715800
(1 row)

in either HEAD or 9.1.24 (don't have a build of 9.1.18 laying about),
and this agrees with outside tools such as "date", so I think it's
the right answer.  I'm not sure why your 9.1.18 installation is giving
a different answer.  At this time of year, though, a discrepancy in
opinions about the DST transition date is the first theory that springs
to mind.  I wonder which version of the tzdata database your 9.1.18
is using.

I also find this in the 9.1.23 release notes:

    <listitem>
     <para>
      Update our copy of the timezone code to match
      IANA's <application>tzcode</application> release 2016c (Tom Lane)
     </para>

     <para>
      This is needed to cope with anticipated future changes in the time
      zone data files.  It also fixes some corner-case bugs in coping with
      unusual time zones.
     </para>
    </listitem>

so it's not out of the question that the behavior discrepancy arises
from a since-fixed bug.

            regards, tom lane