Resolve a Bad format for Time error in java?

Question

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.

What is the type of the column [time]? – Jose Conde Aug 16 '10 at 13:37 — Jose Conde, Aug 16 '10 at 13:37

score 2 · Answer 1 · answered Aug 16 '10 at 13:35

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

}

score 1 · Accepted Answer · edited May 23 '17 at 12:07

1

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.

edited May 23 '17 at 12:07

Community

1
1

answered Aug 16 '10 at 13:47

BalusC

1,082,665
372
3,610
3,555

fully agree on 'unecessary overhead' :) – Andreas Dolk Aug 16 '10 at 15:14

score 1 · Answer 3 · answered Mar 09 '12 at 16:33

1

Maybe this answer is so late. But you can solve it just concating a string to the field you want. I mean:

select id,concat(time,'') from table where Hour(time)<=32

Regards Saul Hidalgo.

answered Mar 09 '12 at 16:33

Saul Hidalgo

11
1

The issue that he had was that the field could not be converted to a java `Time` object - how does adding an empty string to it help that? – Tim Mar 11 '12 at 07:04

score 0 · Answer 4 · answered Aug 16 '10 at 13:43

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.

Resolve a Bad format for Time error in java?

4 Answers4