CODEWITHSUNDEEP
javahibernatespring-data-jpamariadb
Alex
Alex

Reputation: 21

Spring Boot JPA Hibernate - Storing Date with millisecond precision

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...

Upvotes: 2

Views: 9256

Answers (3)

Peter
Peter

Reputation: 352

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

Upvotes: 0

Onur Başt&#252;rk
Onur Başt&#252;rk

Reputation: 735

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

Try this one instead of using new Date()

Upvotes: 1

hiren
hiren

Reputation: 1812

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;

Upvotes: 1

Related Questions