Function time_bucket(unknown, timestamp without time zone) does not exist - when running in kotlin application

TimescaleDB and PostgreSQL
1

I am using timescale to store market tick data.
My timescaleDb is running in an ec2 instance

timescale extension is properly installed and the hypertable has been created.

I have this query to get aggregated ohlcv data.

SELECT extract(epoch from time_bucket('1 min', to_timestamp(time/1000.0) AT TIME ZONE 'IST')) * 1000 AS bucket,
  first(open, time) as open,
  max(high) as high,
  min(low) as low,
  last(close, time) as close,
  sum(volume) as volume
FROM ltpht5
WHERE time >= 1682399897000 AND time < 1682486297000 and instrument = 'AARTI'
GROUP BY bucket
ORDER BY bucket ASC;

When I run this query in dbeaver (postgres client) or cli, it runs fine and I get a response.

But when I try to execute the same query through my kotlin based application, I get the following error -
“org.postgresql.util.PSQLException: ERROR: function time_bucket(unknown, 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.”

Here is my code in kotlin -

    @Query(
        "SELECT extract(epoch from time_bucket('1 min', to_timestamp(time/1000.0) AT TIME ZONE 'IST')) * 1000 AS bucket,\n" +
                "  first(open, time) as open,\n" +
                "  max(high) as high,\n" +
                "  min(low) as low,\n" +
                "  last(close, time) as close,\n" +
                "  sum(volume) as volume\n" +
                "FROM ltpht5\n" +
                "WHERE time >= 1682399897000 AND time < 1682486297000 and instrument = 'AARTI'\n" +
                "GROUP BY bucket\n" +
                "ORDER BY bucket ASC",
        nativeQuery = true
    )
    fun get1minbucket(): List<Any>?

and here is my application.yml where I have established the connection -

  datasource:
    driver-class-name: org.postgresql.Driver
    url: jdbc:postgresql://**.***.***.***:****/postgres?currentSchema=timeseries
    username: ********
    password: ********

And I have made sure that the connection has been established, I am able to make simple write and read queries from the hypertable.
It’s only when I use any timescale functions, I get the error.

I am not able to understand why this is happening. If timescaledb is installed at my ec2 endpoint (as verified through dbeaver client), why when accessing it’s features through application layer gives errors?

Any help would be highly appreciated.

Thanks.

2

Hi @Satyam_Saurabh, thanks for the detailed explanation.

Let’s see if we discover the case.

First thing: please, anonymize and remove the IP address from your db server :nerd_face:

I understand that you’re using kotlin and connecting to the database.

Let’s run a minimal kotlin example:

select time_bucket('1 min', to_timestamp(66938214855/1000.0) AT TIME ZONE 'IST');
┌─────────────────────┐
│     time_bucket     │
├─────────────────────┤
│ 1972-02-14 19:56:00 │
└─────────────────────┘

Also, take a look in the cast options:

 select pg_typeof('1 min'::interval);
┌───────────┐
│ pg_typeof │
├───────────┤
│ interval  │
└───────────┘
(1 row)

tsdb=> select pg_typeof('1 min');
┌───────────┐
│ pg_typeof │
├───────────┤
│ unknown   │
└───────────┘
(1 row)

Maybe your kotlin code will need to force the ::interval for the first argument.

Generally the casts are good at it but maybe something is different for the frameworks!

Let us know if it works!

3

HI @jonatasdp ,
Thanks for your reply.

Even with casting it doesn’t work.
Here’s the code -

    @Query(
        "SELECT " +
                "extract(epoch from time_bucket(cast(:bucket as interval), to_timestamp(time/1000.0) AT TIME ZONE 'IST')) * 1000 AS bucket, " +
                "first(open, time) as open, " +
                "max(high) as high, " +
                "min(low) as low, " +
                "last(close, time) as close, " +
                "sum(volume) as volume " +
                "FROM ltpht1 " +
                "WHERE time >= :startTime AND time < :endTime and instrument = :instrument " +
                "GROUP BY bucket, instrument " +
                "ORDER BY bucket ASC",
        nativeQuery = true
    )
    fun getMinBucket(startTime: Long, endTime: Long, bucket: String, instrument: String) : List<Any>?

Even this doesn’t work.

But here is the thing,
When I changed my schema to public, it started working -

url: jdbc:postgresql://**.***.***.***:****/postgres?currentSchema=public

But with any other schema manually created in the db, it is not working.

Any idea why this is happening, is there some configuration in timescale due to which this might be happening ?

4

Hi @Satyam_Saurabh , it’s like an extension that is adding a bunch of functions but anything changes in your section.

I’d try to confirm what are the defaults of your connections, specially verify the search_path and try to investigate the pg_typeof of each element you’re using in the parameters.

As an extra step, I’d also try to find the function in the pg_catalog:

SELECT n.nspname AS schema,
       p.proname AS function_name,
       pg_get_function_identity_arguments(p.oid) AS function_arguments,
       d.description
FROM pg_proc p
JOIN pg_namespace n ON p.pronamespace = n.oid
LEFT JOIN pg_description d ON p.oid = d.objoid
WHERE  p.proname = 'time_bucket';

The results of this query can help you to answer it.

Try some different casting, maybe you’re connecting to a different machine.

5

hi @Satyam_Saurabh , any news? did it worked for you? please share anything relevant for future kotlin/timescale users :hugs:

6

Hi @jonatasdp ,

Unfortunately, I have no new update as of now.
I tried your query and everything looks fine to me.

For now, I am creating the hyper-tables in public schema only. And I am proceeding ahead with this.

My suggestion to people who are also not able to access timescale specific functions using kotlin, try changing your schema to public.

Also, I have noticed some ingestion and read query aspects for timescale while doing a poc of timescale vs influx vs druid performance. I will start a new thread for that.

Thanks.

1 Like
7

Just had a similar problem in Grafana which may apply here, realise this is an old thread but could help someone as it’s the first that popped up in my search!

I have a non public schema where the data is held and Grafana is set to use that schema as the default when it connects so that it can pick up the tables and columns etc. I had to specify the public schema when using the time bucket function, so:

public.time_bucket(‘your_interval’,“your_time_column”)

fixed the problem. This means for anyone using Grafana and connecting with an alternative schema may also struggle to use the $__timegroupalias macro.