CODEWITHSUNDEEP
hibernatetimer
nijan gouda
nijan gouda

Reputation: 1

hibernate group by date column on time interval

I need to get record from table, for showing hourly based transaction last 24 hours from now.

I am using hibernate, I am not able group by hour. Exactly I need to get status for every every hour.

Example.

For 1 hour
status count 
S        1
F        2

same for next hour as on.

DrWalletId  CrWalletId  TransAmount Currency    TransTime               Status
29251       29620              50   INR         2017-03-30 18:34:41.397  N
29251       29620              50   INR         2017-03-30 18:39:23.633  N
29251       29620              50   INR         2017-03-30 18:40:52.027  N
29251       29620              50   INR         2017-03-30 18:44:02.353  N
29251       29620              50   INR         2017-03-30 18:48:43.087  N
29251       29620              50   INR         2017-03-30 18:52:16.460  S
29620       29251              50   INR         2017-03-30 19:14:56.937  S
29620       29251              50   INR         2017-03-30 19:19:52.687  N
29620       29251              50   INR         2017-03-30 19:22:34.270  N
29620       29251              50   INR         2017-03-30 19:24:36.310  N
29620       29251              50   INR         2017-03-30 19:31:33.430  N
29620       29251              50   INR         2017-03-30 19:53:11.517  N
29620       29251              50   INR         2017-03-30 19:54:25.627  F
29620       29251              50   INR         2017-03-30 20:09:41.107  F
29620       29251              50   INR         2017-03-30 20:21:12.873  N
29620       29251              50   INR         2017-03-30 20:25:17.397  A

Please suggest how to do it in hibernate

Upvotes: 0

Views: 398

Answers (1)

ansh
ansh

Reputation: 696

You can use EXTRACT()

I have created a query for your understanding, I am assuming that you are using JPA as well. If not, feel free to modify it to suit your needs.

@Query("SELECT new com.example.HourlyTransactionStatus(CAST(EXTRACT(HOUR FROM (T.transTime - :referenceDate)) AS long), T.status, COUNT(T.status)) " +
       "FROM Transactions T " +
       "GROUP BY EXTRACT(HOUR FROM (T.transTime - :referenceDate)), T.status")
List<HourlyTransactionStatus> getHourlyTransactionStatus(@Param("referenceDate") Date referenceDate);

And I have created a model HourlyTransactionStatus

@AllArgsConstructor
class HourlyTransactionStatus {
    private Long hour; // hours passed from referenceDate
    private String status;
    private Long count;
}

Same Query on DB

select extract(hour from T.trans_time - '2017-03-30 00:00:00'), T.status, count(T.status) 
from test_so T
group by extract(hour from T.trans_time - '2017-03-30 00:00:00'), T.status

Results from DB

date_part status count
 18        S       1
 18        N       5
 19        N       5
 19        F       1
 19        S       1
 20        A       1
 20        F       1
 20        N       1

Upvotes: 1

Related Questions