1. ホーム
  2. Reporting Errors

ネストされた例外は org.hibernate.exception.SQLGrammarException: ResultSet を抽出できませんでした。

2022-02-13 14:42:53

エラーが報告されました。

2018-08-02 10:14:55.070 WARN 1744 --- [nio-8080-exec-3] o.h.engine.jdbc.spi.SqlExceptionHelper : SQL Error: 0, SQLState: 42883
2018-08-02 10:14:55.070 ERROR 1744 --- [nio-8080-exec-3] o.h.engine.jdbc.spi.SqlExceptionHelper : ERROR: function time_bucket(character varying, timestamp with time zone) does not exist
  Suggestion: No function matches the given name and argument types. You might need to add explicit type casts.
  Location: 10
2018-08-02 10:14:55.220 ERROR 1744 --- [nio-8080-exec-3] o.a.c.c.C. [. [. [/]. [dispatcherServlet] : Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed; nested exception is org.springframework.dao.InvalidDataAccessResourceUsageException: could not extract ResultSet; SQL [n/a]; nested exception is org. hibernate.exception.SQLGrammarException: could not extract ResultSet] with root cause

PSQLException: ERROR: function time_bucket(character varying, timestamp with time zone) does not exist
  Suggestion: No function matches the given name and argument types. You might need to add explicit type casts.
  Location: 10

エラーの理由

 @Query(value = "SELECT time_bucket(?1, time) AS bucket, avg(value) AS avg_value FROM metrics WHERE tag=?2 and time between ?3 AND ?4 GROUP BY bucket ORDER BY bucket DESC" ,nativeQuery = true)
   List selectTimeBucket(String timeBucket,String tag ,Timestamp startTime , Timestamp endTime);
@RequestMapping(value = "/selectTimeBucket" )
public List selectTimeBucket(@RequestParam("timeBucket") String timeBucket, @RequestParam("tag") String tag ,
                       @RequestParam("startTime") Timestamp startTime ,@RequestParam("endTime") Timestamp endTime){
    return metricsRepository.selectTimeBucket(timeBucket,tag,startTime,endTime);
}

 @Query(value = "SELECT time_bucket('1 hour', time) AS bucket, avg(value) AS avg_value FROM metrics WHERE tag=?1 and time between ?2 AND ?3 GROUP BY bucket ORDER BY bucket DESC" ,nativeQuery = true)
   List selectTimeBucket(String tag ,Timestamp startTime , Timestamp endTime);

アドレスの出力です。

http://127.0.0.1:8080/selectTimeBucket?timeBucket=1%20hour&tag=AAA&startTime=2018-7-01%2011:00:00&endTime=2018-7-26% 2011:00:00

に変更した場合。

 @Query(value = "SELECT time_bucket('1 hour', time) AS bucket, avg(value) AS avg_value FROM metrics WHERE tag=?1 and time between ?2 AND ?3 GROUP BY bucket ORDER BY bucket DESC" ,nativeQuery = true)
   List selectTimeBucket(String tag ,Timestamp startTime , Timestamp endTime);

そして、その結果を実行することができます!!!

公式サイト https://docs.timescale.com/v0.10/using-timescaledb/reading-data#select