I have a timescaleDB but I cannot seem to be able to use the time_bucket method with less than one day intervals.
The code attempting to access it is running inside of a fast API and is as follows :
async def get_candlestick_data(self, db: AsyncSession, market_name: str, resource_name: str, interval: str):
sql = f"""
SELECT
time_bucket('{interval}', timestamp) as period,
FIRST(price, timestamp) as open,
MAX(price) as high,
MIN(price) as low,
LAST(price, timestamp) as close,
SUM(quantity) as volume
FROM
resource_market_value
WHERE
market_name = :market_name AND resource_name = :resource_name
GROUP BY
period
ORDER BY
period;
"""
result = await db.execute(text(sql), {'market_name': market_name, 'resource_name': resource_name})
return result.fetchall()
Here is the model I am using :
from sqlalchemy import DateTime
from sqlalchemy import Column, Integer, String, BigInteger
from common_modules.database.base import Base
class ResourceMarketValue(Base):
__tablename__ = 'resource_market_value'
id = Column(BigInteger, primary_key=True, autoincrement=True)
market_name = Column(String, nullable=False)
resource_name = Column(String, nullable=False)
timestamp = Column(DateTime, nullable=False)
price = Column(Integer, nullable=False)
quantity = Column(Integer, nullable=False)
And here is the only configuration / edit I am doing to the table :
SELECT create_hypertable('resource_market_value', 'timestamp');
The issue is that the TimescaleDB is not accepting intervals of less than one day. Here are the logs I am getting :
2024-09-12 14:59:54.455 UTC [50740] ERROR: interval must not have sub-day precision
2024-09-12 14:59:54.455 UTC [50740] STATEMENT:
SELECT
time_bucket('15 minutes', timestamp) as period,
FIRST(price, timestamp) as open,
MAX(price) as high,
MIN(price) as low,
LAST(price, timestamp) as close,
SUM(quantity) as volume
FROM
resource_market_value
WHERE
market_name = $1 AND resource_name = $2
GROUP BY
period
ORDER BY
period;
2024-09-12 14:59:54.456 UTC [50741] ERROR: could not map dynamic shared memory segment
2024-09-12 14:59:54.456 UTC [50742] ERROR: could not map dynamic shared memory segment
2024-09-12 14:59:54.458 UTC [1] LOG: background worker "parallel worker" (PID 50741) exited with exit code 1
2024-09-12 14:59:54.459 UTC [1] LOG: background worker "parallel worker" (PID 50742) exited with exit code 1
What makes me very confused is that I am not seeing any for of configuration being mentioned in the documentation of the time_bucket as seen here : https://docs.timescale.com/use-timescale/latest/time-buckets/use-time-buckets/
If anything, it seems like I should be able to do so.
The intent with my code on the long run is to be able to have market values being displayed in a candlestick chart and I'll need to be able to have up to 15 minutes per candle as minimum and 1 day as maximum.
Currently the only data in the database is test data that I inserted. There is one entry per minute, no more.
Ideally, if possible, I would like to automate the configuration and not navigate to a web page if changing configuration is needed.
Edit : the version of timescaleDB I am using is the 2.16.1 as seen here :
psql (14.12)
Type "help" for help.
timescaledb=# SELECT default_version, installed_version FROM pg_available_extensions WHERE name = 'timescaledb';
default_version | installed_version
-----------------+-------------------
2.16.1 | 2.16.1
(1 row)
timescaledb=# SELECT version();
version
---------------------------------------------------------------------------------------------------------------
PostgreSQL 14.12 on x86_64-pc-linux-musl, compiled by gcc (Alpine 13.2.1_git20240309) 13.2.1 20240309, 64-bit
(1 row)
I will also add that it is as unconfigured as it can get. It has the password and username through environ values and that is it.
The database now works fine.
What seems to have happened is that the table what not a hypertable. I had code in order to make sure that it was one but there is a catch : by default, a table cannot be converted to a hypertable if the table possesses some data.
It's quite frustrating that I did not see anything mentioned in the documentation of the time_bucket method about this and the error was not explicit about the topic.