The following bug has been logged on the website:
Bug reference: 15527
Logged by: Michael Davidson
Email address: packfan91@gmail.com
PostgreSQL version: 9.6.10
Operating system: CentOS 6-9.el6.12.3
Description:
We have an application that is storing time series data in postgres using
'timestamp with time zone' field. Both the operating system and postgres
are configured using 'US/Eastern' time zone. No timezone is being provided
in the date/time values being inserted. We noticed that on 11/4/2018 one
hour worth of readings (from 1am to 2am US/Eastern) are missing. Due to
the dst rules for 'US/Eastern' the UTC offset should transition from -04 to
-05 on 11/4/2018 at 2am US/Eastern. However, please take a look at the
output below. It looks to me as if postgres is changing the UTC offset from
-04 to -05 at 1am instead of at 2am. I'm wondering if this is by design.
raritan=# select now();
now
------------------------------
2018-11-04 00:05:18.91363-04
(1 row)
raritan=# SELECT * FROM pg_timezone_names where name = 'US/Eastern';
name | abbrev | utc_offset | is_dst
------------------+--------+------------+--------
US/Eastern | EDT | -04:00:00 | t
(1 row)
raritan=# select '2018-11-04 00:00:00'::timestamp with time zone;
timestamptz
------------------------
2018-11-04 00:00:00-04
(1 row)
raritan=# select '2018-11-04 01:00:00'::timestamp with time zone;
timestamptz
------------------------
2018-11-04 01:00:00-05 <====== shouldn't this be -04
because still in daylight savings time?
(1 row)
I suspect that postgres doesn't take into account the current time and
whether or not daylight savings time is currently in effect when converting
to UTC. If that's the case there's no way to know if the date/time being
inserted is 1am EDT (UTC -04) or 1am EST (UTC -05) Perhaps postgres just
chose to go with the latter regardless of the current time. I looked at the
documentation but didn't notice anything that specifically addressed this
detail.
