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
– Basil Bourque, Commented Jan 22, 2020 at 20:57
Provide a MCVE, creating table, inserting rows with your JPA. — Basil Bourque
– Basil Bourque, Commented Jan 22, 2020 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
– All Pereira, Commented Jan 22, 2020 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 — user330315
– user330315, Commented Jan 22, 2020 at 21:26
You need JPA 2.2 or later to support java.time types. See Jakarta Persistence. Search to learn more. — Basil Bourque
– Basil Bourque, Commented Jan 24, 2020 at 0:03

All Pereira · Accepted Answer · 2020-01-28 18:03:38Z

2

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.

answered Jan 28, 2020 at 18:03

All Pereira

1491 gold badge4 silver badges13 bronze badges

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

Collectives™ on Stack Overflow

Problem with Java LocalDate / LocalDateTime fields mapped in PostgreSQL colunms

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related