TransWikia.com

Spring Boot JPA Hibernate - Storing Date with millisecond precision

Stack Overflow Asked on November 28, 2020

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…

3 Answers

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

Try this one instead of using new Date()

Answered by Onur Baştürk on November 28, 2020

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 by Hiren on November 28, 2020

I suggest you use Instant which models a single instantaneous point on the time-line.

import java.time.Instant;
import java.time.ZoneOffset;
import java.time.format.DateTimeFormatter;

public class Main {
    public static void main(String[] args) {
        Instant now = Instant.now();
        System.out.println(now);

        // Instant to milliseconds
        System.out.println(now.toEpochMilli());

        // Milliseconds to Instant
        Instant moment = Instant.ofEpochMilli(1602120218454L);
        // Print the default representation i.e. Instant#toString
        System.out.println(moment);
        // Which is same as the following output
        String strTimestampDefaultFormat = moment.toString();
        System.out.println(strTimestampDefaultFormat);

        // Custom representation with milliseconds precision
        String strTimestamp = moment.atOffset(ZoneOffset.UTC)
                .format(DateTimeFormatter.ofPattern("uuuu-MM-dd HH:mm:ss.SSS"));
        System.out.println(strTimestamp);
    }
}

Output from a sample run:

2020-10-08T13:44:46.765240Z
1602164686765
2020-10-08T01:23:38.454Z
2020-10-08T01:23:38.454Z
2020-10-08 01:23:38.454

With strTimestamp (as obtained above), you can replace

sql.append(" (date) VALUES( "").append(dateTimeStamp).append("")");

with

sql.append(" (date) VALUES( "").append(strTimestamp).append("")");

Answered by Arvind Kumar Avinash on November 28, 2020

Add your own answers!

Ask a Question

Get help from others!

© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP