=?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