8

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.

ljs.dev
  • 4,449
  • 3
  • 47
  • 80
Rory
  • 1,805
  • 7
  • 31
  • 45
  • 6
    Did you try `getTime()` or `getTimeStamp()`? – Thomas Oct 24 '11 at 11:55
  • possible duplicate of [Handling MySQL datetimes and timestamps in Java](http://stackoverflow.com/questions/3323618/handling-mysql-datetimes-and-timestamps-in-java) – BalusC Oct 25 '11 at 01:07

2 Answers2

8

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

NightWolf
  • 7,694
  • 9
  • 74
  • 121
  • 1
    `java.sql.Timestamp` is a subclass of `java.util.Date`. You can just upcast instead of recreating it. – BalusC Oct 25 '11 at 01:05
1

Just to clarify, only getTimesStamp() will return the Date and time in one shot when querying Mysql :

Column: startTime: value: 2014-09-29 20:49:41

Java:

java.sql.Timestamp dbSqlTimestamp = rs.getTimestamp("startTime");
System.out.println("dbSqlTimestamp="+dbSqlTimestamp);

output: dbSqlTimestamp=2014-09-29 20:49:41.0

Salim R
  • 343
  • 7
  • 16