Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

fix times for 1852 #1534

Closed
davecramer opened this issue Jul 27, 2019 · 1 comment · Fixed by #1539

Comments

@davecramer
Copy link
Member

commented Jul 27, 2019

Chapman Flack reported this on the mailing list, I am just copying and pasting here

October 1582 was a weird month. If you were in a European Catholic
country, you switched to Pope Gregory's calendar then, and your day
after the 4th was the 15th. Other countries switched at way different
times, some not until the 20th century.

ISO 8601, and therefore SQL, PostgreSQL, and the Java 8 java.time types,
all use Gregory's calendar proleptically, that is, out forever in both
directions, even into the past before anybody was using it in real life.
So they should render dates in a steady sequence without goofy jumps.

The older java.util.Date class, and therefore the java.sql.Date/Time/
Timestamp classes based on it, do a ten-day jump that month, just like
you would have if you lived in a European Catholic country.

So if you generate a little sequence of PostgreSQL timestamps from
October 16 backwards to September 30 that year, and you retrieve them
in PL/Java as java.sql.Timestamp and as java.time.LocalDateTime, and
in pgJDBC the same way (using binary protocol), you get this:

PG PL/Java pgJDBC
1582- java.sql java.time java.sql java.time
10-16 16 16 16 16
15 15 15 15 15
14 4 14 24 24
13 3 13 23 23
12 2 12 22 22
11 1 11 21 21
10 9-30 10 20 20
9 29 9 19 19
8 28 8 18 18
7 27 7 17 17
6 26 6 16 16
5 25 5 15 15
4 24 4 4 14
3 23 3 3 13
2 22 2 2 12
1 21 1 1 11
9-30 20 9-30 9-30 10

PL/Java, which does no special munging on the milliseconds value
it feeds to Java, produces a sequence of java.time values that has
no break and matches the PostgreSQL values, as they should,
being their proleptic little selves. It gets java.sql values
that do the thing they do, showing you what you'd have seen as
a European Catholic.

pgJDBC's toJavaSecs() and toPgSecs() methods contain some dubious
adjustments, apparently there in an attempt to make the java.sql
mapping less weird. But it really succeeds only in smearing the weird
around so it's not where it belongs, and some even gets on the java.time
mapping, which ought to be free of weird.

Both mappings repeat the dates 10-24 down to 10-15, and then, continuing
backward from there, it's the java.sql value that matches PostgreSQL,
and the java.time value that doesn't (!), and really nowhere
earlier than 10-15 does either one produce the value it would be
expected to.

marschall added a commit to marschall/pgjdbc that referenced this issue Aug 3, 2019
fix: proleptic java.time support
Make the java.time support proleptic by not going through
TimestampUtils.toJavaSecs when in binary mode.

Fixes pgjdbc#1534
@marschall marschall referenced this issue Aug 3, 2019
6 of 6 tasks complete
@marschall

This comment has been minimized.

Copy link
Contributor

commented Aug 3, 2019

I'm submitting a ...

  • bug report
  • feature request

Describe the issue
Short version:
Postgres and java.time use a proleptic calendar. The old java.util.Date and java.util.Calendar no not use a proleptic calendar and switch to the Julian calendar. The binary protocol support of the java.time classes goes through common code that tries to account for this when it shouldn't so it inherits its limitations.

Long version:
The issue was found by Chapman Flack and reported to the pgsql-jdbc mailing list.
Postgres uses the proleptic Gregorian calendar meaning it extends the Gregorian calendar beyond its introduction date and uses it for dates when the Gregorian calendar was not yet in effect but the Julian calendar was still being used. B.5. History of Units in the Postgres documentation does a good job of explaining this.
The situation in Java is as follows, the old classes java.util.Date and java.util.Calendar switch from the Gregorian to the Julian calendar. Despite its name GregorianCalendar is actually a hybrid calendar between the Gregorian and Julian calendars.
The new java.time classes use the ISO calendar system and use a proleptic Gregorian calendar. This makes it possible for us the exactly match the Postgres and Java semantics.
Unfortunately the current binary support for the java.time classes goes through TimestampUtils#toJavaSecs which tries to model the Julian to Gregorian calendar transition.

Driver Version?
42.2.6

Java Version?
11.0.4

OS Version?
Ubuntu 19.04

PostgreSQL Version?
11.04

To Reproduce

SELECT *
FROM generate_series('1582-09-30 00:00'::timestamp, '1582-10-16 00:00'::timestamp, '1 day');

Expected behaviour

1582-09-30
1582-10-01
1582-10-02
1582-10-03
1582-10-04
1582-10-05
1582-10-06
1582-10-07
1582-10-08
1582-10-09
1582-10-10
1582-10-11
1582-10-12
1582-10-13
1582-10-14
1582-10-15
1582-10-16

Actual behaviour

When using the binary protocol

1582-10-10
1582-10-11
1582-10-12
1582-10-13
1582-10-14
1582-10-15
1582-10-16
1582-10-17
1582-10-18
1582-10-19
1582-10-20
1582-10-21
1582-10-22
1582-10-23
1582-10-24
1582-10-15
1582-10-16

marschall added a commit to marschall/pgjdbc that referenced this issue Aug 3, 2019
fix: proleptic java.time support
Make the java.time support proleptic by not going through
TimestampUtils.toJavaSecs when in binary mode.

Fixes pgjdbc#1534
marschall added a commit to marschall/pgjdbc that referenced this issue Aug 3, 2019
fix: proleptic java.time support
Make the java.time support proleptic by not going through
TimestampUtils.toJavaSecs when in binary mode.

Fixes pgjdbc#1534
marschall added a commit to marschall/pgjdbc that referenced this issue Aug 3, 2019
fix: proleptic java.time support
Make the java.time support proleptic by not going through
TimestampUtils.toJavaSecs when in binary mode.

Fixes pgjdbc#1534
marschall added a commit to marschall/pgjdbc that referenced this issue Aug 10, 2019
fix: proleptic java.time support
Make the java.time support proleptic by not going through
TimestampUtils.toJavaSecs when in binary mode.

Fixes pgjdbc#1534
davecramer added a commit that referenced this issue Aug 27, 2019
fix: proleptic java.time support (#1539)
Make the java.time support proleptic by not going through
TimestampUtils.toJavaSecs when in binary mode.

Fixes #1534
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
2 participants
You can’t perform that action at this time.