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

On Nov 28, 2018, at 4:30 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
> =?utf-8?q?PG_Bug_reporting_form?= <noreply(at)postgresql(dot)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