CODEWITHSUNDEEP
springpostgresqlspring-boothibernatespring-data-jpa
sumek
sumek

Reputation: 28232

Postgres + Spring Boot JPA - store timestamp with nanosecond precision

Postgres doesn't support timestamp precision below 1 microsecond. The suggested solution is to store timestamp across two separate fields: one with timestamp up to second precision and separately nanoseconds. My question is how to implement it in Spring Boot so that I can still can have my field with nanosecond precision. How to split the field into two when storing in database and how to hydrate field in the object correctly based on two columns?

I'm currently on Spring Boot 2.5.14, Java 11 or Kotlin 1.6 - depending on the project, and Postgres 14.4. If there was a solution that required upgrade we can probably do it, the upgrades are planned anyway.

Upvotes: 0

Views: 782

Answers (1)

Christian Beikov
Christian Beikov

Reputation: 16452

You could create an AttributeConverter for BigDecimal/BigInteger to Instant and store the UTC timestamp as numeric(28,9)/numeric(28,0) which should be good enough to store timestamps. Just note that you will have to cast to the timestamp type before using that field in timestamp arithmetic functions.

Upvotes: 1

Related Questions