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.
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 |