Error with timestamp in postgres using Spring Data: column $COLUMN_NAME is of type timestamp without time zone but expression is of type bytea

Question

Using the following:

Oracle JDK 1.8.0._171

Spring boot 1.5.9-RELEASE

Postgresql 9.6

Postgres driver: 42.2.4

Getting the error from following definition:

Table column: sql meal_time TIMESTAMP DEFAULT now() NOT NULL

Entity attribute definition: java @Column(name = "meal_time", nullable=false) private Instant mealTime = Instant.now();

Whenever I try to flush or query the entity throws the error:

Caused by: org.postgresql.util.PSQLException: ERROR: column "meal_time" is of type timestamp without time zone but expression is of type bytea
  Hint: You will need to rewrite or cast the expression.

I could write a converter for this field but there should be a standard way and it feels like I am missing something as looking around for examples I found very close implementations that actually work.

Have you tried including hibernate-java-8 in your dependencies? spring-boot-starter-data-jpa:1.5.9 depends on hibernate-core:5.0.12, which did not include support for Java 8's Date and Time API types AFAIK (I think it is included in hibernate-core for newer Hibernate versions, though) — crizzis
– crizzis, Commented Jul 23, 2018 at 21:15
@crizzis - thank you for the feedback. Just tried as you suggested but unfortunately same error perists. I will write a converter by now but still looking for a better, standard solution. — groo
– groo, Commented Jul 23, 2018 at 22:33
and where have you told your JPA provider to persist the Instant as a TIMESTAMP? You haven't — user3973283
– user3973283, Commented Jul 24, 2018 at 7:39

groo · Accepted Answer · 2018-09-13 11:35:34Z

7

After some research I've found this page, Using Java 8 Date and Time classes from official postgres documentation and changed the property type to be LocalDateTime instead of Instant:

java @Column(name = "meal_time", nullable = false) private LocalDateTime mealTime = LocalDateTime.now();

This fixed the issue and was an acceptable solution in this specific project.

edited Sep 13, 2018 at 11:35

answered Jul 24, 2018 at 0:06

groo

4,4787 gold badges51 silver badges75 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

stemadsen · Accepted Answer · 2020-05-28 11:59:34Z

1

As @crizzis points out, in Hibernate core module org.hibernate:hibernate-core (https://mvnrepository.com/artifact/org.hibernate/hibernate-core) the Java 8 type mappings, including mappings from/to java.time.Instant, are included from version 5.2 and onwards. If using a hibernate-core version prior to 5.1, these type mappings are included in the special module org.hibernate:hibernate-java8 (https://mvnrepository.com/artifact/org.hibernate/hibernate-java8). Note that this module is empty from version 5.2 (where it was moved to hibernate-core), so I recommend using version 5.1.17.Final, e.g

compile group: 'org.hibernate', name: 'hibernate-core', version: '5.4.16.Final'

answered May 28, 2020 at 11:59

stemadsen

1,9012 gold badges16 silver badges15 bronze badges

Collectives™ on Stack Overflow

Error with timestamp in postgres using Spring Data: column $COLUMN_NAME is of type timestamp without time zone but expression is of type bytea

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related