nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet

報的錯誤：

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
  建議：No function matches the given name and argument types. You might need to add explicit type casts.
  位置：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

org.postgresql.util.PSQLException: ERROR: function time_bucket(character varying, timestamp with time zone) does not exist
  建議：No function matches the given name and argument types. You might need to add explicit type casts.
  位置：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);
}
           

位址輸出：

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