Timescaledb JPA time_bucket function-CodePudding

I want to use timescaledb in my spring boot application like:

@Query("""select time_bucket(':bucket' minutes) as bucket, avg(data) as value
from h_table ht
where
   ...
group by bucket""", nativeQuery=true)
fun getByDeviceAndCode(@Param("bucket") bucket:Int,
                       ...) : List<ResultData>

but I've got an error:

 ERROR: invalid input syntax for type interval: ":bucket minutes"

how can I pass :bucket parameter to sql?

thx, Zamek

CodePudding user response：

I think you have a mistake in the ending quote, and the time column seems missing. It has to be as follows (where time_col should be replaced with the name of your time column):

select time_bucket(':bucket minutes', time_col) as bucket, avg(data) as value ...

https://docs.timescale.com/api/latest/hyperfunctions/time_bucket/#sample-usage

If you still get the error, try to cast it explicitly to a postgres interval with something like this:

select time_bucket(cast(':bucket minutes' as interval), time_col) as bucket, avg(data) as value ...

To avoid problems, I would also change the type of bucket to a String and include the time unit (minutes) inside it. This way it will be treated as a whole string and avoid issues with single quotes:

select time_bucket(cast(:bucket as interval), time_col) as bucket, avg(data) as value ...