CODEWITHSUNDEEP
javamysqljdbctimestampjava.time.instant
Ujjwal Jung Thapa
Ujjwal Jung Thapa

Reputation: 634

Insert Instant datetime into MySql database with millisecond precision in java

I am using mysql 5.7.x version. Also i am using java 8. I am trying to insert java.time.instant current datetime in millisecond precision into my mysql database from java code. For that I am using preparedstatement.

The table in my database is:

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

My java code to insert is:

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

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

From my log, I can see instant with milliesecond as: 2019-07-30T10:52:34.865Z. But in my mysql database it becomes: 2019-07-30 10:52:34.000Z

I have searched so many questions and answers in stack but none seems to work for me.

Update 1:

I tried using setObject as:

ps.setObject(1, Timestamp.from(instant));

But still same result. Cannot retrieve the milliseconds in database.

Upvotes: 4

Views: 3498

Answers (1)

Ujjwal Jung Thapa
Ujjwal Jung Thapa

Reputation: 634

MySQL has FROM_UNIXTIME() function that can take long values inside and returns a representation of the unix_timestamp in 'YYYY-MM-DD hh:mm:ss' or 'YYYYMMDDhhmmss.uuuuuu' format. Reference from their manual here: FROM_UNIXTIME()

Since I have milliseconds since epoch, thus I have to use

FROM_UNIXTIME(instant.toEpochMilli() * 0.001)

in my insert statement.

Yes nothing needs to be changed in database. The only things that are changed are in java code which are setObject() function used for preparedStatement, passing instant.toEpochMilli() as the argument there and finally use of FROM_UNIXTIME() function inside insert statement.

My final java code looks something like this:

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());
    }

Upvotes: 4

Related Questions