How to parameterise Postgresql Interval for TimescaleDB `time_bucket` function with JPQL, Spring Data Repositories and Hibernate

Question

I am using Spring Data JPA (with Hibernate underneath, JPA 2.1) with TimescaleDB extension on PostgreSQL 13, and wish to use the time_bucket function. This takes the bucket_width which is an INTERVAL and time which is the TIMESTAMP column of the data.

I want to put this in a Spring Data Repository and want to use a JPQL @Query to extract the data into a projection that represents the aggregate counts, averages etc. for the returned time buckets. I don't want to use a native query, because I want to join with some other tables, and populate their entities automatically.

I registered the time_bucket function to the PostgisPG95Dialect I am extending, like this:

public class CustomPostgresqlDialect extends PostgisPG95Dialect {

    public CustomPostgresqlDialect() {
        super();
        this.registerFunction("time_bucket", new StandardSQLFunction("time_bucket", new OffsetDateTimeType()));
    }
}

If the bucket_width is hardcoded, all this works fine. But I want the bucket_width to be a parameter of the query method.

The following works fine:

 @Query("select sys as system, "
                  + "function('time_bucket', '10 mins', vt.ts) as startTime, "
                  + "count(vt) as total, avg(vt.speed) as avgSpeed "
                  + "from Data vt "
                  + "JOIN vt.system sys "
                  + "where sys.sysId = :sysId and "
                  + "function('time_bucket', '10 mins', vt.ts)  between :from and :to "
                  + "group by system, startTime "
                  + "order by startTime")
  List<SummaryAggregate> getSummaryData(
          @Param("sysId") String sysId,
          @Param("from") OffsetDateTime from,
          @Param("to") OffsetDateTime to);

But when I try to parameterise the interval I can't get it to work. I tried passing the interval as a string, since that is how it is being written in the hardcoded version:

 @Query("select sys as system, "
                  + "function('time_bucket', :grouping, vt.ts) as startTime, "
                  + "count(vt) as total, avg(vt.speed) as avgSpeed "
                  + "from Data vt "
                  + "JOIN vt.system sys "
                  + "where sys.sysId = :sysId and "
                  + "function('time_bucket', :grouping, vt.ts)  between :from and :to "
                  + "group by system, startTime "
                  + "order by startTime")
  List<SummaryAggregate> getSummaryData(
          @Param("sysId") String sysId,
          @Param("from") OffsetDateTime from,
          @Param("to") OffsetDateTime to,
          @Param("grouping") String grouping);

where grouping is passed a value like 10 mins.

But for this I get this error:

SQL Error: 0, SQLState: 42883
ERROR: function time_bucket(character varying, timestamp with time zone) does not exist
  Hint: No function matches the given name and argument types. You might need to add explicit type casts.
  Position: 61

I then tried to change it to a Duration, since Hibernate translates Duration to PostgreSQL Interval types

 @Query("select sys as system, "
                  + "function('time_bucket', :grouping, vt.ts) as startTime, "
                  + "count(vt) as total, avg(vt.speed) as avgSpeed "
                  + "from Data vt "
                  + "JOIN vt.system sys "
                  + "where sys.sysId = :sysId and "
                  + "function('time_bucket', :grouping, vt.ts)  between :from and :to "
                  + "group by system, startTime "
                  + "order by startTime")
  List<SummaryAggregate> getSummaryData(
          @Param("sysId") String sysId,
          @Param("from") OffsetDateTime from,
          @Param("to") OffsetDateTime to,
          @Param("grouping") Duration grouping);

But I still got the same error, this time it is thinking that the Duration is a bigint not an Interval.

SQL Error: 0, SQLState: 42883
ERROR: function time_bucket(bigint, timestamp with time zone) does not exist
  Hint: No function matches the given name and argument types. You might need to add explicit type casts.
  Position: 61

Is there a way to parameterise an Interval using JPQL?

Answer 1

There is a way, but you will have to register a custom function for this purpose because you can't cast to an arbitrary SQL type.

public class CastInterval implements SQLFunction {

    @Override
    public boolean hasArguments() {
        return true;
    }

    @Override
    public boolean hasParenthesesIfNoArguments() {
        return true;
    }

    @Override
    public Type getReturnType(Type firstArgumentType, Mapping mapping) throws QueryException {
        return firstArgumentType;
    }

    @Override
    public String render(Type firstArgumentType, List args, SessionFactoryImplementor factory) throws QueryException {
        return "cast(" + args.get(0) + " as interval)";
    }
}

You will have to register the function within the Dialect.

So if the Dialect is being extended as indicated, this would be done with something like:

 this.registerFunction("castInterval", new CastInterval());

Then you can use it like this: function('time_bucket', castInterval(:grouping), vt.ts)