Inserting Timestamp into MySQL from Java application

Question

I am creating a Java application that communicates with MySQL database. Using XAMPP 5.6.33-0 and phpMyAdmin. I have the following method that, among other values, inserts a Timestamp into the table RATING:

PreparedStatement pst = myConn.prepareStatement("INSERT INTO RATING  
       (ratingDate) VALUES(?)");
        java.util.Date today = new java.util.Date();
        Timestamp ts = new java.sql.Timestamp(today.getTime());
        pst.setTimestamp(1, ts);
        pst.executeUpdate();

The schema of the RATING relation looks as follows:

CREATE TABLE RATING
(cID INT,
 rID INT,
 stars INT,
 ratingDate TIMESTAMP,
 FOREIGN KEY(cID) REFERENCES CUSTOMER(cID) on delete cascade,
 FOREIGN KEY(rID) REFERENCES ROOM(rID)
 ) ;

So attribute ratingDate is defined as Timestamp. Everything works great except when the Timestamp is inserted its value is always set to all zeros: 0000-00-00 00:00:00

I tried converting the Timestamp to string using t.toString and can clearly see that the Timestamp object is created properly. It seems the problem is with setTimestamp() method. Also, converting the data type of ratingDate to just Date and using setDate() method works fine, but setTimestamp() function always sets the attribute value to all zeros.

There are, of course, workaround for this. I could declare the date as varchar, convert Timestamp to a String and insert it using setString() but I am really wondering what the problem may be. Running Eclipse with Tomcat server. No errors in console.

Thank you in advance for any help, I'd be happy to provide any other necessary information.

Answer 1

After some additional research I figured it out. The problem was that the java Timestamp object uses milliseconds at the end while the timestamp attribute in the MySQL table didn't (it was in the format "yyyy-MM-dd HH:mm:ss"). So this mismatch prevented the insertion of the correct timestamp and instead put a tuple with all zeros into MySQL table. The solution is to format the Timestamp object in the java code to cut off the milliseconds and then insert the Timestamp object into MySQL table:

java.util.Date today = new java.util.Date();
java.sql.Timestamp timestamp = new java.sql.Timestamp(today.getTime());
SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); 
pst.setObject(4, formatter.format(timestamp)); 

This worked like a charm. Hope it helps somebody!