[jira] [Commented] (DERBY-6445) JDBC 4.2: Add support for new date and time classes
[
https://issues.apache.org/jira/browse/DERBY-6445?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17853703#comment-17853703
]
Philippe Marschall commented on DERBY-6445:
---
I think there are two different questions:
# Should the code for this JIRA silently truncate {{LocalTime}} to seconds? I
believe so based on the current behavior of {{java.sql.Time}}. I just wanted to
bring it up as it may not be immediately obvious.
# Should {{TIME}} in Apache Derby support sub-second precision? If not should
it silently truncate? This is a much boarder question that I believe goes
beyond the scope of this JIRA.
Unfortunately I do not have access to the SQL standard. I would like make two
arguments for {{TIME}} supporting sub-second resolution. The first is a
conceptual one, if a {{TIMESTAMP}} is an aggregate of a {{DATE}} and a {{TIME}}
and {{TIMESTAMP}} has a sub-second resolution doesn't that mean that {{TIME}}
should also have a sub-second resolution? Second a lot of other RDBMS support
sub-second resolution for {{TIME}}
* PosgreS https://www.postgresql.org/docs/current/datatype-datetime.html
* SQL Server
https://learn.microsoft.com/en-us/sql/t-sql/data-types/time-transact-sql
* MySQL https://dev.mysql.com/doc/refman/8.4/en/fractional-seconds.html
* H2 http://www.h2database.com/html/datatypes.html#time_type
* HSQLDB
https://www.hsqldb.org/doc/guide/sqlgeneral-chapt.html#sgc_datetime_types
Oracle does not have a {{TIME}} datatype and DB2 does not support sub-second
resolution https://www.ibm.com/docs/en/db2-for-zos/13?topic=values-time
https://www.ibm.com/docs/en/db2/11.5?topic=list-datetime-values
As for Derby itself {{org.apache.derby.iapi.types.SQLTime}} already has a
{{#encodedTimeFraction}} that is currently always 0 as {{#computeEncodedTime}}
does not read the milliseconds from the calendar.
The Derby client itself seems to support sub-second resolution for {{Time}} if
I have a look at {{org.apache.derby.client.am.CrossConverters#setObject(int,
Timestamp)}} or {{DateTimeValue#DateTimeValue(Time, Calendar)}}.
Whether {{java.sql.Time}} supports sub-second resolution or truncates to
seconds is a bit more complicated. Unfortunately the class comment is not very
clear. What speaks for the truncation is that both {{#toString()}} and
{{#valueOf(LocalTime)}} both truncate to seconds. What speaks against it is
that it is possible to get the milliseconds out again. If we create a
{{Calendar}} from the {{Time}} we get the millisecond value.
{{code}}
Calendar creationCalendar = Calendar.getInstance();
creationCalendar.clear();
creationCalendar.set(Calendar.YEAR, 1970);
creationCalendar.set(Calendar.MONTH, Calendar.JANUARY);
creationCalendar.set(Calendar.DAY_OF_MONTH, 1);
creationCalendar.set(Calendar.HOUR_OF_DAY, 1);
creationCalendar.set(Calendar.MINUTE, 2);
creationCalendar.set(Calendar.SECOND, 3);
int millis = 456;
creationCalendar.set(Calendar.MILLISECOND, millis);
Time time = new Time(creationCalendar.getTimeInMillis());
Calendar readBack = Calendar.getInstance();
readBack.setTimeInMillis(time.getTime());
assertEquals(millis, readBack.get(Calendar.MILLISECOND));
{{code}}
However when going through Derby it is always truncated to seconds. I haven't
debugged it but would assume because
{{org.apache.derby.iapi.types.SQLTime#encodedTimeFraction}} is always 0.
> 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,
> derby-6445-02-aa-patchExplanation.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
> CallableStatement.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (DERBY-6445) JDBC 4.2: Add support for new date and time classes
[
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
>
[jira] [Commented] (DERBY-6445) JDBC 4.2: Add support for new date and time classes
[ https://issues.apache.org/jira/browse/DERBY-6445?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17849434#comment-17849434 ] Philippe Marschall commented on DERBY-6445: --- Thank you. Looking forward to it. > 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 > > > 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 > CallableStatement. -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Commented] (DERBY-6445) JDBC 4.2: Add support for new date and time classes
[
https://issues.apache.org/jira/browse/DERBY-6445?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17849373#comment-17849373
]
Philippe Marschall commented on DERBY-6445:
---
About the diagnostic logging, it is not clear to me how this could best be
implemented. To give you an example:
{{org.apache.derby.client.am.ClientResultSet#getObject(String, Class)}} logs
method entry, but does not log method exit. It ends up calling
{{org.apache.derby.client.am.ClientResultSet#getObject(int, Class)}} which
also logs method entry and not log method exit as well. This can end up calling
an existing method like
{{org.apache.derby.client.am.ClientResultSet#getDate(int, Calendar)}} which
logs method entry and method exit or a new method like
{{org.apache.derby.impl.jdbc.EmbedResultSet#getLocalDate(int)}} which, as you
pointed out correctly, does not do any diagnostic logging. My reasoning here is
this is a {{private}} utility method that can only be called indirectly while
{{#getDate}} is a {{public}} API method that can be called directly by client
code. Diagnostic logging currently only seems be done for {{public}} methods
and very few {{protected}} methods these being four {{finalize}} methods and
two methods on {{BasicClientDataSource}}. The situation is similar for
{{org.apache.derby.client.am.ClientPreparedStatement}} and {{#setObject}}.
I see several options and would welcome your guidance:
- I could add diagnostic logging directly to the new {{private}} methods, they
would become the first {{private}} methods to have diagnostic logging.
- Inline the new {{private}} methods into {{ClientResultSet}}. Not ideal.
- I could extend the diagnostic logging of {{#getObject}} to also log method
exit. That would still result in different diagnostic logs depending on whether
{{#getObject}} is called with {{java.sql.Date}} ({{#getObject}} and
{{#getDate}} are logged) or with {{java.time.LocalTime}} (only {{#getObject}}
is logged). To avoid this they would have to be split into a {{public}} method
which performs diagnostic logging and a {{private}} one which contains the rest
of the implementation. {{#getObject}} would call the latter to avoid logging
twice.
> 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
>
>
> 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
> CallableStatement.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (DERBY-6445) JDBC 4.2: Add support for new date and time classes
[
https://issues.apache.org/jira/browse/DERBY-6445?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17849273#comment-17849273
]
Philippe Marschall commented on DERBY-6445:
---
I attached a patch.
It contains:
- Support for both embedded and client mode.
- Support for both {{ResultSet}} and {{CallableStatement}}.
- Support for {{#getObject}}, {{#setObject}} and {{#updateObject}}.
The code avoids converting through {{java.sql}} types or {{Calendar}} and
instead uses {{java.time}} types directly. Care has been taken to not affect
existing code paths.
Limitations:
- {{#updateObject}} goes through the {{java.sql}} types, meaning to introduces
the limitations of the {{java.sql}} times. I assume the method is used rarely
enough so that this is acceptable. If not storing {{DateTimeValue}} instead of
{{java.sql}} types in {{ClientResultSet.updatedColumns_}} would be an option.
- Java stored procedure implementations do not yet support java.time
parameters. This would be more involved as method lookup would have to be
updated and input/output conversion would have to happen.
> 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
>
>
> 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
> CallableStatement.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Updated] (DERBY-6445) JDBC 4.2: Add support for new date and time classes
[ https://issues.apache.org/jira/browse/DERBY-6445?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Philippe Marschall updated DERBY-6445: -- Attachment: DERBY-6445.patch > 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 > > > 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 > CallableStatement. -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Closed] (DERBY-6920) Add input support for new date and time classes
[ https://issues.apache.org/jira/browse/DERBY-6920?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Philippe Marschall closed DERBY-6920. - Resolution: Abandoned I will submit a full input-output patch in DERBY-6445 > Add input support for new date and time classes > --- > > Key: DERBY-6920 > URL: https://issues.apache.org/jira/browse/DERBY-6920 > Project: Derby > Issue Type: Improvement > Components: JDBC >Reporter: Philippe Marschall >Priority: Major > Attachments: jsr-310-input.diff > > > This is the first half of DERBY-6445. The patch adds support for the new date > and time classes for input only. I may add output support later but for now > splitting it up makes for a smaller, easier to review and implement patch. > A couple of notes about the implementation: > * as the project now requires Java 1.8 I added the code directly to the > classes > * the existing tests are expanded > * I avoided calling the provided #valueOf conversion methods for several > reasons: > ** LocalTime has nanosecond resolution but java.sql.Time only has millisecond > resolution > ** LocalDateTime can represent timestamps that can not be represented by > java.sql.Timestamp because they fall into a daylight saving time transition > ** Performance should be much better since creating a DateTimeValue instance > is a simple matter of calling a few getters. No calculation or object > creation is involved. -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Created] (DERBY-6994) Only last generated key in batch update is returned
Philippe Marschall created DERBY-6994:
-
Summary: Only last generated key in batch update is returned
Key: DERBY-6994
URL: https://issues.apache.org/jira/browse/DERBY-6994
Project: Derby
Issue Type: Bug
Affects Versions: 10.14.1.0
Reporter: Philippe Marschall
Attachments: DerbyGeneratedKeysTest.java
When a batch update is executed only the generated keys of the last update are
returned. Consider the following code.
{code}
EmbeddedDataSource derbyDataSource = new EmbeddedDataSource();
derbyDataSource.setDatabaseName("memory:test");
derbyDataSource.setCreateDatabase("create");
try (Connection connection = derbyDataSource.getConnection()) {
try (Statement statement = connection.createStatement()) {
statement.execute("CREATE TABLE test_table ("
+ "id INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY (START
WITH 1, INCREMENT BY 1) PRIMARY KEY,"
+ "test_value INTEGER"
+ ")");
}
try (PreparedStatement preparedStatement = connection.prepareStatement(
"INSERT INTO test_table(test_value) VALUES (?)",
Statement.RETURN_GENERATED_KEYS)) {
preparedStatement.setObject(1, Integer.valueOf(42));
preparedStatement.addBatch();
preparedStatement.setObject(1, Integer.valueOf(43));
preparedStatement.addBatch();
int[] updateCount = preparedStatement.executeBatch();
assertArrayEquals(new int[] {1, 1}, updateCount, "update count");
List generatedIntegers = new ArrayList<>(2);
try (ResultSet generatedKeys = preparedStatement.getGeneratedKeys()) {
while (generatedKeys.next()) {
generatedIntegers.add(generatedKeys.getObject(1, Integer.class));
}
}
assertEquals(Arrays.asList(1, 2), generatedIntegers, "generated keys");
}
}
{code}
two updates are performed in a single batch. The first generates the key {{1}}
and the second generates the key {{2}} but only the key {{2}} is returned.
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
[jira] (DERBY-6920) Add input support for new date and time classes
Title: Message Title Philippe Marschall commented on DERBY-6920 Re: Add input support for new date and time classes Yes, I plan to do the rest of the work as well but it may take a moment. Add Comment This message was sent by Atlassian JIRA (v6.3.15#6346-sha1:dbc023d)
[jira] (DERBY-6445) JDBC 4.2: Add support for new date and time classes
Title: Message Title Philippe Marschall commented on DERBY-6445 Re: JDBC 4.2: Add support for new date and time classes For the output side of things what is needed is changing the implementation of ResultSet#getObject(int, Class) and ResultSet#getObject(String, Class) so that the types java.time.LocalDate, java.time.LocalTime and java.time.LocalDateTime can be passed as arguments and returned. Unfortunately the specification does not mention this, tables B-4 and B-5 apply only to #setObject and table B-6 does not mention it. So far I can only offer this document from Oracle (at the very end). Add Comment This message was sent by Atlassian JIRA (v6.3.15#6346-sha1:dbc023d)
[jira] [Created] (DERBY-6920) Add input support for new date and time classes
Philippe Marschall created DERBY-6920: - Summary: Add input support for new date and time classes Key: DERBY-6920 URL: https://issues.apache.org/jira/browse/DERBY-6920 Project: Derby Issue Type: Improvement Components: JDBC Reporter: Philippe Marschall Attachments: jsr-310-input.diff This is the first half of DERBY-6445. The patch adds support for the new date and time classes for input only. I may add output support later but for now splitting it up makes for a smaller, easier to review and implement patch. A couple of notes about the implementation: * as the project now requires Java 1.8 I added the code directly to the classes * the existing tests are expanded * I avoided calling the provided #valueOf conversion methods for several reasons: ** LocalTime has nanosecond resolution but java.sql.Time only has millisecond resolution ** LocalDateTime can represent timestamps that can not be represented by java.sql.Timestamp because they fall into a daylight saving time transition ** Performance should be much better since creating a DateTimeValue instance is a simple matter of calling a few getters. No calculation or object creation is involved. -- This message was sent by Atlassian JIRA (v6.3.4#6332)
