Save a java.util.Date in a MySQL DB with Millisecond precision

Question

I would like to get millisecond precision in my MariaDB. After some research, I found that I needed to change the columnDefinition - so I did this in my entity:

@NotNull
@Column(name = "createdDate", columnDefinition = "DATETIME(3) NOT NULL")
@Temporal(TemporalType.TIMESTAMP)
private TimeStamp createdDate;

@PrePersist
void onPersist() {
    createdDate = new Timestamp(new Date().getTime());
}

The resulting SQL to create the column is:

`createdDate` DATETIME(3) NOT NULL

Now, in the DB the value has indeed 3 decimals:

2016-09-12 16:57:44.000

... but they are always 000

What did I do wrong, or what did I forget ?

Edit: I tried without JAVA:

CREATE TABLE `test` (
    `id` BIGINT(20) NOT NULL AUTO_INCREMENT,
    `createdDate` DATETIME(3) NOT NULL,
    PRIMARY KEY (`id`)
)
COLLATE='latin1_swedish_ci'
ENGINE=InnoDB
;

And then:

INSERT INTO test (createdDate)
VALUES(current_timestamp())

Result:

2016-09-13 13:57:44.000

Answer 1

I had the same problem with MariaDB and date types. I've tried org.joda.DateTime and java.util.time types. Both, the server and the client code supported milliseconds correctly.

The problem was that I was using MySQL Connector instead of MariaDB Connector/J JDBC driver.

Background

In most situations using MariaDB with MySQL Connector works out well, but I would never ever recommend this. When I was searching for the issue I was debugging through Hibernate and Connector code and saw many feature detections that were based on the server version number instead of a real feature detection. The version numbering of course differs between MySQL and MariaDB. So there's a big probability that there are far more compatibility issues that are quietly ignored.