0

I want to get data as mentioned follwing question.

Query to retrieve count per hour and zero if none

So according to that, I execute the following query in Postgresql so it is working fine.

select '00:00'::time + g.h * interval '1 hour',count(sv.id) as orders from generate_series(0, 23, 1) g(h) left join paymentvirtualization.summery_virtualizer sv on extract(hour from sv.last_updated) = g.h and date_trunc('day', sv.last_updated) = '2019-10-11' group by g.h order by g.h;

But my problem is I want this to execute as a spring data JPA native query like.

  @Query(
            value = "select '00:00'::time + g.h * interval '1 hour',count(sv.id) as orders from generate_series(0, 23, 1) g(h) left join paymentvirtualization.summery_virtualizer sv on extract(hour from sv.last_updated) = g.h and date_trunc('day', sv.last_updated) = '2019-10-11' group by g.h order by g.h",
            nativeQuery = true
    )
    List<Map<String, Integer>> getPaymentDistry();

If I execute it I am getting an error like

could not extract ResultSet; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet"

Can anyone help me here?

Thanks,

Dasun
  • 602
  • 1
  • 11
  • 34

1 Answers1

2

The obfuscation layer chokes on the :: because it uses : to introduce named parameter. Typically the solution is to use the standard compliant cast() operator instead.

select cast('00:00' as time) + g.h * interval '1 hour' ...

alternatively use the ANSI time syntax as suggested by Laurenz:

select time '00:00' + g.h * interval '1 hour' ...