Bug #8428 Date Types formatted incorrectly
Submitted: 11 Feb 2005 0:25 Modified: 11 Feb 2005 20:18
Reporter: Hyrum Ward Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / J Severity:S3 (Non-critical)
Version:3.1.6 OS:Microsoft Windows (Windows)
Assigned to: Mark Matthews CPU Architecture:Any

[11 Feb 2005 0:25] Hyrum Ward
Description:
I am using MySql 1.4.7 with Java 1.5 in Eclipse 3.1.  I'm using the ResultSet interface to retrieve values from the DB.

After replacing mysql-connector-java-3.0.15-ga-bin.jar with mysql-connector-java-3.1.6-bin.jar, my formats for the DATETIME, TIMESTAMP, and YEAR datatypes have changed.

The DATETIME and TIMESTAMP datatypes are returned with .0 appended.  i.e. 2004-12-12 12:12:12.0

The YEAR datatype is returned as 2004-01-01 instead of 2004.

MySql doesn't store the .0 or the -01-01.  The change in format is fouling up my JUnit assertions.  If it is supposed to work like this, please explain...

How to repeat:
Run this class, with a break-point at each 'String putYourBreakPointHere = ""'.
Set a watch on the expression rs.getString(1)

public class DateTest{
	public static void main(String[] args){
		try{
			Class.forName("com.mysql.jdbc.Driver");
			Connection conn = DriverManager.getConnection("yourConnectionStringHere");
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery("SELECT dateField FROM someTable WHERE someCondition");
			while(rs.next()){
				String test = rs.getString(1);
				String putYourBreakPointHere = "";
			}
			rs = stmt.executeQuery("SELECT yerTaxYear FROM tblBackTax WHERE intBackTaxID = 1");
			while(rs.next()){
				String test = rs.getString(1);
				String putYourBreakPointHere = "";
			}
		}
		catch(ClassNotFoundException e){e.printStackTrace();}
		catch(SQLException e){e.printStackTrace();}
	}
}
[11 Feb 2005 0:30] Hyrum Ward
I originally submitted this accidentally as a showstopper.  It isn't, really, just annoying.
[11 Feb 2005 5:19] Aleksey Kishkin
tested on win xp
[11 Feb 2005 18:18] Mark Matthews
You're running into the edge of double-sided bugfix (I guess we'll have to provide a 'legacy' switch for this).

Really, when you call ResultSet.getTimestamp().toString(), you _should_ get the same value as when calling ResultSet.getString() on the same column (also holding true for the other datetime types)...The old driver did not do this, the new one does.

We'll throw a switch in for 3.1.7 and newer that lets you turn off the 'more correct' behavior if your application depends on it. Unfortunately, it's required for JDBC compliance to act the way that it does now.
[11 Feb 2005 20:18] Mark Matthews
This is fixed for 3.1.7 via the 'noDatetimeStringSync' configuration parameter, which 'unlinks' the timezone conversion from how getString() works, which is the cause of the problem...Which also means you won't be able to use timezones. There's unfortunately no easy fix for this.

My suggestion is that it is dangerous in general to rely on formats of non-string datatypes to always stay the same (for example, java.lang.BigDecimal.toString() changed in 1.5 to be different than before), and that if your application requires _specific_ formatting, that you use the facilities in the class libraries for doing so (for example SimpleDateFormat for DATETIME data).