Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » EclipseLink » [JPA QL] How to group by year if column type is TIMESTAMP?
[JPA QL] How to group by year if column type is TIMESTAMP? [message #380555] Fri, 29 August 2008 05:57 Go to next message
Markus KARG is currently offline Markus KARG
Messages: 44
Registered: July 2009
Member
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
Re: [JPA QL] How to group by year if column type is TIMESTAMP? [message #381075 is a reply to message #380555] Tue, 02 September 2008 10:14 Go to previous messageGo to next message
James is currently offline James
Messages: 272
Registered: July 2009
Senior Member
JPQL does not support this function, you can use an EclipseLink Expression
query for this (it has a datePart() API that should work depending on your
database, otherwise use getFunction()).

You may also wish to vote for the bug,
https://bugs.eclipse.org/bugs/show_bug.cgi?id=219814


-- James
Re: [JPA QL] How to group by year if column type is TIMESTAMP? [message #381076 is a reply to message #381075] Tue, 02 September 2008 10:24 Go to previous message
Markus KARG is currently offline Markus KARG
Messages: 44
Registered: July 2009
Member
James,

thanks for that comments.

Unfortunately my solution must be vendor neutral JPA QL... :-(

Thanks
Markus

"James" <jamesssss@yahoo.com> schrieb im Newsbeitrag
news:05dd820f4f2c594d3cac5355bc211290$1@www.eclipse.org...
> JPQL does not support this function, you can use an EclipseLink Expression
> query for this (it has a datePart() API that should work depending on your
> database, otherwise use getFunction()).
>
> You may also wish to vote for the bug,
> https://bugs.eclipse.org/bugs/show_bug.cgi?id=219814
>
>
> -- James
>
Previous Topic:Where to post feature proposals?
Next Topic:logs and MOXy
Goto Forum:
  


Current Time: Sun Jul 13 11:12:19 EDT 2014

Powered by FUDForum. Page generated in 0.02334 seconds