MySQL Bugs: #4533: Time portion of datetime field truncated

Bug #4533	Time portion of datetime field truncated
Submitted:	13 Jul 2004 3:29	Modified:	9 Aug 2004 21:35
Reporter:	john menarek	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	Connector / J	Severity:	S2 (Serious)
Version:	pondalopus	OS:	Windows (xp)
Assigned to:	Mark Matthews	CPU Architecture:	Any

Description:
I created a java.util.Date with no parameters, therefore the time equals the instant it was created. Saved it out to MySQL. Read it back in and compared the two values

The read back value had no time value, but if you looked in the database the field in the table DID have the time

If you look at the file com.mysql.jdbc.ResultSet.java, method getDate you will find the code segment

                    if (length < 10) {
                        throw new SQLException("Bad format for Date '"
                            + stringVal + "' in column " + columnIndex + "("
                            + fields[columnIndex - 1] + ").", SQLError.SQL_STATE_ILLEGAL_ARGUMENT);
                    }

                    year = new Integer(stringVal.substring(0, 4));
                    month = new Integer(stringVal.substring(5, 7));
                    day = new Integer(stringVal.substring(8, 10));

It never tries to get the time

How to repeat:
/*
 * Created on Jul 5, 2004
 *
 * To change the template for this generated file go to
 * Window&gt;Preferences&gt;Java&gt;Code Generation&gt;Code and Comments
 */
 
import java.util.Date;
import java.text.SimpleDateFormat;
//import java.text.ParseException;
import com.cobra.shippingclerk.user.*; 
import java.sql.*;
import java.lang.Error;
import java.lang.Throwable;
 
/**
 * @author John Menarek
 *
 * To change the template for this generated type comment go to
 * Window&gt;Preferences&gt;Java&gt;Code Generation&gt;Code and Comments
 */
public class DateIssue {
	public static void main(String[] args) 
	{
//		SimpleDateFormat df = new SimpleDateFormat( Cosignee.dateFormat);
		Date delivery = null;
//		try{
//			delivery = df.parse( "06-30-2004" );
//		}catch( Exception e ){}
//		System.out.println(delivery.toString());
//		//'YYYY-MM-DD HH:MM:SS' 
		delivery = new Date();
		SimpleDateFormat df2 = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		String deliveryDate = df2.format( delivery ); 
		System.out.println( deliveryDate + ":" + delivery.getTime() );
		

		try{
			Class.forName( "com.mysql.jdbc.Driver" );
		}
		catch( ClassNotFoundException notFound ){
			System.out.println( "ClassNotFoundException");
			System.exit(1);
		}
		String returnValue  = null;
		try{
			Connection conn = DriverManager.getConnection( 
				"jdbc:mysql:/localhost:3306/shippingclerk", 
				"JMenarek", 
			"JMenarek");
			
			for( int count  = 0; count < 1000000; count++ ){
				System.out.println(".");			
				Statement statement = conn.createStatement();
				String SQL = "insert into testdate values('" + 
					deliveryDate + "')";
					
//				System.out.println(SQL);
				statement.execute( SQL );
	
				SQL = "SELECT * FROM `testdate`";
//				System.out.println( SQL );
				ResultSet result = statement.executeQuery( SQL );
				
			
				int curPo = 0;
				int nextPo = 0; 
				Date retrieved = null;
				if( result.next()){
					retrieved = result.getDate(1);
				}
//				System.out.println( retrieved.toString() );
				if( delivery.compareTo( retrieved ) != 0 ){
					deliveryDate = df2.format( retrieved );
					System.out.println( retrieved.toString() + ":" + retrieved.getTime() );
					System.out.println("The sucka aint equal!!!");
					System.exit(1);
				}
	
				SQL = "delete from `testdate` where deliveryDate = '2004-06-30 00:00:00'"; 
					
//				System.out.println(SQL);

				statement.execute( SQL );
			}

		}catch( SQLException sqlException ){
			System.out.println( "SQLException:" + sqlException.getMessage());
			System.exit(1);
		}
		System.out.println( "Fini");
	}
}

Suggested fix:
Parse up the time

java.sql.Date instances have no time component (the time is 'normalized to midnight), see http://java.sun.com/j2se/1.5.0/docs/api/java/sql/Date.html

If you want to retrieve the time components of a value stored in a DATETIME column, you must use ResultSet.getTimestamp().

It gets you closer but I still see truncation

2004-08-08 14:23:46:1091996626624
.
2004-08-08 14:23:46.0:1091996626000
The sucka aint equal!!!

How are you creating your own instance of java.sql.Timestamp? 

Unless you're explicitly setting the nanos field to '0', the two values will _never_ be equal, as MySQL doesn't have sub-second precision TIMESTAMPs (yet, they're scheduled for 5.0).

Is the testcase that shows this discrepancy any different than the code you show currently in the bug report? (i.e. it's not possible to INSERT using an arbitrary string value of a java.sql.Date and then directly compare to a TIMESTAMP unless you've gone through the trouble of normalizing the date and adjusting for milliseconds via the setNanos() method).

I just tested this with the small change that (assuming you've converted 'delivery' to the java.sql.Timestamp type) I've 'normalized' the timestamp by calling .setNanos(0) directly after creating it.

The issue with using compareTo() if you _don't_ do this is caused by the fact that the 'nanos' field will be initialized to a portion of whatever System.currentTimeMillis() was at that point in time. Of course that value doesn't get saved in the database, and thus the value you will retrieve will vary _slightly_.

Is there a reason you're not comparing in the database (which might be more efficient in any case)?

First thank you for responding

The reason why I am going through all this is that I am trying to track down a bug that I am having with a client application

It's a fat java client using EJBs. The container is JBOSS. The problem is that the clients see dates off by one day. Example July 4th vs July 5th.

Originally I was mapping java.util.date to a mysql datetime. I was only parsing for the date but was seeing times appearing in the DB at 23:00 so infered that was why the date differences

I than changed the datetime to date in the database on my development system and I have not been able to reproduce the problem but of course my client still has the problem!

I have been in contact wit JBOSS and they pointed me towards the piece of code that manages the persistence of java.util.date and they convert everything to a long and than save it out.

I am temped to just change everything to an sql.date but am afraid to since I really have no idea where the problem is

Any light you can shine on the situation would be appreciated