Query to select data from database between 2 timestamps, Exception: time_bucket function does not exists?

Question

I've been trying to write a query in Java to select data from postgres timescale database between 2 timestamps, but I keep getting Exception says that time_bucket function does not exists

I cannot change the timestamp column in database to Date since I dont have the ownership, and I tried to copy paste the query to sql editor, it works perfectly fine!!

this is the query:

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

this is how i set the parameters:

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

this is the exception i get:

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.

Even if i take out the interval keyword in the query, it still does not recognize the time_bucket function.

Thank you in advance for your time and knowledge!

Which version of timescaledb are you using? Are you sure that you are allowed to access the function? Also, are you by chance using a different schema or is the extension installed at a different schema? — Ancoron, Apr 08 '19 at 18:35
I can insert into this table, so its not about my jdbc configuration and schema, and I copy pasted the query to sql edior, it works just fine... — Bronfman, Apr 08 '19 at 18:57
Can you add the output of the following query executed via JDBC and the correct user? `SELECT n.nspname, p.proname, a.rolname, pg_catalog.pg_get_function_arguments(p.oid), p.proacl FROM pg_catalog.pg_proc p LEFT JOIN pg_catalog.pg_namespace n ON (n.oid = p.pronamespace) LEFT JOIN pg_catalog.pg_authid a ON (a.oid = p.proowner) WHERE pg_catalog.pg_function_is_visible(p.oid) AND p.proname = 'time_bucket' ORDER BY 1, 2, 4;` — Ancoron, Apr 09 '19 at 07:00
Thank you for your help, I finally solved the problem, its because I have set my default schema in my jdbc configuration file, but time_bucket function is in different schema, so system somehow cannot find it----which is weird, but that's the cause. again, thank you for your answer and help! — Bronfman, Apr 10 '19 at 14:02

score 1 · Answer 1 · answered Mar 24 '22 at 16:13

Repeating the answer from the comments for easier retrieval:

If you have a default schema (aka search path) set it must include the schema that contains the TSDB functions.

On a live connection you can do this e.g. like this:

SET search_path TO demo, public;

Query to select data from database between 2 timestamps, Exception: time_bucket function does not exists?

1 Answers1