Handling of Invalid datetime at DST spring forward

From: PG Doc comments form <noreply(at)postgresql(dot)org>
To: pgsql-docs(at)lists(dot)postgresql(dot)org
Cc: forrest(at)iris(dot)washington(dot)edu
Subject: Handling of Invalid datetime at DST spring forward
Date: 2021-05-11 19:33:49
Message-ID: 162076162978.9272.2303191534467513079@wrigleys.postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-docs

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.

Responses

Browse pgsql-docs by date

  From Date Subject
Next Message Thomas Munro 2021-05-12 02:45:10 Re: [DOC] pg_stat_replication_slots representation style inconsisitant
Previous Message tanghy.fnst@fujitsu.com 2021-05-11 12:49:40 RE: [DOC] pg_stat_replication_slots representation style inconsisitant