Как параметризовать интервал Postgresql для функции `time_bucket` TimescaleDB с помощью JPQL, Spring Data Repositories и Hibernate

Я использую Spring Data JPA (с Hibernate внизу, JPA 2.1) с расширением TimescaleDB на PostgreSQL 13 и хочу использовать time_bucket. Это берет bucket_width, который является INTERVAL, и time, который является столбцом TIMESTAMP данных.

Я хочу поместить это в репозиторий данных Spring и хочу использовать JPQL @Query для извлечения данных в проекцию, которая представляет совокупные подсчеты, средние значения и т. д. для возвращенных сегментов времени. Я не хочу использовать собственный запрос, потому что я хочу присоединиться к некоторым другим таблицам и автоматически заполнить их объекты.

Я зарегистрировал функцию time_bucket в PostgisPG95Dialect, которую я расширяю, вот так:

public class CustomPostgresqlDialect extends PostgisPG95Dialect {

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

Если bucket_width жестко запрограммировано, все это работает нормально. Но я хочу, чтобы bucket_width был параметром метода запроса.

Следующее работает нормально:

 @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);

Но когда я пытаюсь параметризовать интервал, я не могу заставить его работать. Я попытался передать интервал в виде строки, поскольку именно так он записывается в жестко запрограммированной версии:

 @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);

где grouping передается значение, подобное 10 mins.

Но для этого я получаю эту ошибку:

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

Затем я попытался изменить его на Duration, так как Hibernate переводит Duration в интервал PostgreSQL. типы

 @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);

Но я все еще получаю ту же ошибку, на этот раз я думаю, что Duration - это bigint, а не 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

Есть ли способ параметризовать Interval с помощью JPQL?


postgresql hibernate timescaledb jpql jpa-2.1
person jbx    schedule 13.05.2021    source источник

Ответы (1)


arrow_upward
1
arrow_downward

Есть способ, но вам придется зарегистрировать для этой цели пользовательскую функцию, потому что вы не можете привести к произвольному типу SQL.

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)";
    }
}

Вам нужно будет зарегистрировать функцию в диалекте.

Итак, если диалект расширяется, как указано, это можно сделать примерно так:

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

Затем вы можете использовать его так: function('time_bucket', castInterval(:grouping), vt.ts)

person Christian Beikov    schedule 14.05.2021
comment
Есть идеи, как я могу использовать это в JPQL function? Я попробовал "function('time_bucket', castInterval(:grouping), vt.ts), и это не сработало. (Это дало ошибку antlr.NoViableAltException: unexpected token: vt, поэтому я предполагаю, что это недопустимый синтаксис.) - person jbx; 14.05.2021
comment
Не уверен, откуда исходит исключение, но это допустимый синтаксис. - person Christian Beikov; 14.05.2021
comment
Вы правы... все работает... спасибо! - person jbx; 14.05.2021