Search code examples
mysqljpajpqlconverters

How to convert mysql query to JPQL query

I have working MySQL query:

SELECT date(timestamp), hour(timestamp), sum(numberDet), count(*) 
FROM human_det_counter 
GROUP BY hour( timestamp ) , day( timestamp ) 
ORDER BY date(timestamp)

I want select records, group by hour from each day and sum their number of detections.

I have tried this query:

SELECT date(h.timestamp), hour(h.timestamp), sum(h.numberDet), count(h) 
FROM HumanDetCounter h 
GROUP BY hour( h.timestamp ) , day(h.timestamp ) 
ORDER BY date(h.timestamp)

,but it's not working. I readed that jpa don't support Hour() function and that I should use TO_CHART function, but I don't know how.

Solution

  • Okey, I figured it out. There's my query if someone needs that:

    createQuery("SELECT cast(Date(h.timestamp) as string), substring(h.timestamp, 12,2) , sum(h.numberDet), count(h) FROM HumanDetCounter h GROUP by substring(h.timestamp, 12,2), substring(h.timestamp, 9,2) ORDER BY h.timestamp")

    It's probably not the best solution, but it's work :)