JPA date truncation group by using POSTGRES and eclipselink

Question

I am trying to truncate a date, and group by the values. I have tried this:

JPA

select v.sop, FUNC('DATE_TRUNC', 'day', v.scrappedAt) as dt, sum(v.totalValue) 
from TABLE v 
where v.coordStatus like 'done%' and (:plant is null or v.target = 
:plant) and v.scrappedAt is not null 
group by v.sop, dt

I have also tried grouping by the FUNC

select v.sop, FUNC('DATE_TRUNC', 'day', v.scrappedAt) as dt, sum(v.totalValue) 
from TABLE v 
where v.coordStatus like 'done%' and (:plant is null or v.target = 
:plant) and v.scrappedAt is not null 
group by v.sop, FUNC('DATE_TRUNC', 'day', v.scrappedAt)

The error is the same

org.postgresql.util.PSQLException: ERROR: column "t_mcp_verschrottungs_db_sharepoint.scrappedat" must appear in the GROUP BY clause or be used in an aggregate function

Answer 1

Strictly speaking, if FUNC were a native Postgres function, then your query should be working, and the query would be ANSI compliant. After reading this SO question, it appears that Postgres can't figure out that the two FUNC calls are actually the same thing. Perhaps you can rephrase the sum using a correlated subquery:

SELECT
    v1.sop,
    FUNC('DATE_TRUNC', 'day', v1.scrappedAt) AS dt,
    (SELECT SUM(totalValue) FROM TABLE v2
     WHERE v2.sop = v1.sop AND
         FUNC('DATE_TRUNC', 'day', v2.scrappedAt) =
         FUNC('DATE_TRUNC', 'day', v1.scrappedAt) AND
         v2.acoordStatus LIKE 'done%' AND
         (:plant is null OR v2.target = :plant) AND
         v2.scrappedAt IS NOT NULL) value_sum
FROM TABLE v1
WHERE
    v1.coordStatus LIKE 'done%' AND
    (:plant is null OR v1.target = :plant) AND
    v1.scrappedAt IS NOT NULL;

An alternative to the above, should it either not work or not be performant, would be to use a native Postgres query.