CODEWITHSUNDEEP
springpostgresqlhibernatecriteriahibernate-criteria
Padi
Padi

Reputation: 801

Hibernate Criteria - get sum(hours between two dates)

I have the following table:

     start_date      |      end_date       |       age
---------------------+---------------------+-----------------
 2020-11-30 09:00:00 | 2020-12-05 23:00:00 | 5 days 14:00:00
 2020-11-30 09:00:00 | 2020-11-30 10:00:00 | 01:00:00
 2020-11-30 09:00:00 | 2020-12-03 19:00:00 | 3 days 10:00:00
 2020-11-30 09:00:00 | 2020-12-03 19:00:00 | 3 days 10:00:00
 2020-11-30 09:00:00 | 2020-12-03 19:00:00 | 3 days 10:00:00
 2020-12-01 09:00:00 | 2020-12-03 19:00:00 | 2 days 10:00:00
 2020-12-03 09:00:00 | 2020-12-03 19:00:00 | 10:00:00
 2020-12-04 09:00:00 | 2020-12-04 19:00:00 | 10:00:00

from the following query:

select start_date, end_date, age(end_date, start_date) from event;

how can I get the sum(number of hours/day) between start and end date for each day in java Hibernate Criteria?

until now:

    public List<StatsDto> lastXDaysEvents(LocalDateTime xDaysBefore, LocalDateTime xDaysAfter) {
        Session session = openSession();

        Criteria cr = session.createCriteria(Event.class);
        
        cr.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY);

        cr.add(Restrictions.ge("startDate", xDaysBefore)); 
        cr.add(Restrictions.lt("endDate", xDaysAfter));
//      cr.createAlias("date_part('hour', age(end_date, start_date))", "sumDate");
//      cr.add(Expression.sql("datediff('Hour', start_date, end_date)"));
        
        ProjectionList projectionList = Projections.projectionList(); 
        projectionList.add(Projections.alias(Projections.sqlGroupProjection("date(start_date) as sDate", "sDate", new String[] { "sDate" }, new Type[] { StandardBasicTypes.DATE }), "x"));
        projectionList.add(Projections.alias(Projections.rowCount(), "y"));
//    projectionList.add(Projections.alias(Projections.sum("sum(sumDate)"), "y"));
        
        cr.addOrder(Order.asc("x"));
        cr.setProjection(projectionList);
        cr.setResultTransformer(Transformers.aliasToBean(StatsDto.class));
                
        List<StatsDto> result = cr.list();

        return result;
    }

I get the rowCount() 'y' projection which gives the number of events grouped by date but not all events have exactly one hour. How can I get in the 'y' projection the sum(hour_diff(start_date, end_date))?

I think I need to:

but I don't know how.

Upvotes: 0

Views: 700

Answers (1)

Belayer
Belayer

Reputation: 14934

The AGE function returns the time between 2 timestamps as an INTERVAL. The DATE_PART function extracts a specific portion of a timestamp or interval. See documentation 9.9. Date/Time Functions and Operators. If you want the total duration in terms of specific units (in this case hours) then you must apply the part conversions to calculate the value.

with test_dates (start_date,end_date) as 
     (values ('2020-11-30 09:00:00'::timestamp, '2020-12-05 23:00:00'::timestamp)  
           , ('2020-11-30 09:00:00'::timestamp, '2020-11-30 10:00:00'::timestamp)  
           , ('2020-11-30 09:00:00'::timestamp, '2020-12-03 19:00:00'::timestamp)  
           , ('2020-11-30 09:00:00'::timestamp, '2020-12-03 19:00:00'::timestamp)  
           , ('2020-11-30 09:00:00'::timestamp, '2020-12-03 19:00:00'::timestamp)  
           , ('2020-12-01 09:00:00'::timestamp, '2020-12-03 19:00:00'::timestamp)  
           , ('2020-12-03 09:00:00'::timestamp, '2020-12-03 19:00:00'::timestamp)  
           , ('2020-12-04 09:00:00'::timestamp, '2020-12-04 19:00:00'::timestamp) 
     ) 
select start_date
     , end_date
     , 24*date_part('day',diff)+date_part('hour',diff)  num_hours
  from ( select start_date,end_date,age(end_date,start_date) diff
           from test_dates
       ) d

You actually do not need the AGE function as direct subtraction of timestamps produces the same interval. So (see fiddle for both)

with test_dates (start_date,end_date) as 
     (values ('2020-11-30 09:00:00'::timestamp, '2020-12-05 23:00:00'::timestamp)  
           , ('2020-11-30 09:00:00'::timestamp, '2020-11-30 10:00:00'::timestamp)  
           , ('2020-11-30 09:00:00'::timestamp, '2020-12-03 19:00:00'::timestamp)  
           , ('2020-11-30 09:00:00'::timestamp, '2020-12-03 19:00:00'::timestamp)  
           , ('2020-11-30 09:00:00'::timestamp, '2020-12-03 19:00:00'::timestamp)  
           , ('2020-12-01 09:00:00'::timestamp, '2020-12-03 19:00:00'::timestamp)  
           , ('2020-12-03 09:00:00'::timestamp, '2020-12-03 19:00:00'::timestamp)  
           , ('2020-12-04 09:00:00'::timestamp, '2020-12-04 19:00:00'::timestamp) 
     ) 
select start_date
     , end_date
     , 24*date_part('day',end_date-start_date) + date_part('hours', end_date-start_date) num_hours
  from work_dates;

Finally to get daily total hours just sum the result:

 with test_dates (start_date,end_date) as 
     (values ('2020-11-30 09:00:00'::timestamp, '2020-12-05 23:00:00'::timestamp)  
           , ('2020-11-30 09:00:00'::timestamp, '2020-11-30 10:00:00'::timestamp)  
           , ('2020-11-30 09:00:00'::timestamp, '2020-12-03 19:00:00'::timestamp)  
           , ('2020-11-30 09:00:00'::timestamp, '2020-12-03 19:00:00'::timestamp)  
           , ('2020-11-30 09:00:00'::timestamp, '2020-12-03 19:00:00'::timestamp)  
           , ('2020-12-01 09:00:00'::timestamp, '2020-12-03 19:00:00'::timestamp)  
           , ('2020-12-03 09:00:00'::timestamp, '2020-12-03 19:00:00'::timestamp)  
           , ('2020-12-04 09:00:00'::timestamp, '2020-12-04 19:00:00'::timestamp) 
     ) 
select start_date::date for_date
     , sum( (24*date_part('day',diff)+date_part('hour',diff)))  daily_num_hours
  from ( select start_date,end_date,age(end_date,start_date) diff
           from test_dates
       ) d
group by start_date::date
order by start_date::date;

Upvotes: 1

Related Questions