TimescaleDB not accepting intervales less than one day

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 :

Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
<code> 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()
</code>
<code> 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() </code>
    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 :

Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
<code>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)
</code>
<code>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) </code>
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 :

Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
<code>SELECT create_hypertable('resource_market_value', 'timestamp');
</code>
<code>SELECT create_hypertable('resource_market_value', 'timestamp'); </code>
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 :

Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
<code>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
</code>
<code>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 </code>
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 :

Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
<code>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)
</code>
<code>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) </code>
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

Trang chủ Giới thiệu Sinh nhật bé trai Sinh nhật bé gái Tổ chức sự kiện Biểu diễn giải trí Dịch vụ khác Trang trí tiệc cưới Tổ chức khai trương Tư vấn dịch vụ Thư viện ảnh Tin tức - sự kiện Liên hệ Chú hề sinh nhật Trang trí YEAR END PARTY công ty Trang trí tất niên cuối năm Trang trí tất niên xu hướng mới nhất Trang trí sinh nhật bé trai Hải Đăng Trang trí sinh nhật bé Khánh Vân Trang trí sinh nhật Bích Ngân Trang trí sinh nhật bé Thanh Trang Thuê ông già Noel phát quà Biểu diễn xiếc khỉ Xiếc quay đĩa Dịch vụ tổ chức sự kiện 5 sao Thông tin về chúng tôi Dịch vụ sinh nhật bé trai Dịch vụ sinh nhật bé gái Sự kiện trọn gói Các tiết mục giải trí Dịch vụ bổ trợ Tiệc cưới sang trọng Dịch vụ khai trương Tư vấn tổ chức sự kiện Hình ảnh sự kiện Cập nhật tin tức Liên hệ ngay Thuê chú hề chuyên nghiệp Tiệc tất niên cho công ty Trang trí tiệc cuối năm Tiệc tất niên độc đáo Sinh nhật bé Hải Đăng Sinh nhật đáng yêu bé Khánh Vân Sinh nhật sang trọng Bích Ngân Tiệc sinh nhật bé Thanh Trang Dịch vụ ông già Noel Xiếc thú vui nhộn Biểu diễn xiếc quay đĩa Dịch vụ tổ chức tiệc uy tín Khám phá dịch vụ của chúng tôi Tiệc sinh nhật cho bé trai Trang trí tiệc cho bé gái Gói sự kiện chuyên nghiệp Chương trình giải trí hấp dẫn Dịch vụ hỗ trợ sự kiện Trang trí tiệc cưới đẹp Khởi đầu thành công với khai trương Chuyên gia tư vấn sự kiện Xem ảnh các sự kiện đẹp Tin mới về sự kiện Kết nối với đội ngũ chuyên gia Chú hề vui nhộn cho tiệc sinh nhật Ý tưởng tiệc cuối năm Tất niên độc đáo Trang trí tiệc hiện đại Tổ chức sinh nhật cho Hải Đăng Sinh nhật độc quyền Khánh Vân Phong cách tiệc Bích Ngân Trang trí tiệc bé Thanh Trang Thuê dịch vụ ông già Noel chuyên nghiệp Xem xiếc khỉ đặc sắc Xiếc quay đĩa thú vị
Trang chủ Giới thiệu Sinh nhật bé trai Sinh nhật bé gái Tổ chức sự kiện Biểu diễn giải trí Dịch vụ khác Trang trí tiệc cưới Tổ chức khai trương Tư vấn dịch vụ Thư viện ảnh Tin tức - sự kiện Liên hệ Chú hề sinh nhật Trang trí YEAR END PARTY công ty Trang trí tất niên cuối năm Trang trí tất niên xu hướng mới nhất Trang trí sinh nhật bé trai Hải Đăng Trang trí sinh nhật bé Khánh Vân Trang trí sinh nhật Bích Ngân Trang trí sinh nhật bé Thanh Trang Thuê ông già Noel phát quà Biểu diễn xiếc khỉ Xiếc quay đĩa
Thiết kế website Thiết kế website Thiết kế website Cách kháng tài khoản quảng cáo Mua bán Fanpage Facebook Dịch vụ SEO Tổ chức sinh nhật