Thread: BUG #15527: Issue converting from local date/time to 'timestamp withtime zone' prior to dst transition

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.

=?utf-8?q?PG_Bug_reporting_form?= <noreply@postgresql.org> writes:
> 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.

In a fall-back transition, civil times between 1am and 2am are ambiguous:
they could refer to either the first transition between those hours
(while still on DST) or the second transition (now on standard time).
Postgres assumes that an unlabeled input time between those hours is
to be read as standard time, which I'd agree is pretty arbitrary,
but the other possibilities aren't better.

> 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)

Well, yes, that's exactly the problem.  It could be either.  The only
real way to fix this is to change your data entry procedure so that
you specify a zone abbreviation or UTC offset when entering an ambiguous
timestamp.  Or you could set "timezone" to a non-DST-aware setting
and be sure to change it at exactly the right time.

> I looked at the documentation but didn't notice anything that
> specifically addressed this detail.

This behavior is pretty clearly documented in the source code:

     * It's an invalid or ambiguous time due to timezone transition.  In a
     * spring-forward transition, prefer the "before" interpretation; in a
     * fall-back transition, prefer "after".  (We used to define and implement
     * this test as "prefer the standard-time interpretation", but that rule
     * does not help to resolve the behavior when both times are reported as
     * standard time; which does happen, eg Europe/Moscow in Oct 2014.  Also,
     * in some zones such as Europe/Dublin, there is widespread confusion
     * about which time offset is "standard" time, so it's fortunate that our
     * behavior doesn't depend on that.)

but if this is mentioned anywhere in the SGML docs, I couldn't find it
in a quick search :-(.  We should improve that.

            regards, tom lane

On Nov 28, 2018, at 4:30 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> =?utf-8?q?PG_Bug_reporting_form?= <noreply@postgresql.org> writes:
>> 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.
>
> In a fall-back transition, civil times between 1am and 2am are ambiguous:
> they could refer to either the first transition between those hours
> (while still on DST) or the second transition (now on standard time).
> Postgres assumes that an unlabeled input time between those hours is
> to be read as standard time, which I'd agree is pretty arbitrary,
> but the other possibilities aren't better.
>
>> 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)
>
> Well, yes, that's exactly the problem.  It could be either.  The only
> real way to fix this is to change your data entry procedure so that
> you specify a zone abbreviation or UTC offset when entering an ambiguous
> timestamp.  Or you could set "timezone" to a non-DST-aware setting
> and be sure to change it at exactly the right time.
>
>> I looked at the documentation but didn't notice anything that
>> specifically addressed this detail.
>
> This behavior is pretty clearly documented in the source code:
>
>     * It's an invalid or ambiguous time due to timezone transition.  In a
>     * spring-forward transition, prefer the "before" interpretation; in a
>     * fall-back transition, prefer "after".  (We used to define and implement
>     * this test as "prefer the standard-time interpretation", but that rule
>     * does not help to resolve the behavior when both times are reported as
>     * standard time; which does happen, eg Europe/Moscow in Oct 2014.  Also,
>     * in some zones such as Europe/Dublin, there is widespread confusion
>     * about which time offset is "standard" time, so it's fortunate that our
>     * behavior doesn't depend on that.)
>
> but if this is mentioned anywhere in the SGML docs, I couldn't find it
> in a quick search :-(.  We should improve that.
>
>             regards, tom lane

Tom,

Makes sense…thank you for the clarification.

Best regards,
Mike