MySQL Bugs: #45340: timestamp values not getting the correct timestamp

Bug #45340	timestamp values not getting the correct timestamp
Submitted:	4 Jun 2009 18:36	Modified:	4 Jun 2009 20:59
Reporter:	Roberto Jimeno	Email Updates:
Status:	Won't fix	Impact on me:	None
Category:	Connector / J	Severity:	S3 (Non-critical)
Version:	5.1.30	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	GMT, timestamp, timezone, UTC

Description:
When getting java.sql.Timestamp values from a TIMESTAMP column the server seems to send a timezoned timestamp instead of a plain UTC timestamp.

How to repeat:
I have a server configured for CET timezone with MySQL 5.1.30 (MSWindowsXP) and a client configured on UTC (Linux)
I tested it with this simple jsp (needs a datasource to a MySQL database)

<?xml version="1.0" encoding="UTF-8" ?>
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
<title>MySQL Timestamp UTC bug</title>
</head>
<body>

<%
java.text.SimpleDateFormat ISO8601FORMAT = new java.text.SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ss'Z'");
ISO8601FORMAT.setTimeZone(java.util.TimeZone.getTimeZone("GMT"));
java.util.Calendar cal = java.util.Calendar.getInstance(java.util.TimeZone.getTimeZone("UTC"));

java.sql.Connection conn = null;
try {
	javax.naming.InitialContext cxt = new javax.naming.InitialContext();
	if ( cxt == null ) {
		throw new RuntimeException("Uh oh -- no context!");
	}
	javax.sql.DataSource ds = (javax.sql.DataSource) cxt.lookup( "java:/comp/env/jdbc/deployments" );
	if ( ds == null ) {
		throw new RuntimeException("Data source not found!");
	}

	conn = ds.getConnection();

	java.sql.PreparedStatement createtablepstmt = conn.prepareStatement(
	        "CREATE TABLE mysqltimestamptest (mysqltimestamptest TIMESTAMP NULL)");
	createtablepstmt.executeUpdate();
	createtablepstmt.close();

	java.sql.PreparedStatement insertnowpstmt = conn.prepareStatement(
	        "INSERT INTO mysqltimestamptest (mysqltimestamptest) VALUES (NOW())");
	insertnowpstmt.executeUpdate();
	insertnowpstmt.close();

	java.sql.PreparedStatement pstmt = conn.prepareStatement(
	        "SELECT mysqltimestamptest FROM mysqltimestamptest");
	java.sql.ResultSet rs = pstmt.executeQuery();
	while(rs.next()) {
	    java.sql.Timestamp timestamp = rs.getTimestamp("mysqltimestamptest",cal);
	    out.println(ISO8601FORMAT.format(timestamp));
	}
	rs.close();
	pstmt.close();

	java.sql.PreparedStatement droptablepstmt = conn.prepareStatement(
	        "DROP TABLE mysqltimestamptest");
	droptablepstmt.executeUpdate();
	droptablepstmt.close();

} finally {
	try {
	if (conn != null) {
		conn.close();
	}
	} catch (java.sql.SQLException e) {}
}

%>

</body>
</html>

Suggested fix:
ALWAYS, send timestamp values in UTC over the wire. Sending a timezoned timestamp makes not sense.
It seems MySQL has tons of bugs with timezone support.
Timezones are not the business of a database. Timezone is the business of the presentation layer. So, timezones should always be stored in UTC, send in UTC over the wire, and saved in UTC in backups.
Do you guys have to make UTC in backups the only option?
And fixing this bug?

Configure your MySQL server to use a UTC timezone, and you avoid this issue.

Connector/J can't change the on-wire form of the timestamp, if you think that all timezones should be UTC on the wire, you should re-open a different bug on the server. The JDBC driver can only work with the data that is presented to it.

That means that it is impossible for MySQL Connector/J to open the connection as UTC to pass all timestamps as UTC to the client?

Should all the servers be configured in UTC for proper functioning?
I would like you to state it in the documentation and give a fatal error if someone tries to start MySQL on a server with a timezone different than UTC, if so.

Opened server bug #45342

I think you might be confused with how java.sql.Timestamp is defined in the documentation. It's *not* presented in UTC, it's not in the UTC timezone even. The value is *stored* as milliseconds since the epoch in the UTC timezone, but if you ever *touch* it, it's going to be converted to some other timezone depending on how you're presenting it.