From: Mike Davidson <packfan91@gmail.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #15527: Issue converting from local date/time to 'timestamp with time zone' prior to dst transition
Date: Thu, 29 Nov 2018 09:54:03 -0500
Message-ID: <C95E952B-2B27-47ED-A915-084E2E3C2A24@gmail.com> (raw)
In-Reply-To: <15787.1543440639@sss.pgh.pa.us>
References: <15527-f1be0b4dc99ebbe7@postgresql.org>
<15787.1543440639@sss.pgh.pa.us>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: packfan91@gmail.com, tgl@sss.pgh.pa.us, pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #15527: Issue converting from local date/time to 'timestamp with time zone' prior to dst transition
In-Reply-To: <C95E952B-2B27-47ED-A915-084E2E3C2A24@gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox