[
https://issues.apache.org/jira/browse/DERBY-6445?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=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)
