MIME-Version: 1.0
References: <162076162978.9272.2303191534467513079@wrigleys.postgresql.org>
In-Reply-To: <162076162978.9272.2303191534467513079@wrigleys.postgresql.org>
From: Andrew Grillet <andrew@grillet.co.uk>
Date: Wed, 12 May 2021 17:41:42 +0100
Message-ID: 
 <CAC-wiJUwO-hnUfV27dDK2ef=_QQU3VsChfnTyGS9B-mQ4Eh6cg@mail.gmail.com>
Subject: Re: Handling of Invalid datetime at DST spring forward
To: forrest@iris.washington.edu, Pg Docs <pgsql-docs@lists.postgresql.org>
Content-Type: text/plain; charset="UTF-8"
Archived-At: 
 <https://www.postgresql.org/message-id/CAC-wiJUwO-hnUfV27dDK2ef%3D_QQU3VsChfnTyGS9B-mQ4Eh6cg%40mail.gmail.com>
Precedence: bulk

Changing  the DST setting for an invalid date-time is a bad move.

If you do not know the date-time (because it is not valid), then you
do not know whether it is the date at which the change should occur.
The date of the change is different in different timezones, and if the
date-time is incorrect, it could be because the timezone is set
incorrectly.

Not-a-number is not a number and cannot be affected by arithmetic
operations. This is exactly the same situation.

I think there should be a way to indicate that the date/time is
invalid when read, but I can see that the side effects of not
incrementing
the clock would be worse than leaving it alone.

Andrew


Andrew

On Wed, 12 May 2021 at 16:36, PG Doc comments form
<noreply@postgresql.org> wrote:
>
> The following documentation comment has been logged on the website:
>
> Page: https://www.postgresql.org/docs/10/datetime-invalid-input.html
> Description:
>
> This page
>
>     https://www.postgresql.org/docs/10/datetime-invalid-input.html
>
> says a translation by +1 hour occurs for hours between 2 AM and 3 AM at DST
> spring forward dates, rather than treating them as invalid, i.e., we get
> 2:xx => 3:xx:
>
> => SELECT '2018-03-11 02:30'::timestamptz   =>  '2018-03-11 03:30:00-04'
>
> This has the interesting side affect that if we dont set TZ 'UTC' then
>
>    SELECT TSTZRANGE( '2018-03-11 02:30'::TIMESTAMPTZ , '2018-03-11
> 03:15'::TIMESTAMPTZ , '[]')
>    ERROR:  range lower bound must be less than or equal to range upper
> bound
>
> becomes invalid because the first date-time advances +1 hour and is now past
> the second, even though that is not visible by inspecting the statement.
> Too bad - suppress the error in one spot, have it pop up in another.
> But I see Java has the same policy:
>
>                 LocalDateTime localDateTime = LocalDateTime.of(2005, 4, 3, 2, 30, 0);
>                 ZonedDateTime dt =
> localDateTime.atZone(ZoneId.of("America/Los_Angeles"));
>                 String datetime_in_fmt = DateTimeFormatter.ofPattern(
> "yyyy-MM-dd'T'HH:mm:ss.SSS Z" ).format( dt );
>                 System.out.println( String.format(" => [%s]", datetime_in_fmt ));
>
> converts 2:30 to 3:30:
>
>                 '2005-04-03 02:30:00' => '2005-04-03T03:30:00.000 -0700'
>
> Oh well.