KyleAure
commented
3 months ago I think the root of this issue has to do with how EclipseLink saves date/time data to PostgreSQL. For the above test case the create query for the table was:
[eclipselink.sql] CREATE TABLE DEMOGRAPHICINFO (ID BIGINT NOT NULL, COLLECTEDON VARCHAR(255), INTRAGOVERNMENTALDEBT DECIMAL(38), NUMFULLTIMEWORKERS BIGINT, PUBLICDEBT DECIMAL(38), PRIMARY KEY (ID))The instant collectedOn was saved in PostgreSQL as VARCHAR(255) instead of as a TIMESTAMP.
So I can see two solutions:
- When creating a table, data should be stored in columns that represent what the data actually is:
| Java Type | Column Type |
|---|---|
| Instant | TIMESTAMP |
| LocalDate | DATE |
| LocalTime | TIME |
| OffsetTime | TIME WITH TIME ZONE |
| OffsetDateTime | TIMESTAMP WITH TIME ZONE |
| ZonedDateTime | TIMESTAMP WITH TIME ZONE |
NOTE: in this case ZonedDateTime stores zone offset and zone id data, PostgreSQL can store one or the other but not both.
- When extracting timestamp data from columns in PostgreSQL be pessimistic and always cast the column to the correct column type
| Dates and Times | Java Type | PostgreSQL |
|---|---|---|
| Instant | EXTRACT( YEAR FROM COLLECTEDON::TIMESTAMP ) | |
| LocalDate | EXTRACT( YEAR FROM COLLECTEDON::DATE ) | |
| LocalTime | EXTRACT( HOUR FROM COLLECTEDON::TIME ) | |
| OffsetTime | EXTRACT( HOUR FROM COLLECTEDON::TIME WITH TIME ZONE ) | |
| OffsetDate | EXTRACT( YEAR FROM COLLECTEDON::TIMESTAMP WITH TIME ZONE ) | |
| ZonedDateTime | IMPOSSIBLE |
NOTE: In this case ZonedDateTime is stored as 2007-12-03T10:15:30+01:00 Europe/Paris which PostgreSQL cannot parse because it only supports the use of an offset or zoneId but not both.
lukasj
ajaypaul-ibm
rfelcman
EclipseLink is generated a SQL query from JPQL to PostgreSQL that is rejected by the database.
For example, the JPQL SELECT this.publicDebt / this.numFullTimeWorkers FROM DemographicInfo WHERE EXTRACT (YEAR FROM this.collectedOn) = ?1
The PostgreSQL JDBC driver throws the exception: org.postgresql.util.PSQLException: ERROR: function pg_catalog.extract(unknown, character varying) does not exist
Full exception stack: