Content-Type: text/plain;
	charset=utf-8
Mime-Version: 1.0 (Mac OS X Mail 12.0 \(3445.100.39\))
Subject: Re: BUG #15527: Issue converting from local date/time to 'timestamp
 with time zone' prior to dst transition
From: Mike Davidson <packfan91@gmail.com>
In-Reply-To: <15787.1543440639@sss.pgh.pa.us>
Date: Thu, 29 Nov 2018 09:54:03 -0500
Cc: pgsql-bugs@lists.postgresql.org
Content-Transfer-Encoding: quoted-printable
Message-Id: <C95E952B-2B27-47ED-A915-084E2E3C2A24@gmail.com>
References: <15527-f1be0b4dc99ebbe7@postgresql.org>
 <15787.1543440639@sss.pgh.pa.us>
To: Tom Lane <tgl@sss.pgh.pa.us>
Precedence: bulk

On Nov 28, 2018, at 4:30 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>=20
> =3D?utf-8?q?PG_Bug_reporting_form?=3D <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.
>=20
> 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.
>=20
>> 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)
>=20
> 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.
>=20
>> I looked at the documentation but didn't notice anything that
>> specifically addressed this detail.
>=20
> This behavior is pretty clearly documented in the source code:
>=20
>     * 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.)
>=20
> but if this is mentioned anywhere in the SGML docs, I couldn't find it
> in a quick search :-(.  We should improve that.
>=20
> 			regards, tom lane

Tom,

Makes sense=E2=80=A6thank you for the clarification.=20

Best regards,
Mike