Bug #36051 Illegal hour value '24' for java.sql.Time type in value '24:00:00.
Submitted: 14 Apr 2008 10:23 Modified: 7 Jul 2008 12:35
Reporter: Juliane Grimm Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / J Severity:S2 (Serious)
Version: Connector/J 5.1.6 MySQL 5.0.16-Debian_1 OS:Microsoft Windows
Assigned to: Mark Matthews CPU Architecture:Any
Triage: D4 (Minor)

[14 Apr 2008 10:23] Juliane Grimm
Description:
When trying to read Time values like "00:00:00" with the new Connector/J 5.1.6 with ResultSet.getTime(int) an exception (see below) is thrown. This didn't happen with 5.0.3. It also happens with 5.0.8.
I'd like to keep the previous behaviour if possible...

Setting "useFastDateParsing=false" doesn't help.

java.sql.SQLException: java.sql.SQLException: Illegal hour value '24' for java.sql.Time type in value '24:00:00.
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1055)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:956)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:926)
	at com.mysql.jdbc.ResultSetImpl.getTimeFromString(ResultSetImpl.java:5878)
	at com.mysql.jdbc.ResultSetImpl.getTimeInternal(ResultSetImpl.java:5910)
	at com.mysql.jdbc.ResultSetImpl.getTime(ResultSetImpl.java:5662)
	...
Caused by: java.sql.SQLException: Illegal hour value '24' for java.sql.Time type in value '24:00:00.
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1055)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:956)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:926)
	at com.mysql.jdbc.TimeUtil.fastTimeCreate(TimeUtil.java:1023)
	at com.mysql.jdbc.ResultSetImpl.fastTimeCreate(ResultSetImpl.java:1012)
	at com.mysql.jdbc.ResultSetImpl.getTimeFromString(ResultSetImpl.java:5869)
	... 14 more

How to repeat:
Fill a TIME type column with 00:00:00 and then use
ResultSet.getTime(int columnIndex) on it.
[14 Apr 2008 12:51] Tonci Grgin
Hi Juliane and thanks for your report.

I would like you to attach full, self-contained, test case as well as DML/DDL statements needed to reproduce the problem. Please, also attach my.ini/my.cnf file from server. If server config files contain sensitive data, attach them as "Private".

From your report, it is unclear which JDK/JRE version you use. Please add that info.

What is the SQL_MODE server is run in?

What do you get as result of same queries in command line client? Does it give expected result?
[15 Apr 2008 0:23] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/45392
[15 Apr 2008 7:07] Juliane Grimm
Hi Tonci,

first of all. Sorry I made a mistake. The values in the database that made problems are not '00:00:00' but '24:00:00'. In my databaseviewer (squirrel) they look like '00:00:00'. So my problem changed a little bit. Values greater than '23:59:59' cause the given exception. So I'm right now sure if our configuration is the problem, or if it is a bug in the connector. Fact is with Connector/J 5.0.3 value '24:00:00' is read as '00:00:00', '25:00:00' as '01:00:00' and so on and in 5.08. or 5.1.6 this values cause the exception. I read that TIME columns can contain values up to '838:59:59', is there something we have to configure for that behavior? And maybe a stupid question: How do I have to read this column values as duration (time between two events)?

SQL_MODE is: 
SELECT @@session.sql_mode; => STRICT_TRANS_TABLES

I have a little Testprogram written (we use jre 1.5.06), trying it with different versions of the connector:

public class IllegalHourTest {
    public static void main(String[] args) throws SQLException, ClassNotFoundException {
        Class.forName("com.mysql.jdbc.Driver");

    Connection connection2 = DriverManager.getConnection(
            "jdbc:mysql://10.11.53.74/admdb?characterEncoding=utf8&zeroDateTimeBehavior=convertToNull",
            "matrix", "admin");
        Statement sqlStatement =
            connection2.createStatement(
                    ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);

        String sqlStmtStr = "select ID, DURATION from admdb.test_data";

            ResultSet rs = sqlStatement.executeQuery(sqlStmtStr);
            while (rs.next()) {

                Integer id = rs.getInt(1);
                Time wert = rs.getTime(2);

            System.out.println("id:value <->" + id + ":" + wert);
            }
    }
}

and this is the script to create the test_data table:
CREATE TABLE `admdb`.`test_data`
(
   ID int not null primary key auto_increment,
   DURATION time
);
truncate TABLE test_data;
insert into test_data (duration) values('00:00:04');
insert into test_data (duration) values('00:00:00');
insert into test_data (duration) values('024:00:00');
insert into test_data (duration) values('25:00:00');
[15 Apr 2008 10:15] Tonci Grgin
Juliane, I see our c/J team leader, Mr. Mark Matthews, has already prepared the patch (http://lists.mysql.com/commits/45392).

Keep an eye on this report to see when the version with patch will be released. Or, you can build it from SVN sources as described in manual.

Thanks for your interest in MySQL.
[15 Apr 2008 10:31] Juliane Grimm
Hi,

I don't think that this solves my problem.
What about:

public void testBug36051() throws Exception {
		try {
			this.rs = this.stmt.executeQuery("SELECT '25:12:13'");
			this.rs.next();
			this.rs.getTime(1);
		} finally {
			closeMemberJDBCResources();
		}
	}
[17 Apr 2008 18:02] Mark Matthews
That's not a *valid* time for java.sql.Time. You'll get "nonsense" out the other side if we try and parse that.

One needs to remember that java.sql.Time is *time-of-day*, not elapsed time, irrespective of what MySQL can handle, JDBC can't handle treating java.sql.Time as an elapsed time.
[18 Apr 2008 5:25] Juliane Grimm
Connector 5.0.3 and before could handle that.
I got a java.sql.Time object, containing the elapsed time as milliseconds.

So please tell me how should I read the MySQL datatype TIME now, when I store elapsed time there?
[7 Jul 2008 12:35] Tony Bedford
An entry has been added to the 5.1.6 Changelog:

When trying to read Time values like “00:00:00” with ResultSet.getTime(int) an exception is thrown.
[30 Jul 2008 14:53] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/50724
[13 Mar 2009 18:50] Mohammed Qader
to solve this issue try (select concat('',call_duration) from time_table) the concat will make it string so you can process it as string...