Problem with Java LocalDate / LocalDateTime fields mapped in PostgreSQL colunms

Question

I have an application that contains some JPA classes that are using LocalDate / LocalDateTime fields and these fields are being mapped into PostgreSQL columns as Bytea.

The greatest problem with this approach is that I can't do queries with SQL, for example, I can't query this: SELECT * FROM star_date BETWEEN '2020-01-01' AND '2020-01-02', because DateTime and date columns using the Bytea type.

Below a have an example of a class that shows my current problem scenery! This class is using JPA to generate the table in PostgreSQL, so it happens automatically. Look at created fiend into class and design of the table.

@Data
@Entity
@Table(name = "processes")
public class Process implements Serializable {
    @Id
    @GeneratedValue(generator = "ProcessSequenceGenerator")
    private Long id;
    private Long legacyId;
    private String createdBy;
    private LocalDateTime created;
}

The table design:

Has somebody ever had an issue like this one?

What are the data types of your columns, *exactly*? Are you saying the columns are of type `BYTEA`? — Basil Bourque, Jan 22 '20 at 20:57
Provide a [MCVE](https://stackoverflow.com/help/mcve), creating table, inserting rows with your JPA. — Basil Bourque, Jan 22 '20 at 20:58
@BasilBourque I have a class that is using a field like this one: private LocalDateTime created; I use JPA, then when JPA creates the database that is a postgresql, the column for this field is typed as a Bytea. Do you got it? — All Pereira, Jan 22 '20 at 21:11
The joys of obfuscation layers. Just create the table manually, then you can be sure they are created with the correct types. e.g. `timestamp` which can easily be read into a `LocalDateTime` — , Jan 22 '20 at 21:26
@BasilBourque 1.4.7.RELEASE it's from spring-boot-starter-data-jpa — All Pereira, Jan 23 '20 at 09:02
You need JPA 2.2 or later to support *java.time* types. See [*Jakarta Persistence*](https://jakarta.ee/specifications/persistence/). [Search to learn more](https://duckduckgo.com/?q=jpa+java.time&t=h_&ia=web). — Basil Bourque, Jan 24 '20 at 00:03

score 1 · Answer 1 · answered Jan 28 '20 at 18:03

I'm using Spring Boot 1.4.7.RELEASE! So a fix my problem including into Column the property columnDefinition and a @Convert like below:

@Column(nullable = false, updatable = false, columnDefinition = "TIMESTAMP")
@Convert(converter = LocalDateTimeConverter.class)
private LocalDateTime created;

Right now, I'm looking for a way to convert bytea that is into my current table in postgresql.

Problem with Java LocalDate / LocalDateTime fields mapped in PostgreSQL colunms

1 Answers1