Time in MySQL and JDBC

MySQL offers a number of internal data types for representing time. The most important data types to be aware of are the DATE, TIME, and DATETIME types. DATE is used to represent calendar days. TIME is used to represent times within a given day, and DATETIMEs are used to represent both day and time information.

To get time information into or out of a MySQL database we will be using PreparedStatements and ResultSets. The ResultSet class offers getDate(), getTime(), and getTimestamp() methods to read data from MySQL DATE, TIME, and DATETIME fields, respectively. (Somewhat confusingly, MySQL also has a TIMESTAMP data type. This data type is most commonly used internally by MySQL itself to record information such as when a given row in a table was last updated. I recommend that you avoid using the TIMESTAMP data type in MySQL and use DATETIME in its place.)

A further complication with these methods is that they do not return Calendar objects. Instead, getDate() returns a java.sql.Date object, getTime() returns java.sql.Time, and getTimestamp() returns java.sql.Timestamp. Fortunately, each of these classes extends the java.util.Date class and can be used in combination with the Calendar class's setTime() method.

Here is an example. In this example we are doing a query that will return data from a field of type DATETIME.

Calendar outTime = Calendar.getInstance();
String query = "select when from events where source="+id;
ResultSet results = statement.executeQuery(query);
if(results.next()) {
	Timestamp t = results.getTimestamp(1);
  outTime.setTime(t);
}

Similarly, you can use the PreparedStatement's setTimestamp() method to load a time value into a table. Converting a Calendar into a Timestamp is slightly more complicated - in this example I assume that the time in question is stored in a Calendar object c.

String updateSQL = "update events set when=? where source="+id;
PreparedStatement stmt = connection.prepareStatement(updateSQL);
Timestamp t = new Timestamp(c.getTimeInMillis());
stmt.setTimestamp(1,t);
stmt.executeUpdate();

Another problem that comes up commonly when working with time in JDBC applications is to find rows based on a time comparison. Time comparisons are easy to do in SQL with the '<' and '>' comparison operators. Here is an example. Suppose we wanted to select a set of events stored in a table whose when field is less than some time. Suppose further what we have a Calendar object c that represents that time. Here is how we would run that query.

String querySQL = "select * from events where when < ?";
PreparedStatement stmt = connection.prepareStatement(querySQL);
Timestamp t = new Timestamp(c.getTimeInMillis());
stmt.setTimestamp(1,t);
ResultSet results = stmt.executeQuery();
while(results.next())
	{
	// Process the results
	}