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.