Insert Instant datetime into MySql database with millisecond precision in java

Question

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.

Huh, interesting question. Good code example; it seems like you're doing exactly everything right. — chrylis -cautiouslyoptimistic-, Jul 30 '19 at 11:15
Stay away from the outdated `Timestamp` class. Use `ps.setObject(instant);` or `ps.setObject(instant.atOffset(ZoneOffset.UTC));`. — Ole V.V., Jul 30 '19 at 11:36
@OleV.V. thank you for the reply but then what should I place in mysql datatype? — Ujjwal Jung Thapa, Jul 30 '19 at 11:41
Sorry, I was being unclear. `timestamp` is fine in the database. It's only the `java.sql.Timestamp` class that is pporly designed and outdated and which you don't want to use. — Ole V.V., Jul 30 '19 at 13:29
@OleV.V. the insert fails if I do that. setObject can be used but passing just instant, results in some format error. — Ujjwal Jung Thapa, Jul 31 '19 at 01:42
My best guess is that the second form eorks, then. `atOffset` convetts to `OffsetDateTime`, which I expect to work (I hope we don't need a`LocalDateTime`). — Ole V.V., Jul 31 '19 at 05:12
i tried second one as well. Still same format error. But thank you, with setObject idea eventually led me to right answer. — Ujjwal Jung Thapa, Jul 31 '19 at 05:37

score 4 · Accepted Answer · answered Jul 31 '19 at 06:13

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

Insert Instant datetime into MySql database with millisecond precision in java

1 Answers1