Spring Boot JPA Hibernate - Storing Date with millisecond precision

Question

Spring Boot version '2.3.4.RELEASE'
Java 11
org.springframework.boot:spring-boot-starter-jdbc
org.springframework.boot:spring-boot-starter-data-jpa
spring-data-jpa-2.3.4.RELEASE
runtime(mysql:mysql-connector-java)

Server DB MariaDB (ver. 10.5.5-MariaDB)
Java MariaDB Connector J:2.6.0[stable]

I'm trying to persist a java.sql.Timestamp object in Hibernate with millisecond precision. I need save dates to db with milliseconds. For example: 2020-10-08 03:23:38.454.

my domain:

import java.sql.Timestamp;
@Entity
@Data
@Table(name = "date_test")
public class DateTestDomain {
    @Id
    @Column(nullable = false, name = "date", columnDefinition = "TIMESTAMP(3)")
    @Temporal(TIMESTAMP)
    private Calendar dateTest;
}

my repo:

@Repository
public interface DateTestRepo extends JpaRepository<DateTestDomain, Timestamp> {
}

save date to db:

private final JdbcTemplate db;
...
        long testTime = 1602120218454L;
        Timestamp dateTimeStamp = new Timestamp(testTime);
        db.update("INSERT INTO date_test" + " (date) VALUES( \"" + dateTimeStamp + "\")");

UPD: Result of sql is right as I need!!! This method working perfect: 2020-10-08 03:23:38.454

But with hibernate/JPA result is FALSE.
Debug Trace:
2020-10-09 22:26:53.120 Hibernate: insert into date_test (date) values (?)
2020-10-09 22:26:53.122 TRACE 95038 --- [ restartedMain] o.h.type.descriptor.sql.BasicBinder : binding parameter [1] as [TIMESTAMP] - [2020-10-09 22:26:53.044]

        Calendar calendar = Calendar.getInstance();
        Date date = new Date();
        calendar.setTimeInMillis(date.getTime());
        dateTestDomain.setDateTest(calendar);

        dateTestRepo.save(dateTestDomain);

Result of sql: the fractional seconds are always set to .000 with hibernate sql insert:

2020-10-09 22:26:53.000

please help. I need save to db time with millisecond precision throw JPA.

UPD:

