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,620
11
18

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

685
4
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

Arvind Kumar Avinash · Answer 3 · 2020-10-08T17:41:46.247

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("\")");

@Alex - I've suggested you use `Instant` (which is part of the [modern date-time API](https://www.oracle.com/technical-resources/articles/java/jf14-date-time.html) of Java) rather than using legacy `java.sql.Timestamp`. — Arvind Kumar Avinash, Oct 08 '20 at 14:19
then in your case I must change in the database field Timestamp to Varchar? than how I compare string with datetime? or in your case transfer string to unixtime in database for compare and return string... im not understand. — Alex, Oct 08 '20 at 14:39
dear @ Arvind Kumar Avinash - im not need save to db with jdbctemplate and changed sql. Its working very well. But I need jpa/hibernate. This its my problem. — Alex, Oct 09 '20 at 20:20

Spring Boot JPA Hibernate - Storing Date with millisecond precision

3 Answers3