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.
2
It seems you’re using version 1.7 or less of Timescaledb.
Please, update to latest versions to confirm that the error will disappear.
1
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.
4