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 4:26	Modified:	11 Nov 3:35
Reporter:	Steven Buschman
Status:	Won't fix
Category:	Connector/J	Severity:	S3 (Non-critical)
Version:	3.1.8a	OS:	Microsoft Windows (Win XP)
Assigned to:		Target Version:

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').