PowerBI Sending Invalid Query to Snowflake for TIME Data Type

[brycebaker]

Connecting to Snowflake using built-in Snowflake connector (v2.23.3). When adding a table that includes a column of TIME(9) datatype and then dragging it to the canvas to use as a table, the below error appears. Confirmed running directly against Snowflake that the values being passed in are invalid for the TIMESTAMPDIFF() function (minute, TIMESTAMP, TIME) to be able to perform the calculation. Previewing the data works fine, but it seems all of the additional "magic" it's doing isn't well formed.

OLE DB or ODBC error: [DataSource.Error] ODBC: ERROR [42p13] SQL compilation error: error line 8 at position 138
Invalid argument types for function 'TIMESTAMPDIFF': (VARCHAR(14), TIMESTAMP_NTZ(9), TIME(9)).

Generated query from Snowflake when attempting to drag TIME column to canvas:

select "MY_TIME_COLUMN"
from 
(
select "MY_TIME_COLUMN",
"C1",
case
when not "C1" is null
then { fn timestampadd(SQL_TSI_SECOND, 
{ fn convert(
{ fn timestampdiff(SQL_TSI_FRAC_SECOND, 
{ fn timestampadd(SQL_TSI_MINUTE, 
{ fn timestampdiff(SQL_TSI_MINUTE, TO_TIMESTAMP('2000-01-01 00:00:00.0000000','YYYY-MM-DD HH24:MI:SS.FF9'), "C1") }, 
TO_TIMESTAMP('2000-01-01 00:00:00.0000000','YYYY-MM-DD HH24:MI:SS.FF9')) }, "C1") }, 
SQL_DOUBLE) } / CAST(1000000000 as DOUBLE), 
{ fn timestampadd(SQL_TSI_MINUTE, 
{ fn minute("C1") }, 
{ fn timestampadd(SQL_TSI_HOUR, 
{ fn hour("C1") }, TO_TIMESTAMP('1899-12-30 00:00:00.0000000','YYYY-MM-DD HH24:MI:SS.FF9')) }) }) }
else TO_TIMESTAMP('1899-12-28 00:00:00.0000000','YYYY-MM-DD HH24:MI:SS.FF9')
end as "C2",
case
when "C1" is null
then CAST(0 as INTEGER)
else CAST(1 as INTEGER)
end as "C3"
from 
(
select "MY_TIME_COLUMN",
"MY_TIME_COLUMN" as "C1"
from "MY_DB"."DBO"."MY_FACT_TABLE"
) as "ITBL"
group by "MY_TIME_COLUMN",
"C1"
) as "ITBL"
order by "ITBL"."C2",
"ITBL"."C3"
LIMIT 501 OFFSET 0;

[v-yingjl]

Hi @brycebaker ,

You can try to use DateDiff() function to replace the TIMESTAMPDIFF() function in Snowflake.

Please refer:

1. TIMESTAMPDIFF 
2. DATEDIFF 

Best Regards,
Community Support Team _ Yingjie Li

[brycebaker]

@v-yingjl The query hitting Snowflake is generated entirely by PowerBI. The column is directly sourced and not part of a calculated column. There's no opportunity to modify how it's handling the column that I'm aware of.

[RaphaelBijiaoui]

Hi, I have the exact same problem.

After a quick investigation, 'SQL_TSI_MINUTE' keyword is not recognized by snowflake.

Being a generated query, it's most likely womthing to be fixed in the connector.

[brycebaker]

@RaphaelBijiaoui I actually don't have any issue with SQL_TSI_MINUTE in the query. Ran the below and it returns fine, so it seems Snowflake recognizes the original Oracle value as a synonym for 'MINUTE'.

select top 100
TIMESTAMPDIFF(SQL_TSI_MINUTE, TO_TIME('00:23:32'), "C1"), C1
from (select MY_TIME_COLUMNas C1
from MY_TABLE) q;

The issue seems to be in the data types that're being used in the second argument. It will only accept like for like data types to do the DIFF, so both have to be TIME data type. The problem is that PowerBI is passing in a DATETIME evaluated value for the second argument and not a TIME. 

[RaphaelBijiaoui]

@brycebaker :

Your are right 👍

Thanks to your analysis, I was able to patch my data to make it work :

- instead of using the TIME type  in the Snowflake table column, I created a new one with a DATETIME type.

- I used the '2000/01/01' date as the prefix of all time values (the PowerBI generated query seems to use that date as pivot, I hope it stays the same in the future until the problem is fixed)

- I changed the type in PowerBI from DateTime to Time so the data stays visualy the same for the end user (date part is just not showed, particular care will need to be taken to the DAX queries using the field)

This can only be temporary :

- because we will need to do regression testing each time the connector is updated to validate that the query is generated in the same way (no guaranty the inner working of the connector stays stable over time)

- also because of the memory/disk space taken by the DATETIME type Vs TIME type

[brycebaker]

@RaphaelBijiaoui Workaround I'm recommending at the moment is to store the time value as a VARCHAR(9), then set the data type in the PowerBI model to Time. That protects against some odd miscalculation involving dates a little bit better by ensuring only the time is evaluated.

I have a support call scheduled here in a few minutes to demo the issue and hoping to get a 'will fix' response on this one since it's clearly a PowerBI bug.

[brycebaker]

CC: @RaphaelBijiaoui 

Per Microsoft support ticket #2108090060003920, this has been accepted as a PowerBI bug and will be fixed by the dev team. Awaiting fix ID #.