0

I have the following MySQL query:

select p.name, sum(timestampdiff(MINUTE, r.start_time, r.end_time)) 
from ride r inner join person p on r.driver_id=p.id 
group by p.id

I want to write this in a JPA Query. The code below throws a NullPointerException, it does not work:

@RestResource
public interface SomeRepository extends CrudRepository<Ride, Long> {

@Query("SELECT new com.sample.dto.MyDTO(d.name, SUM(FUNCTION('TIMESTAMPDIFF', 'MINUTE', r.startTime, r.endTime))) " +
      "FROM Ride r JOIN r.driver d WHERE r.startTime BETWEEN ?1 AND ?2 " +
      "GROUP BY d.id" )
  List<MyDTO> findSomething(@Param("startTime") LocalDateTime startTime,
                                   @Param("endTime") LocalDateTime endTime);

}

If I use DATEDIFF function, it works. 

FUNCTION('DATEDIFF', r.startTime, r.endTime)

But I need the exact difference in minutes. TIMESTAMPDIFF satisfies my needs. After failure of finding how to do this with JPA, I returned to native query but this is not good. Any alternatives?

I use the latest spring-data-jpa (2.1.3), hibernate-core:5.3.7

public class Ride {
  @Column(name = "start_time")
  private LocalDateTime startTime;

  @Column(name = "end_time")
  private LocalDateTime endTime;

 }

The goal is to find total difference between startTime and endTime (in minutes). Is there a way to do this with standard (portable) JPA?

Bhdr
  • 169
  • 3
  • 13
  • why do you think that a native query is not good? IMHO this is a perfect fit for what you try to achieve. Native queries are ok in many situations. – Simon Martinelli Dec 02 '18 at 10:36
  • Yes, here the JPA was like an unnecessary layer which caused more trouble than help to me. I'll use the native query instead of wrestling with JPQL. – Bhdr Dec 02 '18 at 13:27
  • @Query(value="...", nativeQuery = true) Object[][] findSomething(..) this Object array and mapping it is not nice. (The result is not an entity, just an aggregate function result.) – Bhdr Dec 02 '18 at 13:30
  • @Bhdr You may give a shot to my solution if you really want to use JPA. – Kunal Puri Dec 03 '18 at 00:42
  • Thanks Kunal. I tried, it didn't work but I'll try again when I have time. – Bhdr Dec 03 '18 at 07:32
  • @Bhdr Can you please tell me the error that you received? I ran a simple query using the same approach on my end and it was working. – Kunal Puri Dec 03 '18 at 14:25
  • @KunalPuri yes it works, but I also need to limit the result at the DB level. It seems JPA does not support this too. "ORDER BY blahblah LIMIT ?3" – Bhdr Dec 05 '18 at 14:30

2 Answers2

2

Actually, there is no TIMESTAMPDIFF in JPQL.

Reference: timestampdiff equivalent in JPQL (without using criteria)

So, What you can do is that you can use TIME_TO_SEC(TIMEDIFF(r.startTime, r.endTime)) / 60 instead of using FUNCTION('TIMESTAMPDIFF', 'MINUTE', r.startTime, r.endTime)

Kunal Puri
  • 3,419
  • 1
  • 10
  • 22
  • this works, but if you want to limit the results at DB level, JPA has not support it seems. For something like "ORDER BY blah LIMIT ?3" , we need native query again. Any ideas? – Bhdr Dec 05 '18 at 14:33
  • @Bhdr There is an alternative to LIMIT clause. Explore the `Pageable` Interface. Refer to https://stackoverflow.com/questions/9314078/setmaxresults-for-spring-data-jpa-annotation/28444140. Since constructors of `PageRequest` are deprecated, Refer to https://stackoverflow.com/questions/44848653/pagerequest-constructors-have-been-deprecated – Kunal Puri Dec 06 '18 at 01:58
0

There is no registerFunction('TIMESTAMPDIFF') in org.hibernate.dialect.MySQLDialect.

But registerFunction('DATEDIFF') exists.

So you cant use TIMESTAMPDIFF in jpa.

You can add custom function or use custom Dialect.

But I suggest using unix_timestamp() instead.

(function('unix_timestamp', startTime)-function('unix_timestamp', endTime))/60
cavenda
  • 1