Timestream ODBC & DirectQuery

Hi DavinGonzales ,

Thanks for reaching out to the Microsoft fabric community forum. 

The ODBC logs show this query is being transformed into:

sql code:

where "C1" < {ts '2025-04-01 12:00:00'} and "C1" >= {ts '2025-04-01 00:01:01'}
This syntax is standard ODBC escape syntax for timestamps ({ts '...'}), but Amazon Timestream does not support this syntax natively, which explains why removing it in the Timestream query editor works.

Note:The problem likely stems from how the custom Timestream ODBC driver handles ODBC escape syntax when used with DirectQuery. Since Power BI uses {ts '...'} escape sequences internally for timestamps in DirectQuery mode, this becomes incompatible with Timestream.

Please follow below steps:

1. Use Import Mode: If real-time data is not strictly required, use Import Mode instead of DirectQuery: Change your data source to Import instead of DirectQuery. Apply your time filter Power BI will convert the data before querying, bypassing the {ts} issue.

2. If You Need DirectQuery:

1.Transform the time column into a string or integer in Power BI:

Create a calculated column that casts the timestamp to a format that avoids {ts} syntax:

DAX:

TimeText = FORMAT([time], "yyyy-MM-dd HH:mm:ss")

Filter on TimeText, which may avoid triggering {ts} syntax.

2. Custom SQL (if supported by the connector): Try defining a native SQL query in the connector setup that parses time ranges using from_iso8601_timestamp() or another Timestream-friendly format. Unfortunately, custom SQL is often restricted in DirectQuery for connectors.

3. Raise the issue with AWS: Report to AWS Support . This is a potential bug or lack of support in the ODBC driver for Power BI DirectQuery mode.

Please refer communtiy threads .

Solved: Power BI DirectQuery Error with Simba ODBC – Anyon... - Microsoft Fabric Community

Solved: Direct Query using ODBC - Microsoft Fabric Community

Solved: DirectQuery with ODBC (Postgre SQL) - Microsoft Fabric Community

Enabling DirectQuery for an ODBC-based Power Query connector - Power Query | Microsoft Learn

If you find this post helpful, please mark it as an "Accept as Solution" and consider giving a KUDOS. Feel free to reach out if you need further assistance.
Thanks and Regards