Re: BUG #15527: Issue converting from local date/time to 'timestampwith time zone' prior to dst transition

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