Handling of Invalid datetime at DST spring forward
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.
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:
Show quoted text
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
boundbecomes 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.