CODEWITHSUNDEEP
jpamariadb
Tim
Tim

Reputation: 4274

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

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

Upvotes: 7

Views: 8339

Answers (3)

Alex
Alex

Reputation: 744

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.

Upvotes: 9

klonq
klonq

Reputation: 3587

To do this using pure JPA :

@Column(name="STMP", columnDefinition = "TIMESTAMP (6)")
private Timestamp timestamp = Timestamp.from(Instant.now());

Upvotes: 2

niilzon
niilzon

Reputation: 426

Your problem most probably comes from the fact that you mix Dates and Timestamps. Changing the createdDate type to java.sql.Timestamp should solve your issue.

Also, if your version of MySQL is prior to 5.6.4, DateTime won't let you save time fractions.

EDIT after OP's edit :

you are still mixing the Date Java type with Timestamp when you do this : 

createdDate = new Timestamp(new Date().getTime());

Can you try createdDate = new Timestamp(System.currentTimeInMilliseconds()); instead ?

Ideally you should use objects from a library like JodaTime to avoid such issues, but that's beyond the point of your question, just a tip :)

Ultimately, if this way of creating your Timestamp does not work, I would use the Timestamp type in DB instead of Datetime, but that's just trial and error as Datetime should work as well in your example.

Edit : excerpt from Oracle's Date API : 

Date()
Allocates a Date object and initializes it so that it represents the time at which it was allocated, measured to the nearest millisecond.

In which case using System.currentTimeInMilliseconds() shouldn't change the outcome - my bad.

To troubleshoot the problem, I'd start to create a date via SQL (without passing via Java objects) with CURRENT_TIMESTAMP to make sure that the field can indeed contain decimal time precision.. If it is OK, verify the value in the Java object with a debugger.. Might give you a lead. If both contain milliseconds, I'd look at the usage of the annotations or start from a working sample.

Upvotes: 2

Related Questions