MySQL Bugs: #11154: ResultSet.getTime() fails if time is >= 100:00:00

Bug #11154	ResultSet.getTime() fails if time is >= 100:00:00
Submitted:	8 Jun 2005 2:26	Modified:	11 Nov 2009 2:35
Reporter:	Steven Buschman	Email Updates:
Status:	Won't fix	Impact on me:	None
Category:	Connector / J	Severity:	S3 (Non-critical)
Version:	3.1.8a	OS:	Windows (Win XP)
Assigned to:		CPU Architecture:	Any

Description:
ResultSet.getTime() will fail with an SQLException: "Bad format for Time 'hhh:mm:ss' in column x" if hhh >= 100, e.g., 100:00:00. My understanding this should work up to 838:59:59.  Time foo = new Time(100 * 86400 * 1000) is perfectly acceptable for representing 100 hours.

How to repeat:
create a table with a TIME field and store '100:00:00'. Try to do a getTime, and

I've had a change of heart on the bug's severity. I can easily use a LONG instead of TIME. I'll live.

Tested on Win 2000 Sp4 , JDK 1.5, JDBC 3.1.8. Test case is attached.

Test case

Attachment: test.java (text/java), 1.06 KiB.

Modified test.java to include negative times, e.g., -01:00:00

Attachment: test1.java (text/x-java), 1.26 KiB.

These fail as well:
batch.addBatch("insert into test4 values ('-1:00:00')");
batch.addBatch("insert into test4 values ('-10:00:00')");
batch.addBatch("insert into test4 values ('-100:00:00')");

Please refer to this paragraph from the Javadocs for java.sql.Time:

"A thin wrapper around the java.util.Date class that allows the JDBC API to identify this as an SQL TIME value."

Although not spelled out in the APIDOCs, if you dig up the SQL 2003 specification, a SQL TIME type is described as such on page 32:

"TIME — contains the <primary datetime field>s HOUR, MINUTE, and SECOND."

Using the table at the top of the page, one finds the following definition of HOUR, MINUTE, SECOND:

"HOUR Hour within day
MINUTE Minute within hour
SECOND Second and possibly fraction of a second within minute"

Taken together, one can determine that the SQL TIME type represents _time_of_day_, not elapsed time (there's actually another type in standard SQL, INTERVAL, just for this purpose).

Therefore, the driver is acting in accordance with both the JDBC and SQL specifications., so thus not really a bug.

We'll look into how to provide access as to what is essentially the "interval" type as described by SQL, but it will not be the default behavior. I will poll the JDBC experts' group to see if this is something that can be addressed in a future JDBC specification.

Please also make note of the javadocs describing the constructor:

"Constructs a Time object initialized with the given values for the hour, minute, and second. The driver sets the date components to January 1, 1970. Any method that attempts to access the date components of a Time object will throw a java.lang.IllegalArgumentException.

The result is undefined if a given argument is out of bounds.

Parameters:
    hour - 0 to 23
    minute - 0 to 59
    second - 0 to 59"

Java Bug

Attachment: TimeBug.java (text/x-java), 300 bytes.

I'm not getting any exceptions with below (Java 1.5). Granted the toString is wrong, however, the time calculations are correct.
------------------
import java.sql.*;

public class TimeBug
{
	public static void main(String argv[])
	{
		Time fooNeg = Time.valueOf("-01:00:00"),
			 foo100 = Time.valueOf("100:00:00"),
			 foo0   = Time.valueOf("00:00:00");

		System.out.println("Should be -01:00:00: " + fooNeg.toString() + " " + (foo0.getTime() - fooNeg.getTime()));
		System.out.println("Should be 100:00:00: " + foo100.toString() + " " + (foo100.getTime() - foo0.getTime()));
	}
}

If in the future there would be JDBC support for INTERVAL, wouldn't be fairly easy to have INTERVAL defined as a combination of a java.sql.date and java.sql.time? java.sql.time doesn't need to limited by the SQL TIME limitation of +-837 hours.

Yes, but if you read the apidocs again, it's not guaranteed to work _at_all with hours > two digits:

"     The result is undefined if a given argument is out of bounds.

Parameters:
    hour - 0 to 23
    minute - 0 to 59
    second - 0 to 59".

Not very safe to rely on the fact that it _currently_ works in my opinion. Sun has in the past changed behaviors of classes as long as they meet what the spec and javadocs say (java.math.BigDecimal.toString() in JDK-1.5 comes to mind here).

If java.sql.ResultSet.getLong(...) is allowed for TIME columns, all values stored on the MySQL server can be retrieved using Connector/J.  Could we please have this?  Adding this shouldn't pose a risk to backward compatibility since current applications fail on TIME columns when getLong() is called.

Note: The mapping of the java.sql.Time class to columns having a MySQL TIME data type is a conceptual error leading to application and design problems.  The former represents the time portion of date-time, while the latter represents a time interval (including a sign).

As it stands today, I can use java.sql.ResultSet.setString(...) to insert values like '839:59:59' and '-22:10:45' into the database; however, I cannot ever retrieve those values -- they get "normalized" to something else modulo 24 hours.  There is no work-around.

Incidentally, java.sql.Time.equals() is problematic in that the server (rightly) narrows Time's representation.  Test jigs that look for what goes in, must come out will fail when using equals(), but java.sql.Time.toString().equals() works fine.

Pardon my naivete - how would you recommend  I store a time interval > 24 hours other than LONG? The best I can think of is a DATETIME, offset from Time.valueOf("00:00:00"). Am I missing something?

Until MySQL updates Connector/J, use BIGINT or INT, depending on the units and valid range of your time intervals.  Usage of the TIME data type will lead to complications in Java.

Update example code: Change for loop in test1.java as follows.  If getLong() would return a number in milliseconds, problem solved.

for (; rs.next(); ) {
    // alas, units of Long are in hours (no rounding).
    // units of milliseconds instead would be ideal.
    System.out.println("Long: " + rs.getLong("t"));
    try {
        System.out.println("Time: " + rs.getTime("t"));
    }
    catch (SQLException x) {
        System.out.println("Time: " + x.getMessage());
    }
}

I was trying to find a solution that lets ad-hoc queries make sense (select from foo where t >'25:00:00').