java - MySQL datetime not returning time

Question

I have a MySQL db with a table containing a datetime column.

However, I cannot find a way in Java to return this as some sort of Time object within Java.

I can call resultset.getString("Date") and it will return the datetime, but this is no good as there is no way to compare dates, etc., on the string.

I can also call resultset.getDate("Date"), but this doesn't return the time at all.

Answer 1

You need to use the getTime() or getTimestamp() methods as suggested in the comment by Thomas. To give an example however...

Say for a table you query like this: rs = stmt.executeQuery("select timeCol, dateCol, dateTimeCol from dateTimeTable");

You could do:

java.sql.Time dbSqlTime = rs.getTime(1);
java.sql.Date dbSqlDate = rs.getDate(2);
java.sql.Timestamp dbSqlTimestamp = rs.getTimestamp(3);

If you want to use the Java date object:

java.util.Date dbSqlTimeConverted = new java.util.Date(dbSqlTime.getTime());
java.util.Date dbSqlDateConverted = new java.util.Date(dbSqlDate.getTime());

I would also check out JodaTime for working with Dates in Java, makes life much simpler.

Finally, its worth noting that there are a few differences between Timestamp and DateTime in MySQL. Namely that Timestamp has a timezone and the server will return a queried Timestamp in the Server's local time (which can be annoying). My advice is to use DateTime and always keep dates/times in the same timezone (i.e. UTC). See http://dev.mysql.com/doc/refman/5.0/en/datetime.html