java - 查询从 2 个时间戳之间的数据库中选择数据，异常 : time_bucket function does not exists?

Question

我一直在尝试用 Java 编写查询以从 2 个时间戳之间的 postgres 时间刻度数据库中选择数据，但我一直收到异常说 time_bucket 函数不存在

我无法将数据库中的时间戳列更改为日期，因为我没有所有权，我尝试将查询复制粘贴到 sql 编辑器，它工作得很好!!

这是查询:

private final String SELECT_CANDLESTICK_BY_REQUEST = "SELECT " + 
        "   time_bucket(  interval '1 minute' , period_start_ts) AS periodts,  " + 
        "   count(*),  " + 
        "   first(metrics->>'askOpen',period_start_ts) as askOpen,  " + 
        "   max(metrics->>'askHigh') as askHigh,  " + 
        "   min(metrics->>'askLow') as askLow,  " + 
        "   last(metrics->>'askClose',period_start_ts) as askClose, " + 
        "   first(metrics->>'bidOpen',period_start_ts) as bidOpen,  " + 
        "   max(metrics->>'bidHigh') as bidHigh,  " + 
        "   min(metrics->>'bidLow') as bidLow,  " + 
        "   last(metrics->>'bidClose',period_start_ts) as bidClose " + 
        "  FROM candlestick_1_sec_fact " + 
        "  where period_start_ts between ? and ? " + 
        "  and symbol_cd = ? and liquidity_source_nm =  ? " + 
        "  GROUP BY periodts " + 
        "  ORDER BY periodts" ;

这是我设置参数的方式:

PreparedStatement select = connection.prepareStatement(SELECT_CANDLESTICK_BY_REQUEST);


    select.setTimestamp(1, new Timestamp(startTime));
    select.setTimestamp(2, new Timestamp(endTime));
    select.setString(3, symbol);
    select.setString(4, source); 

这是我得到的异常:

org.postgresql.util.PSQLException: ERROR: function time_bucket(interval, timestamp without time zone) does not exist . Hint: No function matches the given name and argument types. You might need to add explicit type casts.

即使我在查询中去掉了interval关键字，它仍然无法识别time_bucket函数。

提前感谢您的时间和知识!

Answer 1

重复评论中的答案以便于检索:

如果您设置了默认架构(又名搜索路径)，则它必须包含包含 TSDB 函数的架构。

在实时连接上，您可以执行此操作，例如像这样:

SET search_path TO demo, public;