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.