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

user2782405
  • 393
  • 1
  • 6
  • 20
  • 1
    https://stackoverflow.com/questions/26299149/timestamp-with-a-millisecond-precision-how-to-save-them-in-mysql can answer your question. – mkalsi Sep 18 '17 at 18:44
  • MySQL version is 10.1.6. Should I still multiply with .001? – user2782405 Sep 18 '17 at 18:49
  • How do you know that the milliseconds are not stored in the table? What query are you using for checking that? – Mick Mnemonic Sep 18 '17 at 18:55
  • I checked in the table by using `SELECT` statement. 306 | 72.7 | 2017-09-18 17:59:03.000 – user2782405 Sep 18 '17 at 18:57
  • @HaifengZhang: Yes, I thought so. But I don't know how to fix it. Any suggestions? – user2782405 Sep 18 '17 at 18:59
  • `Insert statement: INSERT INTO records ( id, value, recorded_time ) VALUES (306,'72.7','2017-09-18 17:59:03')` the fractions were truncated before you insert into database. check your setTimestamp() mehtod – Haifeng Zhang Sep 18 '17 at 18:59
  • @user2782405 this was reported as a bug https://bugs.mysql.com/bug.php?id=40279 The nano fractions were truncated when setTimestamp(). One solution is you set it to varchar/string instead, then keep the full precision. – Haifeng Zhang Sep 18 '17 at 19:13
  • I checked the link before posting. Since the thread was last updated in 2015, I thought it would have been fixed by now. I had no idea about it. Thank you @HaifengZhang – user2782405 Sep 18 '17 at 19:15

0 Answers0