| [JPA QL] How to group by year if column type is TIMESTAMP? [message #380555] |
Fri, 29 August 2008 05:57  |
Eclipse User |
|
|
|
In my database type, I need to store the exact TIMESTAMP (= DATE + TIME)
when a row was inserted. No problem so far. But now I need to run a query
that reports the numbers of rows inserted grouped by YEAR of insertion.
While I am familiar with GROUP BY, and while I managed to write the query as
SQL, it seems that in JPA QL that is impossible: I just do not find out how
to get the YEAR part of the insertion TIMESTAMP -- since I want to have one
group per year, not one group per millisecond!
The SQL is:
SELECT
YEAR(whenRegistered) Y,
COUNT(registration.id) C
FROM registration
GROUP BY Y
I do not know how to translate the SQL clause "YEAR(x)" into JPA QL.
What to do?
Using native SQL queries is not a solution, since that would break the
transparency of the database mapping.
Thanks
Markus
|
|
|
|
|
Powered by
FUDForum. Page generated in 0.47162 seconds
Home