Tuesday, January 11, 2011

datetime fun with mysql: when do i lost my time?

In our project, we chose the data type of a column as datetime in MySQL. We built models that are in line with our tables. For the datetime attribute in the database I chose to use the Date object in Java model and the show begun.

When I tried to use my model's Date attribute for setting the corresponding field (with setDate()) on the prepared statement I saw that the object types dont match. What I have in the model is java.util.Date while the prepared statement asks for java.sql.Date. Ok then I'll convert it. I googled a bit and found a solution, or rather thought that I found a solution.

ps.setDate(1, new java.sql.Date(model.getDateField().getTime()) )

First I was converting to a millisecond unix timestamp with getTime() and then converting it to second (by dividing with 1000) and then wrapping the result into a java.sql.Date object.
The result was far from satisfying. The time part of the date (hour, minute and second) were missing in the result. The date in the model was '2010-10-26 14:13:33' and the result in the database was '2010-10-26 00:00:00'.

After that I googled a bit more and inspected few javadocs then came up with not one but two solutions.

SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
ps.setString(1, dateFormat.format( model.getDateField() ));

The first solution creates a SimpleDateFormat object which formats the Date attribute of the model as a String and we use setString() of the prepared statement for setting it.

The second solution is below.

ps.setTimestamp(1, new java.sql.Timestamp(model.getDateField().getTime()));

We first get the Date attribute and then convert it to unix timestamp in milliseconds for wrapping it into a Timestamp object and call the related setter.

I didn't notice major performance difference in one of the solutions I offer which means that you have to test it in your cases.

No comments:

Post a Comment