Use session time zone for ResultSet.getTimestamp(String columnLabel)

How to reproduce:
Run attached Junit test case(s) TimezoneTest.java.
Psedo code:
1. Create a table with a TIMESTAMP column
2. Insert the time (and a date) 13:20 in UTC time. It is important to make this insert correctly. You should probably not use PreparedStatement.setTimestamp(int parameterIndex, Timestamp x) because that method also may have a bug.
3. SELECT the inserted row and extract the timestamp with ResultSet.getTimestamp(String columnLabel)

Actual:
When running the JUnit computer in Swedish time (CEST) and server in Istanbul time (EEST) you get the results:

org.junit.ComparisonFailure: expected:<2014-01-01T1[3]:20:59+0000> but was:<2014-01-01T1[8]:20:59+0000>

at org.mariadb.jdbc.TimezoneTest.testGetTimestampAdvancedTimeZoneSwitching(TimezoneTest.java:124)

org.junit.ComparisonFailure: expected:<2014-01-01T1[3]:20:59+0000> but was:<2014-01-01T1[7]:20:59+0000>

	at org.mariadb.jdbc.TimezoneTest.testGetTimestampTest(TimezoneTest.java:93)

In other words the time is not 13:20, but some other time, usually shifted by the difference of the client and server.

The reason for difference between the test cases is that the method testGetTimestampTest only can insert a correct time value when the server is in UTC timezone (I think).

The test case testEnsureServerIsNotUsingAtlanticTimeZone() will always pass (unless you have put your server in a crazy time zone (GMT -2)).

Expected:
All test cases should pass (expect maybe not testGetTimestampTest() if server is using non-UTC time.). The times should always return 13:20 in UTC.

Environment:
I used an Ubuntu Virtualbox to switch time zone on my server with the command:

sudo dpkg-reconfigure tzdata;sudo service mysql restart

I tested with different time zones (Stockholm, Istanbul, UTC).

The test case is written in the context MariaDB JDBC source code. You might need to edit BaseTest to change the server connection URL.

I used Eclipse and Windows 8 in a Swedish time zone to execute the test cases. I tried my best to make sure my test cases would show an error regardless of your time zone set up. But the actual (erroneous) values may differ.

I primarily used MariaDB JDBC version 1.1.5 and 1.1.7. I also tried the current code in trunk.

I also tried with Mysql JDBC driver 5.1.26 and 5.1.31. The Mysql driver also returned wrong results.

My opinions on Java SQL API and Mysql data types:
Mysql/MariaDB server has 3 time data types (http://dev.mysql.com/doc/refman/5.1/en/datetime.html): DATE, DATETIME, and TIMESTAMP. It is my option that only TIMESTAMP should be used to store time. Because it is the only data type that stores a point in time. My definition is that a point is some seconds since an Epoch. TIMESTAMP is the number of seconds since the start of 1970 in UTC.

The DATETIME data types should be avoided since it stores a point of day. For example it stores the point of day when you normally take lunch (12:00). Since it does not stores any time zone information you cannot defer at what point in time I took my lunch, only the point of day. Of course there are exceptions to my rule when DATETIME may be used: most notably if you which to store a point of time past the year 2038 (where TIMESTAMP is out of range). But in these cases you must store the time zone in a separate column, or always know the time zone.

Java SQL API has 2 types of time methods: the ones that takes a Calendar and the ones that does not:
ResultSet.getTimestamp(int columnIndex) vs ResultSet .getTimestamp(int columnIndex, Calendar cal)
PreparedStatement.setTimestamp(int parameterIndex, Timestamp x) vs PreparedStatement .setTimestamp(int parameterIndex, Timestamp x, Calendar cal).

It is my option that the methods that takes a Calendar should never be used. My reason is found in the Javadoc of ResultSet: “…if the underlying database does not store timezone information”. Since I always use TIMESTAMP in Mysql/MariaDB I never use methods that takes a Calendar since it should be redundant since the database stores the time zone information (baked into the definition of TIMESTAMP).

Analysis of MariaDB JDBC code and suggested solution:
Using Wireshark to sniff the traffic it is easy to see that Mysql protocol used between server and JDBC is a simple protocol that often uses strings. The timestamp is transferred as a string. For example “2014-01-01 15:20:59”. This string must then be converted into a java.sql.Timestamp by the JDBC driver. So how should a client know the timezone? My understanding of the Mysql documentation is that the client and server will agree on a session timezone (http://dev.mysql.com/doc/refman/5.5/en/time-zone-support.html).

The class AbstractValueObject and method getTimestamp(Calendar cal) is the main focus. Please note that this class has changed a bit from 1.1.5 and 1.1.7. In the getTimestamp-metod a SimpleDateFormat is created. If cal is null no timezone is used for the SimpleDateFormat and the client JVM timezone is used. This may be the correct time zone for the session, but I have written my test case to demonstrate what happens when they differ.

My suggested solution is that the SimpleDateFormat should use the session time zone. I briefly looked at the code but could not see any simple way to implement this in MySQLProtocol.java. I could not find anything in my Wireshark trace or in MySQL Client/Server Protocol http://dev.mysql.com/doc/internals/en/client-server-protocol.html to specify this without executing an SQL query. One way to know the session time zone would be to force it to a specific value during the connection phase. For example always set it to UTC or the JVM default timezone.