public inbox for [email protected]
help / color / mirror / Atom feedHandling of Invalid datetime at DST spring forward
2+ messages / 2 participants
[nested] [flat]
* Handling of Invalid datetime at DST spring forward
@ 2021-05-11 19:33 PG Doc comments form <[email protected]>
2021-05-12 16:41 ` Re: Handling of Invalid datetime at DST spring forward Andrew Grillet <[email protected]>
0 siblings, 1 reply; 2+ messages in thread
From: PG Doc comments form @ 2021-05-11 19:33 UTC (permalink / raw)
To: [email protected]; +Cc: [email protected]
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.
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Handling of Invalid datetime at DST spring forward
2021-05-11 19:33 Handling of Invalid datetime at DST spring forward PG Doc comments form <[email protected]>
@ 2021-05-12 16:41 ` Andrew Grillet <[email protected]>
0 siblings, 0 replies; 2+ messages in thread
From: Andrew Grillet @ 2021-05-12 16:41 UTC (permalink / raw)
To: [email protected]; Pg Docs <[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.
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2021-05-12 16:41 UTC | newest]
Thread overview: 2+ messages (download: mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2021-05-11 19:33 Handling of Invalid datetime at DST spring forward PG Doc comments form <[email protected]>
2021-05-12 16:41 ` Andrew Grillet <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox