public inbox for [email protected]  

help / color / mirror / Atom feed

From: Andrew Grillet <[email protected]>
To: [email protected]
To: Pg Docs <[email protected]>
Subject: Re: Handling of Invalid datetime at DST spring forward
Date: Wed, 12 May 2021 17:41:42 +0100
Message-ID: <CAC-wiJUwO-hnUfV27dDK2ef=_QQU3VsChfnTyGS9B-mQ4Eh6cg@mail.gmail.com> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>

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
<[email protected]> 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.

reply

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: [email protected]
  Cc: [email protected], [email protected], [email protected]
  Subject: Re: Handling of Invalid datetime at DST spring forward
  In-Reply-To: <CAC-wiJUwO-hnUfV27dDK2ef=_QQU3VsChfnTyGS9B-mQ4Eh6cg@mail.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 agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox