[jira] [Commented] (DERBY-6445) JDBC 4.2: Add support for new date and time classes

[Richard N. Hillegas (Jira)]

    [ 
https://issues.apache.org/jira/browse/DERBY-6445?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17851796#comment-17851796
 ] 

Richard N. Hillegas commented on DERBY-6445:
--------------------------------------------

Thanks for that great explanation, Philippe. I have added it to the header 
comment of SQLTimstamp.

I am confused about the subtleties surrounding the precision of the 
java.sql.Time and SQL TIME types. My experiments show that java.sql.Time 
truncates a millisecond-granularity timestamp to seconds, throwing away the 
excess milliseconds, even using the Calendar code you included. The SQL TIME 
datatype has HOUR, MINUTE, and SECONDs parts (no milliseconds), according to 
the SQL Standard, part 2 (Foundation), clause 4.6.2 (Datetimes). So it seems to 
me that the code in the patch is correct as is.

Here is the experiment I ran:

{noformat}
import java.sql.*;
import java.util.Calendar;

public class TimeTest
{
    public static void main(String... args) throws Exception {

        Connection conn = 
DriverManager.getConnection("jdbc:derby:memory:db;create=true");

        prepareStatement(conn, "CREATE TABLE t(a TIME)").execute();

        Time time1 = new Time(System.currentTimeMillis());

        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);
        Time time2 = new Time(calendar.getTimeInMillis());

        try (PreparedStatement ps = prepareStatement(conn, "INSERT INTO t 
VALUES (?)"))
        {
            setTime(ps, time1);
            ps.execute();

            setTime(ps, time2);
            ps.execute();
        }
        
        try (PreparedStatement ps = prepareStatement(conn, "SELECT * from t"))
        {
            try (ResultSet rs = ps.executeQuery())
            {
                while(rs.next())
                {
                    println(rs.getTime(1).toString());
                }
            }
        }
        
    }

    private static void setTime(PreparedStatement ps, Time time) throws 
SQLException {
        println("Setting time column to " + time.toString());
        ps.setTime(1, time);
    }

    private static PreparedStatement prepareStatement(Connection conn, String 
text) throws SQLException {
        println("Preparing '" + text + "' ...");
        return conn.prepareStatement(text);
    }
    
    private static void println(String text) { System.out.println(text); }
}
{noformat}

Here is the output of that experiment:

{noformat}
Preparing 'CREATE TABLE t(a TIME)' ...
Preparing 'INSERT INTO t VALUES (?)' ...
Setting time column to 12:26:04
Setting time column to 01:02:03
Preparing 'SELECT * from t' ...
12:26:04
01:02:03
{noformat}


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