1 Reply Latest reply on Nov 27, 2014 6:46 AM by Danila Zharenkov

    Group by date from timestapm ejbql

    Danila Zharenkov Newbie

      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

        • 1. Re: Group by date from timestapm ejbql
          Danila Zharenkov Newbie

          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