how to resolve a Bad format for Time problem in java?

Question

hi, i'm working with java to extract values of a time column in table in mysql.

the code below show the query i do send.

String query="select id,time from table where Hour(time)<=32 ";
ResultSet res = stmt.executeQuery(query);
while (res.next()) {
  String id = res.getString(1); 
  Time tc = res.getTime("time");
  System.out.println(tc);
}

the time values can be negative (-56:00:00 meaning that we surpassed a certain delay. the problem is that i get: java.sql.SQLException: java.sql.SQLException: Bad format for Time '-05:48:49' in column 2.

thanks for your help.

Answer 1

If the conversion done by the ResultSet implementation does not work for negative time values then you still can read the value as a String and implement your custom method to convert the String to a Date (and vice versa):

  String query="select * from table where Hour(time)<=32 ";
  ResultSet res = stmt.executeQuery(query);
  while (res.next()) {
    String id = res.getString(1); 
    Time tc = convertToDate(res.getString("time"));
    System.out.println(tc);
  }

  // ....

}

private Time convertToDate(String s) {
  // implement magic here

}

Answer 2

I think the problem is on the query itself.

When you run direcly the query [select * from table where Hour(time)<=32] does it not return you an error? I imagine the error is on the where clause [Hour(time)<=32]

The resultset does not have any information about the where clause. It just returns all the columns.

You need to check the columns return to check if you are not returning some strange type.

Answer 3

As answered in your previous question you need to store and handle it as seconds which is stored as a signed integer.

The time type cannot be negative. You also cannot do math on a varchar/string and massaging it forth and back to a workable format as suggested by Andreas_D would only add unnecessary overhead. A signed integer is really the best datatype you can use for this. Use PreparedStatement#setInt() to store it and use ResultSet#getInt() to obtain it.