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

小鱼儿 2022-05-18 09:47 314阅读 0赞

## 报的错误： ##

![70][]

![70 1][]

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][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][https_docs.timescale.com_v0.10_using-timescaledb_reading-data_select]

[70]: /images/20220518/e2b02cbb997a4a6f8e69e29d121d362f.png
[70 1]: /images/20220518/ad2c6382f8744fcf93a6dcca9c5cc3c0.png
[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]: 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
[https_docs.timescale.com_v0.10_using-timescaledb_reading-data_select]: https://docs.timescale.com/v0.10/using-timescaledb/reading-data#select

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

发表评论取消回复

还没有评论，来说两句吧...

相关阅读

相关 Could not load properties； nested exception is java.io.FileNotFoundException: Could not open Servlet

相关 Connection reset和could not extract ResultSet

相关 “could not execute statement； SQL [n/a]； nested exception is org.hibernate.exception.SQLGrammarExce

相关 Could not open JDBC Connection for transaction； nested exception is : Communications link failure

相关 IDEA Could not get JDBC Connection； nested exception is com.mysql.jdbc.exceptions

相关 could not extract ResultSet

相关 nested exception is org.hibernate.exception.SQLGrammarException: could not execute query

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

相关 Could not get JDBC Connection; nested exception is java.sql.SQLException: Connections could not

相关 Caused by: org.hibernate.exception.SQLGrammarException: could not extract ResultSet