Abstraction of time zone in MariaDB JDBC: is TIMESTAMP time zone aware?

Summary:
Almost every data type in Java is time zone aware. Therefore it would be logical that a Java developer that uses an SQL database should be spared to know details about how a time value is stored and converted/reconverted during INSERT and SELECT with respect to time zone. It should just work out of the box.

Background:
I have written this ticket
Use session time zone for ResultSet.getTimestamp(String columnLabel)
http://mariadb.atlassian.net/browse/CONJ-109

In this ticket I have found a problem: the need for a Java developer to worry about how a timestamp is converted/transformed and stored with respect to time zone is too high.

My definitions:
I use the term “point in time”. This means that I look at time as a linear scale that exists in the entire universe. One point in time is the same regardless where you are in the universe. (Probably in total violation to the relativity theory.)
I use the term “point of day” to indicate when it is time for lunch. Everybody eat lunch at “12:00”. But if you are in a different timezone you would eat lunch at a different point in time.

Time data types and time zones in Java:
There are a couple of time/date related classes in Java. The most common are: java.util.Date, java.sql.Timestamp and java.util.Calendar. Every one of these classes are what I would call time zone aware. This means that it does not matter what computer or time zone the JVM runs on, the objects of these classes all represents the same point in time.

When an object is passed between classes there is never a need to know the time zone for the object to be useful. The time object will always represent the same point in time and thus there is no need for a time zone. The only exception to this rule is the time represented by the System.currentTimeMillis(). For this value you must know the Epoch (midnight, January 1, 1970 UTC).

However when a time object is to be formatted to a String the developer must choose in what time zone the String should be written as. This conversion of time object to String uses time zone data in the JRE to perform the conversion. Past times will always be correct, but future times may not be correct because countries has governments that always will stipulate new laws about what time zone a country should belong to and rules about daylight savings time. To get the latest conversion data the Timezone Updater Tool can be used: http://www.oracle.com/us/technologies/java/tzupdater-readme-136440.html

TIMESTAMP data type in Mysql/MariaDB:
I have always though that the data type TIMESTMAP in Mysql is time zone aware. My motivation for this is the text in http://dev.mysql.com/doc/refman/5.5/en/datetime.html:
“The TIMESTAMP data type is used for values that contain both date and time parts. TIMESTAMP has a range of '1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC.”

That DATETIME data type on the other hand only represents a point of day. It is my opinion that a point of day data type has very limited use and should be avoided. In the majority of cases it is better to have a point in time data type.

The problem (as highlighted by my ticket CONJ-109) is that the TCP protocol to transfer data to and from the server is a text-ish based protocol. A TIMESTAMP value is transferred as text without any time zone context. Well, okay, actually the session time zone found in the variable time_zone is used by the server.

Some text representations of some time zones causes the time to loose resolution. For example during the transition in autumn in the end of daylight saving time in EU the time “01:15” may refer to two different points in time since the hour 01 is repeated. On the opposite end, during the spring, when daylight saving time begins the time “01:15” does not exist and it is very unclear how it should be converted into a point in time. This can be a real problem and it is shown in http://mariadb.atlassian.net/browse/CONJ-86

The Java developer perspective:
If possible, I would like it to be easy to store and retrieve a time from a database. Imagine that you are a Java developer without any knowledge about the JDBC driver, how the server stores data types or how the data types are transferred between the server and the JDBC driver. As this Java developer you have read/interpreted that the TIMESTAMP value is somewhat time zone aware. You have also read the Java API for PreparedStatement and especially http://docs.oracle.com/javase/7/docs/api/java/sql/ResultSet.html#getTimestamp%28int,%20java.util.Calendar%29 which states this about the cal parameter: “…if the underlying database does not store timezone information”. The Java developer think: well since TIMESTAMP is time zone aware I should not use this method, I should instead use the method without the cal parameter: http://docs.oracle.com/javase/7/docs/api/java/sql/ResultSet.html#getTimestamp%28int%29. It would be logical for the Java developer to assume that all that is needed to perform:
        PreparedStatement ps = connection.prepareStatement("INSERT INTO ORDERS ('candy', ?)");
        ps.setTimestamp(1, new Timestamp(System.currentTimeMillis()));
        ps.executeUpdate();
to store the time and use this to retrieve the time:
        ResultSet rs = statement.executeQuery("SELECT * FROM ORDERS");
        rs.next();
        Timestamp orderTime = rs.getTimestamp("order_time");

But this does not work! See the attached Junit test case in CONJ-109 for the specifics on how to reproduce the problem.

The endianness analogy:
An analogy that I think fits: an integer value can be sent over the network in big endian or little endian way. In Java the data type java.lang.Integer does not trouble the developer with this. The bytes stored in RAM uses big endian in Java. But the developer never have to know this. Mysql server also stores the bytes of an integer in some way (I don’t know if it is big or little). But the java developer never have to make any special conversion to SELECT an integer value with respect to endianness. The JDBC code “just deals with it” and converts (or not converts) the value to the correct endianness for Java. Do you think it would be fair for the developer to instruct the JDBC driver to use big endian for each session? Or for every integer stored in the database to add another column with a “b ig” or ”little” value?

How time zone aware is the MariaDB/Mysql server:
I have discussed this issue with some peoples working at MariaDB Corporation (mainly JDBC devs). I admit that this is a hard topic and it is easy to be confused (I have been many times). For me it all boils down to this question: “Is the TIMESTAMP data type in Mysql/MariaDB time zone aware?”.

If the answer is yes then I think CONJ-109 should be fixed to make it easier for developers.

If the answer is no, then I guess I will have to stop looking at TIMESTAMP as a complex data type that can store a point in time and instead look at is as a simple data type like a string value that at best can store a point of day. On its own it is worthless in a point of time perspective; it must always be accompanied by some offset or time zone information to be usable.