ResultSet.updateTime() results in a wrong value

Question

So, I have a local server of MySQL database on my machine and I'm trying to change a TIME column values in one of the database's tables, straight from a Java program.

What I'm trying to do is to fetch all the lines from a table called "Flights" where the source is "Amsterdam" into a ResultSet called rs, then update the time inside the result row's 2nd column to a value the user inputs, and then update the row inside the DB.

Those are the rows I want to update their 2'nd column TIME:

35  11:38:08    9W8915  Amsterdam   Cancelled   4
36  11:38:08    DL9644  Amsterdam   Cancelled   4
37  11:38:08    G35516  Amsterdam   Cancelled   4
38  11:38:08    GA9081  Amsterdam   Cancelled   4
39  11:38:08    KL1017  Amsterdam   Cancelled   4
40  11:38:08    MF9651  Amsterdam   Cancelled   4
113 11:38:08    9W8916  Amsterdam   On time 14:25   4
114 11:38:08    G35524  Amsterdam   On time 14:25   4
115 11:38:08    KL1019  Amsterdam   On time 14:25   4
116 11:38:08    MF9653  Amsterdam   On time 14:25   4
144 11:38:08    AA6505  Amsterdam   On time 14:35   5
145 11:38:08    BA435   Amsterdam   On time 14:35   5
279 11:38:08    G35530  Amsterdam   On time 16:05   4
280 11:38:08    KL1021  Amsterdam   On time 16:05   4
281 11:38:08    MF9929  Amsterdam   On time 16:05   4

The code:

System.out.println("Enter time: (HH:MM:SS)"); 
   SimpleDateFormat sdf = new SimpleDateFormat("hh:mm:ss");
    try {
        Time tme = new Time(sdf.parse(in.nextLine()).getTime());
        System.out.println(tme);
        while(rs.next()) {  
            System.out.println(rs.getString(2));
            System.out.println("New Time: " + tme);
            rs.updateTime(2, tme);
            rs.updateRow();
        }
    }

The problem I'm facing is that the updated time is wrong. I insert "22:22:22" and it seems that tme (Time object) recieves the correct time (because System.out.println(tme) prints "22:22:22") but the results inside the database are wrong. That's the result AFTER executing the query with tme='22:22:22':

35  01:52:22    9W8915  Amsterdam   Cancelled   4
36  01:52:22    DL9644  Amsterdam   Cancelled   4
37  01:52:22    G35516  Amsterdam   Cancelled   4
38  01:52:22    GA9081  Amsterdam   Cancelled   4
39  01:52:22    KL1017  Amsterdam   Cancelled   4
40  01:52:22    MF9651  Amsterdam   Cancelled   4
113 01:52:22    9W8916  Amsterdam   On time 14:25   4
114 01:52:22    G35524  Amsterdam   On time 14:25   4
115 01:52:22    KL1019  Amsterdam   On time 14:25   4
116 01:52:22    MF9653  Amsterdam   On time 14:25   4
144 01:52:22    AA6505  Amsterdam   On time 14:35   5
145 01:52:22    BA435   Amsterdam   On time 14:35   5
279 01:52:22    G35530  Amsterdam   On time 16:05   4
280 01:52:22    KL1021  Amsterdam   On time 16:05   4
281 01:52:22    MF9929  Amsterdam   On time 16:05   4

The output from the console:

Enter time: (HH:MM:SS)
22:22:22
22:22:22
11:38:08
New Time: 22:22:22
11:38:08
New Time: 22:22:22
11:38:08
New Time: 22:22:22
11:38:08
New Time: 22:22:22
11:38:08
New Time: 22:22:22
11:38:08
New Time: 22:22:22
11:38:08
New Time: 22:22:22
11:38:08
New Time: 22:22:22
11:38:08
New Time: 22:22:22
11:38:08
New Time: 22:22:22
11:38:08
New Time: 22:22:22
11:38:08
New Time: 22:22:22
11:38:08
New Time: 22:22:22
11:38:08
New Time: 22:22:22
11:38:08
New Time: 22:22:22

As you can see, the time in the second column has been updated to "01:52:22" insted of "22:22:22". I don't understand why the time in 2nd column has changed to 01:52:22 if tme contains 22:22:22. Any ideas will be very appreciated!

Your example data is not consistent. What is the `11:38:08` value? — Basil Bourque, Apr 09 '20 at 14:20
You can turn off the code-formatting of your plain text by adding he line `` — Basil Bourque, Apr 09 '20 at 14:21
Seems a default timezone in the Java program differs from MySQL's one. Here's similar issue https://stackoverflow.com/questions/53725799/saving-a-localtime-in-mysql-time-column — edwgiz, Apr 09 '20 at 14:46
@BasilBourque Sorry for that. it's the result of input "08:08:08", so it's wrong too. — Alon Barenboim, Apr 09 '20 at 14:59

score 0 · Answer 1 · edited Jun 20 '20 at 09:12

java.time

Using the modern java.time classes instead of these terrible legacy date-time classes may avoid the time zone problem you seem to encounter here.

Use a JDBC driver compliant with JDBC 4.2 or later. These later versions of JDBC require support for exchanging java.time objects with the database by calling setObject, updateObject, and getObject.

Your input strings comply with the standard ISO 8601 format. These standard formats are used by default in java.time for parsing/generating text. So no need to specify a formatting pattern.

LocalTime lt = LocalTime.parse( "22:22:22" ) ;
myResultSet.updateObject( … , lt ) ;

Retrieval.

LocalTime lt = myResultSet.getObject( … , LocalTime.class ) ;

Example app

