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