Incorrect milliseconds in long value retrieved from MySQL in Java

Question

First of I was trying to insert timestamp in millisecond precision in my mysql database, which I got success by this code:

Instant instant = Instant.now().truncatedTo(ChronoUnit.MILLIS);

try (Connection conn = DbConnection.getCon();
        PreparedStatement ps = conn.prepareStatement("INSERT INTO test_table (timestamp) VALUES (FROM_UNIXTIME(?*0.001))");) {
    ps.setObject(1, instant.toEpochMilli());
    ps.executeUpdate();
    LOG.info("Instant: {} and long: {}", instant, instant.toEpochMilli());
} catch (SQLException ex) {
    LOG.error(ex.getMessage());
}

For this my table in database is:

CREATE TABLE `test_table` (
`id` INT NOT NULL AUTO_INCREMENT,
`timestamp` TIMESTAMP(3) NOT NULL,
PRIMARY KEY (`id`));

This is my milliseconds from instant.toEpochMilli() from my java code and timestamp in from my database:

milliseconds: 1564566174331 and timestamp: 2019-07-31 15:27:54.331

Now I wanted to retrieve this timestamp in my java code hence my code is:

    try (Connection conn = DbConnection.getCon();
            PreparedStatement ps = conn.prepareStatement("SELECT timestamp FROM test_table WHERE id=?")) {
        ps.setString(1, "14");
        ResultSet rs = ps.executeQuery();
        if (rs.next()) {
            LOG.info("milliseconds: {}", rs.getTimestamp("timestamp").toInstant().toEpochMilli());
        } else {
            LOG.info("No data");
        }
    } catch (SQLException ex) {
        LOG.error(ex.getMessage());
    }

But what I got for total timestamp in milliseconds for this is:

milliseconds: 1564566174000

Somehow that 331 value in millisecond place is gone.

How to RETRIEVE complete timestamp value in millisecond precision. Now I am getting more confused. What am I missing?

What I tried:

A) I tried getTime() of ResultSet for timestamp to retrieve total time in milliseconds as:

LOG.info("milliseconds: {}", rs.getTimestamp("timestamp").getTime());

Still not retrieving that milliseconds place value. Same result as above.

B) I tried getObject() as well and TO MY ASTONISHMENT the milliseconds place value is somehow placed at NANOSECONDS place.

LOG.info("object: {}", rs.getObject("timestamp").toString());

What I got:

object: 2019-07-31 15:27:54.000000331

I mean your jdbc driver version, because I have just tested, it's normal — yelliver, Jul 31 '19 at 11:32
I use java 8, mysql-8.0.17, mysql-connector-java 8.0.17 and it's totally normal, maybe you should try new version of jdbc first — yelliver, Jul 31 '19 at 12:05
Don't use the outdated `java.sql.Timestamp` class. Use `rs.getObject("timestamp", Instant.class)`. (Or `OffsetDateTime` instead of `Instant`, not sure; you are specifying the type of the object to retrieve.) — Ole V.V., Aug 01 '19 at 12:26

score 0 · Answer 1 · answered Jun 01 '20 at 08:41

0

I was also facing same Issue , i changed Database column to long/int .and inserted Directly milliseconds.it reduced lots of code and complexity for me .

answered Jun 01 '20 at 08:41

Ram Prasad

9
7

Incorrect milliseconds in long value retrieved from MySQL in Java

1 Answers1