PostgreSQL Bugs

Collected from the PG bugs email list.

Bug ID15527
PG Version9.6.10
OSCentOS 6-9.el6.12.3
Opened2018-11-28 20:52:45+00
Reported byMichael Davidson
StatusOpen

Body of first available message related to this bug follows.

The following bug has been logged on the website:

Bug reference:      15527
Logged by:          Michael Davidson
Email address:      (redacted)
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.

Messages

DateAuthorSubject
2018-11-28 20:52:45+00=?utf-8?q?PG_Bug_reporting_form?=BUG #15527: Issue converting from local date/time to 'timestamp with time zone' prior to dst transition
2018-11-28 21:30:39+00Tom LaneRe: BUG #15527: Issue converting from local date/time to 'timestamp with time zone' prior to dst transition
2018-11-29 14:54:03+00Mike DavidsonRe: BUG #15527: Issue converting from local date/time to 'timestamp with time zone' prior to dst transition