[
https://issues.apache.org/jira/browse/DERBY-6445?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17851457#comment-17851457
]
Philippe Marschall commented on DERBY-6445:
---
I'm probably the worst person to search potential of issues in my code.
Nevertheless here are some potential issues to consider:
The {{LocalTime}}, {{LocalDate}} and {{LocalDateTime}} abstractions are [very
simple|
https://docs.oracle.com/en/java/javase/21/docs/api/java.base/java/time/package-summary.html]
and match very well to SQL types {{DATE}}, {{TIME}} and {{TIMESTAMP}}. This is
by design.
{quote}
LocalDate stores a date without a time. This stores a date like '2010-12-03'
and could be used to store a birthday.
LocalTime stores a time without a date. This stores a time like '11:30' and
could be used to store an opening or closing time.
LocalDateTime stores a date and time. This stores a date-time like
'2010-12-03T11:30'.
{quote}
Notice there is no reference to time zones, epochmillis and what not. This is
reflected in the code, nowhere except for `#updateObject` does the new code
mess with `Calendar`.
This the first way using the {{java.time}} classes can result in different
results than using the {{java.sql}} classes. As there is no reference to a time
zone there are also no time zone artifacts in the behavior. For example you can
not store a {{Timestamp}} of {{2025-03-30 02:30}} in Derby if you're using the
default {{Calendar}} and your JVM time zone is Europe/Berlin. This is because
it falls into a DST transition, on {{2025-03-30 02:00}} the clocks are
forwarded to {{2025-03-30 03:00}} hence {{2025-03-30 02:30}} "doesn't exist" in
the JVM default time zone used by {{Timestap}}. Storing a {{LocalDateTime}} of
{{2025-03-30 02:30}} on the other hand will work independent of the JVM default
time zone as we bypass {{Calendar}} and therefore time zones. Writing tests for
this would either require calling {{TimeZone.setDefault}} or relying on the
test being run in a time zone that has daylight savings time.
The second difference is that the {{java.time}} classes use a [proleptic
calendar|https://en.wikipedia.org/wiki/Proleptic_Gregorian_calendar] meaning
our current leap year rules are applied "forever" and there is no jump from 4
October 1582 to 15 October 1582. This is called an ISO calendar and my
understanding is what the [SQL standard
intends|https://www.postgresql.org/docs/current/datetime-units-history.html].
To give you an example
{code}
DAY(TIMESTAMP('1582-10-10 10:11:12.12345678'))
{code}
will return 20 with Derby, not 10. Using {{java.time}} classes will not fix
this but will allow storing and retrieving of {{1582-10-10 10:11:12.12345678}}.
Some tests here may also make sense.
The {{java.time}} classes default to ISO formatting meaning they use {{'T'}}
instead of {{' '}} as a separator between day and time hence the need for a
custom {{DateTimeFormatter}}.
The implementation as in the patch silently truncates {{LocalTime}} to seconds.
{{LocalTime}} supports sub second resolution but the code in the patch ignores
it. This is similar to the behavior of {{java.sql.Time}}. We can create a
{{java.sql.Time}} with sub second resolution like so
{{code}}
Calendar calendar = Calendar.getInstance();
calendar.clear();
calendar.set(Calendar.YEAR, 1970);
calendar.set(Calendar.MONTH, Calendar.JANUARY);
calendar.set(Calendar.DAY_OF_MONTH, 1);
calendar.set(Calendar.HOUR_OF_DAY, 1);
calendar.set(Calendar.MINUTE, 2);
calendar.set(Calendar.SECOND, 3);
calendar.set(Calendar.MILLISECOND, 456);
new Time(calendar.getTimeInMillis()));
{{code}}
If there's agreement on the intended behavior then some tests here may also
make sense.
And finally the {{java.time}} types operate on the [Java
Time-Scale|https://docs.oracle.com/en/java/javase/21/docs/api/java.base/java/time/Instant.html]
meaning they no not observe leap seconds. It is my understanding {{java.sql}}
types do [not|https://bugs.openjdk.org/browse/JDK-4272347] support leap seconds
either.
> JDBC 4.2: Add support for new date and time classes
> ---
>
> Key: DERBY-6445
> URL: https://issues.apache.org/jira/browse/DERBY-6445
> Project: Derby
> Issue Type: Improvement
> Components: JDBC
>Affects Versions: 10.10.1.1
>Reporter: Knut Anders Hatlen
>Priority: Major
> Attachments: DERBY-6445.patch, Derby-6445.html, Derby-6445.html,
> derby-6445-01-aa-DERBY-6445.patchPlusJavadocCleanup.diff,
> derby-6445-01-ab-DERBY-6445.patchPlusPlusTweaks.diff, tweaks.diff
>
>
> JDBC 4.2 added type mappings for new date and time classes found in Java 8.
> Derby should support these new mappings.
> This would at least affect Derby's implementation of the various getObject(),
> setObject() and setNull() methods in ResultSet, PreparedStatement and
>