I try sql dialect:
org.hibernate.dialect.MySQL5InnoDBDialect than org.hibernate.dialect.MySQL55InnoDBDialect than org.hibernate.dialect.MariaDB103Dialect than org.hibernate.dialect.MariaDB105Dialect
without success.
UPD 1:
Hibernate: INSERT INTO date_test (timestamp, local_date_time, local_date_timea) VALUES (NOW(3), ?, ?)
2020-10-10 15:33:29.099 TRACE 44072 --- [ restartedMain] o.h.type.descriptor.sql.BasicBinder : binding parameter [1] as [TIMESTAMP] - [2020-10-10 15:33:29.051]
2020-10-10 15:33:29.100 TRACE 44072 --- [ restartedMain] o.h.type.descriptor.sql.BasicBinder : binding parameter [2] as [TIMESTAMP] - [java.util.GregorianCalendar[time=1602336809051,areFieldsSet=true...

Result SQL:
2020-10-10 15:33:29.101, 2020-10-10 13:33:29.000, 2020-10-10 15:33:29.000.

And one more problem:
DB dates:
2020-10-10 16:19:42.578
2020-10-10 16:20:47.000
2020-10-10 16:20:47.888
2020-10-10 16:20:47.892
2020-10-10 16:20:47.896
2020-10-10 16:20:47.900
Hibernate: select datetestdo0_.timestamp as timestam1_0_ from date_test datetestdo0_ where datetestdo0_.timestamp>?
binding parameter [1] as [TIMESTAMP] - [2020-10-10 16:20:47.893]
2020-10-10 16:20:47.888
2020-10-10 16:20:47.892
2020-10-10 16:20:47.896
2020-10-10 16:20:47.9

jdbcsql:
select timestamp from date_test where timestamp>"2020-10-10 16:20:47.893"
2020-10-10 16:20:47.896
2020-10-10 16:20:47.900

jpa/hibernate not working with milliseconds...

You can append to the sql a formatted string of the time with millisecond precision. https://stackoverflow.com/questions/1459656/how-to-get-the-current-time-in-yyyy-mm-dd-hhmisec-millisecond-format-in-java — Dario, Oct 08 '20 at 13:23
Formatted string? I have Timestamp - timestamp formatted with milliseconds. — Alex, Oct 08 '20 at 14:22
Replace .append(dateTimeStamp) with .append(YourFormattedString). You can find how to get a formatted string representing date and time in the link — Dario, Oct 08 '20 at 15:52
@Dario in this place - not is my problem. jdbctemplate working true, but in the last 3 row: my problem hibernate dateTestRepo.save(dateTestDomain) are always set to .000 timestamp. This is my problem. I, not need format the timestamp to string or change the sql query, i need that jpa save to db my timestamp with milliseconds same as jdbctemplate. — Alex, Oct 08 '20 at 16:06
For starters you should be comparing equal things. What you are doing is passing a static SQL with the result of `Timestamp.toString` into the query. What you should have been doing is `db.update("INSERT INTO date_test (date) VALUES(?);", timestamps);` as that will use proper sql conversion which JPA/hibernate is also using. Finally add an `@Temporal(TIMESTAMP)` to your field definition. — M. Deinum, Oct 08 '20 at 18:02
@M. Deinum One more... I need in the db this date time format: 2020-10-08 03:23:38.454. I add annotation @Temporal(TIMESTAMP). but after save to db with JPA/Hibernate in db truncate milliseconds to .000. — Alex, Oct 09 '20 at 20:07

score 1 · Answer 1 · answered Oct 10 '20 at 08:55

1

Since JPA 2.2 there is support of java8 date and time API. I have not tried if it will solve your problem or not but can you try with java8's LocalDateTime instead of Calendar type.

Replace:

@Id
@Column(nullable = false, name = "date", columnDefinition = "TIMESTAMP")
@Temporal(TIMESTAMP)
private LocalDateTime localDateTime;

answered Oct 10 '20 at 08:55

hiren

1,742
13
20

thank, but @Temporal should only be set on a java.util.Date or java.util.Calendar property. https://docs.jboss.org/hibernate/jpa/2.1/api/javax/persistence/TemporalType.html – Alex Oct 10 '20 at 12:07
As i mentioned they are supporting other types as well since 2.2 version. https://www.baeldung.com/jpa-java-time check this – hiren Oct 10 '20 at 13:01
with annotation Temporal should only be set on a java.util.Date or java.util.Calendar property. Without - yes, are supporting other types. Please see your link one more. in point 5 nothing about annotation Temporal. But this not important for new hibernate, where changed to new annotation org.hibernate.annotations.Type( type = "calendar" ) . But all the same not working. – Alex Oct 10 '20 at 13:20
In that case can you try without @Temporal ? – hiren Oct 10 '20 at 13:56
im trying all what is can... its not working. and select from db same not working as must be. – Alex Oct 10 '20 at 14:28
Hi Alex, I tried locally and when I save to db I am getting second precision as you wanted. (i.e 2020-10-10 21:37:55.102). Even with your configuration of entity. – hiren Oct 10 '20 at 16:14

score 1 · Answer 2 · answered Oct 10 '20 at 10:43

1

Calendar calendar = Calendar.getInstance();
calendar.setTimeInMillis(calendar.getTimeInMillis());

Try this one instead of using new Date()

answered Oct 10 '20 at 10:43

Onur Baştürk

715
5
15

thanks. if you see debug trace sql query, that its not problem in value. Value sending with milliseconds. After hibernate get the time truncate milliseconds and save without. – Alex Oct 10 '20 at 12:10

score 0 · Answer 3 · answered Oct 03 '22 at 20:45

This is not a direct solution but a work around, works perfectly: Instead of storing the actual date, just store the long value of your date, by calling the date.getTime() method, store this result, when getting the date from the date use new Date(longValue) to recreate your original date using the longValue stored in your database, this approach at least preserves your millisecond precision

Spring Boot JPA Hibernate - Storing Date with millisecond precision

3 Answers3