Support nanosecond precision by converting to Timestamp instead of Date [DATAJDBC-637]

[spring-projects-issues]

Jens Schauder opened DATAJDBC-637 and commented

Date like properties (as for example LocalDateTime) get converted to java.util.Date which only has millisecond precision. While LocalDateTime and java.sql.Timestamp have nanosecond precision.

---

Reference URL: https://stackoverflow.com/questions/64886345/spring-data-jdbc-microsecond-precision-lost-during-insert-of-localdatetime-type

Referenced from: pull request #255

Backported to: 2.1.3 (2020.0.3), 2.0.7 (Neumann SR7)

[spring-projects-issues]

Jens Schauder commented

The current solution caused problems at least with MySql. Rolling the changes back right now

[milanplesa]

noticed this problem yesterday so i was confused. Used java.time.Instant to save to H2 TIMESTAMP WITH TIME ZONE and after that retrieve the same object. Printing objects before and after save indicated different createdAt field (lacking precision). Tried this example against postgres db and problem is the same.

spring-data-jdbc version: 3.0.5

jdbcTemplate.update("""
                    CREATE TABLE toy
                     (
                        id          BIGINT auto_increment PRIMARY KEY,
                        name        VARCHAR(255),
                        created_at  TIMESTAMP WITH TIME ZONE,
                        created_by  VARCHAR(255),
                        modified_at TIMESTAMP WITH TIME ZONE,
                        modified_by VARCHAR(255)
                     )
                    """);

            var toy = new Toy("Batman");
            var insertedToy = toyRepository.save(toy);
            var retrievedToy = toyRepository.findById(insertedToy.id);
            System.out.println(insertedToy);
            // createdAt=2023-04-19T10:14:51.247964454Z
            System.out.println(retrievedToy.orElseThrow());
            // createdAt=2023-04-19T10:14:51.247964Z

public class Toy {

    @Id
    public Long id;
    public String name;
    @CreatedDate
    public Instant createdAt;
    @CreatedBy
    public String createdBy;
    @LastModifiedDate
    public Instant modifiedAt;
    @LastModifiedBy
    public String modifiedBy;
}

[schauder]

H2 uses precision 6 as the default (http://h2database.com/html/datatypes.html#timestamp_type):

The timestamp with time zone data type. The proleptic Gregorian calendar is used. If fractional seconds precision is specified it should be from 0 to 9, 6 is default.

Postgres only supports a precision up to 6 (https://www.postgresql.org/docs/current/datatype-datetime.html)

time, timestamp, and interval accept an optional precision value p which specifies the number of fractional digits retained in the seconds field. By default, there is no explicit bound on precision. The allowed range of p is from 0 to 6.

Spring Data JDBC sends the data with full precision to the database which throws parts of it away.
I don't think there is much we can do about.

If you still think there is a bug or an improvement to be made, please create a new ticket instead of replying to a closed one.