0

I'm trying to insert a record to MySQL table using Java. My table schema:

CREATE TABLE records (
id int(5) NOT NULL,
value varchar(30) NOT NULL,
recorded_time timestamp(3) NOT NULL 
);

My code to insert record in MySQL:

for (Record rec : records) {
    st.setInt(1, rec.getId());
    st.setString(2, rec.getValue().toString());

    Timestamp ts = new Timestamp(rec.getDatetime());
    System.out.println("New Timestamp: " + ts);

    st.setTimestamp(3, ts);

    System.out.println("Insert Statement: " + st);
    st.addBatch();
}
st.executeBatch();

where st is the preparedstatement. Output:

New Timestamp: 2017-09-18 17:59:03.362
Insert statement: INSERT INTO records ( id, value, recorded_time ) VALUES (306,'72.7','2017-09-18 17:59:03')

Value of 'datetime' field is generated using ZonedDateTime in Java8:

ZonedDateTime.ofInstant(Instant.now(), ZoneOffset.UTC).toInstant().toEpochMilli();

My question is timestamp shows the millisecs when I print it. But why the millisecs (362) not stored in the table. How to fix it.

Similar question on stackoverflow: How to insert current time in MySQL using Java. But I don't know how to use it in my case.

EDIT: MySQL version - 10.1.6

8
  • 1
    stackoverflow.com/questions/26299149/… can answer your question. Commented Sep 18, 2017 at 18:44
  • MySQL version is 10.1.6. Should I still multiply with .001? Commented Sep 18, 2017 at 18:49
  • How do you know that the milliseconds are not stored in the table? What query are you using for checking that? Commented Sep 18, 2017 at 18:55
  • I checked in the table by using SELECT statement. 306 | 72.7 | 2017-09-18 17:59:03.000 Commented Sep 18, 2017 at 18:57
  • @HaifengZhang: Yes, I thought so. But I don't know how to fix it. Any suggestions? Commented Sep 18, 2017 at 18:59

0

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.