Eclipse Community Forums: EclipseLink » [JPA QL] How to group by year if column type is TIMESTAMP?

Help

Home

Home » Eclipse Projects » EclipseLink » [JPA QL] How to group by year if column type is TIMESTAMP?

Show: Today's Messages :: Show Polls :: Message Navigator

[JPA QL] How to group by year if column type is TIMESTAMP? [message #380555]

Fri, 29 August 2008 09:57

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

Report message to a moderator

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 14:14

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

Report message to a moderator

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 14:24

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
>

Report message to a moderator

Previous Topic:	Where to post feature proposals?
Next Topic:	logs and MOXy

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 25 14:26:39 GMT 2024

.:: Contact :: Home ::.

Breadcrumbs

Sign up to our Newsletter