Here is an entire example app to demonstrate using a LocalTime object to update rows of a TIME WITHOUT TIME ZONE column.

This example uses the H2 Database Engine. H2 is handy for such demos. Being written in Java, it can be installed in your demo app via Maven or similar tool.

First we establish a DataSource object, containing our database connection information. We configure an in-memory database using mem, as there is no need to persist this example database to storage.

Next we create a table of two columns, a primary key of type UUID, and a TIME WITHOUT TIME ZONE column for the time-of-day value. Then we write a couple of rows, with times 10:10:10 and 11:11:11. We dump those rows to the console to verify. Then we update those rows, both getting the value 22:22:22. Again, we dump those rows to verify.

// Establish an object implementing `DataSource` interface.
JdbcDataSource ds = new JdbcDataSource();
ds.setURL( "jdbc:h2:mem:localtime_example_db;DB_CLOSE_DELAY=-1" );
// Set `DB_CLOSE_DELAY` to `-1` to keep in-memory database in existence after connection closes.
ds.setUser( "scott" );
ds.setPassword( "tiger" );

try (
        Connection conn = ds.getConnection() ;
)
{
    // Create database.
    String sql = "CREATE TABLE event_ ( \n" +
            "  pkey_ UUID NOT NULL DEFAULT RANDOM_UUID() PRIMARY KEY , \n" +
            "  when_ TIME WITHOUT TIME ZONE NOT NULL \n" +
            ");";
    try (
            Statement stmt = conn.createStatement() ;
    )
    {
        stmt.execute( sql );
    }

    // Insert row.
    sql = "INSERT INTO event_ ( when_ ) \n";
    sql += "VALUES ( ? ) \n";
    sql += ";";
    try (
            PreparedStatement pstmt = conn.prepareStatement( sql , Statement.RETURN_GENERATED_KEYS ) ;
    )
    {

        // Insert row.
        LocalTime lt10 = LocalTime.parse( "10:10:10" );
        pstmt.setObject( 1 , lt10 );
        pstmt.executeUpdate();

        ResultSet rs = pstmt.getGeneratedKeys();
        System.out.println( "INFO - Reporting generated keys." );
        while ( rs.next() )
        {
            UUID uuid = rs.getObject( 1 , UUID.class );
            System.out.println( "generated keys: " + uuid );
        }

        // Insert another row.
        LocalTime lt11 = LocalTime.parse( "11:11:11" );
        pstmt.setObject( 1 , lt11 );
        pstmt.executeUpdate();
    }


    // Dump all rows.
    System.out.println( "INFO - Reporting all rows in table `event_`." );
    sql = "SELECT * FROM event_";
    try (
            Statement stmt = conn.createStatement() ;
            ResultSet rs = stmt.executeQuery( sql ) ;
    )
    {
        while ( rs.next() )
        {
            UUID pkey = rs.getObject( "pkey_" , UUID.class );
            LocalTime lt = rs.getObject( "when_" , LocalTime.class );
            System.out.println( "Event: " + pkey + " | " + lt );
        }
    }

    // Update rows.
    sql = "SELECT pkey_ , when_  FROM event_ ;";
    try (
            Statement stmt = conn.createStatement(
                    ResultSet.TYPE_FORWARD_ONLY ,
                    ResultSet.CONCUR_UPDATABLE
            ) ;
            ResultSet rs = stmt.executeQuery( sql ) ;
    )
    {
        LocalTime lt = LocalTime.parse( "22:22:22" );

        while ( rs.next() )
        {
            rs.updateObject( "when_" , lt );
            rs.updateRow();
        }
    }

    // Dump all rows.
    System.out.println( "INFO - Reporting all rows in table `event_`." );
    sql = "SELECT * FROM event_";
    try (
            Statement stmt = conn.createStatement() ;
            ResultSet rs = stmt.executeQuery( sql ) ;
    )
    {
        while ( rs.next() )
        {
            UUID pkey = rs.getObject( "pkey_" , UUID.class );
            LocalTime lt = rs.getObject( "when_" , LocalTime.class );
            System.out.println( "Event: " + pkey + " | " + lt );
        }
    }
}
catch ( SQLException e )
{
    e.printStackTrace();
}

When run.

INFO - Reporting generated keys.

generated keys: 7554110f-a856-4945-9926-2d03ca819c93

INFO - Reporting all rows in table event_.

Event: 7554110f-a856-4945-9926-2d03ca819c93 | 10:10:10

Event: c05a7be2-3c09-4e57-8861-c05161138199 | 11:11:11

INFO - Reporting all rows in table event_.

Event: 7554110f-a856-4945-9926-2d03ca819c93 | 22:22:22

Event: c05a7be2-3c09-4e57-8861-c05161138199 | 22:22:22

Thank you for the attempt. I did try to use LocalTime with updateObject, and the result is exactly the same .. input "22:22:22" results in "01:52:22" and "08:08:08" results in "11:38:08". so weired ! — Alon Barenboim, Apr 09 '20 at 15:01
@AlonBarenboim I added source code for complete example app to demonstrate the effectiveness of using `LocalTime` class to update `TIME WITHOUT TIME ZONE` column using the H2 database. — Basil Bourque, Apr 09 '20 at 19:58
@edwgiz No, I did not as I do not use MySQL. But my Answer uses plain code that should work with any JDBC driver claiming compliance with JDBC 4.2, 4.3, or later. Furthermore, I added source code for a complete demo using H2. — Basil Bourque, Apr 09 '20 at 20:14

ResultSet.updateTime() results in a wrong value

1 Answers1

java.time

Example app