Group by date from timestapm ejbql

[zharenkov]

Hello,

I've got query in ejbql, which select date, count of element and owner from the table. The date is stored as timestamp. What I need is to group results by date(day of year, moth,year) and owner, but my query groups by date and time. Is it possible to group by only by date, not time? I tried to use cast() but it doesn't work in group by block.

Here is my query

select cast(rqs.dateTime as date) as rqsDate,

count(rqs.typeState), rqs.gossrvcDocPackage.owner

from GossrvcPackRatingQualityService rqs

where rqs.dateTime between :startDate and :endDate

and rqs.gossrvcDocPackage.owner = :owner

and rqs.typeState = 'reject'

group by

rqs.dateTime, rqs.gossrvcDocPackage.owner

[zharenkov]

I found the solution:

select EXTRACT(day from rqs.dateTime)||'.'||EXTRACT(month from rqs.dateTime)||'.'||EXTRACT(year from rqs.dateTime),rqs.gossrvcDocPackage.owner,

count(rqs.typeState)

from GossrvcPackRatingQualityService rqs where rqs.dateTime between :startDate and

:endDate  and rqs.typeState = 'reject'  group by

EXTRACT(day from rqs.dateTime)||'.'||EXTRACT(month from rqs.dateTime)||'.'||EXTRACT(year from rqs.dateTime),

rqs.gossrvcDocPackage.owner