Unknown expression or function identifier 'SQL_TSI_FRAC_SECOND'

[linux-wizard]

Describe the bug

ClickHouse ODBC driver do not support SQL_TSI_FRAC_SECOND which could leads to some queries failing in PowerBI when using a date table (https://learn.microsoft.com/en-us/power-bi/guidance/model-date-tables, https://www.datacamp.com/tutorial/how-to-create-date-tables-in-power-bi-tutorial)
For example:

SELECT Date, floor(CAST(dateDiff('day', _CAST(0, 'Nullable(Date)'), addDays(Date, _CAST(2, 'Nullable(Int32)'))), 'Float64') + ((CAST(dateDiff(SQL_TSI_FRAC_SECOND, addDays(_CAST(0, 'Nullable(Date)'), dateDiff('day', _CAST(2, 'Nullable(Int32)'), addDays(Date, _CAST(0, 'Nullable(Date)')))), addDays(Date, _CAST(2, 'Nullable(Int32)'))), 'Float64') / _CAST(86400000., 'Nullable(Float64)')) * multiIf(addDays(Date, _CAST(2, 'Nullable(Int32)')) < _CAST('-2208988800', 'Nullable(DateTime64(9))'), _CAST(-1, 'Nullable(Int32)'), _CAST(1, 'Nullable(Int32)')))) AS C1 FROM db.Date WHERE NOT (Date IS NULL). 

Clickhouse dateDiff does support nanoseconds (https://clickhouse.com/docs/sql-reference/functions/date-time-functions#date_diff) and so support could be added to the driver.
SQL_TSI_FRAC_SECOND match with nanoseconds as units: https://calcite.apache.org/javadocAggregate/org/apache/calcite/sql/fun/SqlTimestampAddFunction.html

Expected behaviour

date_diff at the nanoseconds level should succeed

Error log

Unknown expression or function identifier 'SQL_TSI_FRAC_SECOND'

Query log

Configuration

Environment

• Driver version: 1.4.1.20250523
• OS: Windows Server 2022 Datacenter 21H2
• ODBC Driver manager:

ClickHouse server

• ClickHouse Server version: 24.8
• CREATE TABLE statements for tables involved:
• Sample data for all these tables, use clickhouse-obfuscator if necessary
  custom date table schema:

-- db.`Date` definition

CREATE TABLE db.Date
(

    `PK_ID` String,

    `PK_ID_Number` Int32,

    `Date` DateTime,

    `Year` LowCardinality(String),

    `Month` LowCardinality(String),

    `Month_Number` Int32,

    `Month_Year` LowCardinality(String)
)
ENGINE = ReplicatedMergeTree('/clickhouse/tables/{shard}/db/Date/{uuid}',
 '{replica}')
PRIMARY KEY PK_ID
ORDER BY PK_ID
SETTINGS index_granularity = 8192;

[linux-wizard]

@slabko In fact this is quite a bad bug as this prevent the conversion of a table in ClickHouse as a PowerBI date table.
Without a date table, you can't use PowerBI Time intelligence allowing easy date/time navigation/filtering

• https://learn.microsoft.com/en-us/power-bi/transform-model/desktop-date-tables
• https://learn.microsoft.com/en-us/dax/time-intelligence-functions-dax

However it seems the fix could be to just uncomment these lines:

• https://github.com/ClickHouse/clickhouse-odbc/blob/master/driver/escaping/lexer_declare.h#L4
• https://github.com/ClickHouse/clickhouse-odbc/blob/master/driver/escaping/escape_sequences.cpp#L46

[slabko]

Hey @linux-wizard,

Thank you for reporting this issue. Unfortunately, with my very limited knowledge of calendars in Power BI, I immediately ran into a series of other error messages when I tried to enable SQL_TSI_FRAC_SECOND and SQL_TSI_QUARTER.

Given my limited Power BI experience, I’d really appreciate it if you could provide a minimal working scenario. I’ve found different ways of using calendars in Power BI, but most of them seemed very inefficient to me. Do you have an idea of how you would approach this? Seeing your method would help me focus on this particular scenario.

By scenario, I mean: how do you create the calendar table? A table in ClickHouse, a table in Power BI? or perhaps in some automated way?

[linux-wizard]

Hi @slabko , so I think the best is to do as follow:

• create a dim_date table in ClickHouse which will be use as "Date table" for PowerBI

CREATE TABLE IF NOT EXISTS `db`.`dim_date`
    date_key UInt32,
    full_date Date,
    day_of_month UInt8,
    day_name String,
    day_of_week UInt8,
    day_of_year UInt16,
    week_of_year UInt8,
    month_of_year UInt8,
    month_name String,
    quarter_of_year UInt8,
    year UInt16,
    is_weekend UInt8,
)
ENGINE = MergeTree()
ORDER BY full_date;

• Populate the dim_date table

INSERT INTO db.dim_date
SELECT
    toUInt32(formatDateTime(d, '%Y%m%d'))              AS date_key,
    d                                                  AS full_date,
    toUInt8(toDayOfMonth(d))                           AS day_of_month,
    formatDateTime(d, '%a')                            AS day_name,
    toUInt8(toDayOfWeek(d))                            AS day_of_week,   -- 1=Monday ... 7=Sunday
    toUInt16(toDayOfYear(d))                           AS day_of_year,
    toUInt8(toISOWeek(d))                              AS week_of_year,
    toUInt8(toMonth(d))                                AS month_of_year,
    formatDateTime(d, '%b')                            AS month_name,
    toUInt8(toQuarter(d))                              AS quarter_of_year,
    toUInt16(toYear(d))                                AS year,
    toUInt8(toDayOfWeek(d) IN (6,7))                   AS is_weekend     -- 1 if Sat/Sun, else 0
FROM (
    SELECT addDays(toDate('2020-01-01'), number) AS d
    FROM numbers(
        dateDiff('day', toDate('2020-01-01'), toDate('2030-12-31')) + 1
    )
);

• Go to PowerBI desktop
• Connect to ClickHouse in DIrectQuery Mode
• Be sure to import the dim_date table in the semantic model
• In the semantic model:
  • select the dim_date table,
  • right click and select Mark as date table
  • select full_date as the Date column
    • This is where we fail to convert the dim_date table as a "Date table" for PowerBI.
    • Looking at clickhouse logs, we will see several queries made by PowerBI to validate the "Date table"

You can also test by using DateTime type for the full_date column (if think my test case was using DateTime as type for the "Date column"

Resources:

• https://learn.microsoft.com/en-us/power-bi/transform-model/desktop-date-tables
• https://medium.com/microsoft-power-bi/building-an-effective-date-table-with-dimdate-for-kpi-measurement-and-chart-decluttering-in-power-aa63041fff97
• https://learn.microsoft.com/en-us/fabric/data-warehouse/dimensional-modeling-dimension-tables#date-dimension

[slabko]

@linux-wizard Awesome, thank you very much!

Let me try this and see what problems I run into along the way.