Why does Power BI not recognize Snowflake TIMESTAMP_TZ as DateTimeZone

[jmg80525]

I have worked with the data engineer to ensure that timestamp data being pipelined into Snowflake is cast as Snowflake's TIMESTAMP_TZ data type; the equivilent, I assume, of DateTimeZone in SQL Sever.  The zone is specified as UTC +0.  Or at least when I query in Snowflake the results set values for timestamp seem to indicate that.

I say seem to indicate that becasue if I inspect the schema it shows the Snowflake datatype as TIMESTAMP_TZ(9). What the 9 indicates is beyond me.

However, when I bring the SF table into Power Query it doesn't recognize the the TIMESTAMP_TZ(9) as what we would refer to as DateTimeZone; it can't seem to interpret the zone and initially casts it as DateTime.  If I change the type to DateTimeZone it applies a UTC zone of -6.  

Uhhh? O.K. Why?
For kicks and giggle I thought I would cast the values stored as TIMESTAMP_TZ as VARCHAR expecting to see the the offset portion to be expressed at +0000. Below is what I get.

Anyone else had luck with with TIMESTAMP_TZ (aka DateTimeZone) data in Snowflake?

[jmg80525]

To the best of my determination, the Snowflake datatype TIMESTAMP_TZ is not ISO_8061 compliant and that is the what Power BI is looking for to cast the data as DATETIMEZONE as opposed to DATETIME.  Here's what I did to formulate that response.

• Create a trial account in SF

Generated the following SQL:

ALTER SESSION SET TIMESTAMP_TYPE_MAPPING = TIMESTAMP_TZ;
CREATE TABLE tstx_test (timestamp_tz TIMESTAMP_TZ);
INSERT INTO tstx_test (timestamp_tz) VALUES (CURRENT_TIMESTAMP());

Assured that timestamp column casted correctly

Ran SQL against the newly formed table. Result below.

Brought the data into PBI Desktop and the transformation editor.  By default, PBI recognizes it as DateTime

Of 

If convert the column in PBI to DateTimeZone it applies my local time (MST-UTC(-7) zone to the timestamp. Which is not what is stored in Snowflake as shown on top. 

Please inform me if I'm missing something.

[lbendlin]

The standard is called ISO 8601

Apart from that (and for a second ignoring the fact that your server is not running on UTC (!!!)) it looks like the datetimezone value is accurate.   11am Pacific is 12pm Mountain.

What is your expectation - to see the UTC time?

[lbendlin]

Your TIMESTAMP_TZ result is not ISO-8601 conform.  Add the T  and it will work just fine.  It will work just fine without it too, but that's besides the point.

[jmg80525]

Could you clarify? Here's a better snapshot of what I'm seeing.  The data stored in Snowflake is cast as TIMESTAMP_TZ with the timezone being UTC(Z).  For whatever reason, PBI does not recoginzie the Snowflake data type being what it would call DateTimeZone.  Are you suggesting that the Snowflake data type TIMESTAMP_TZ is not ISO_8061 compliant?  As you can see the transformation step I applied to create PBI_TIMESTAMP_DTZ (DTZ being DateTimeZone) I simply took the DateTime value from the first column and concatenated the zone as text.  I fully realize I can transform the data into what I need.  What I want to understand is why PBI doesn't cast Snowflake TIMESTAMP_TZ as DateTimeZone when pulling data from it.  Any insights are greatly appreciated. 

[lbendlin]

For whatever reason, PBI does not recoginzie the Snowflake data type being what it would call DateTimeZone

The reason is that the incoming data assumes UTC, but does not explicitly specify it. Can you ask your data engineer to help you change the query so the timestamp comes across in proper ISO-8601 ?

Or continue doing what you are doing - slap "+000"  onto the text.  Or use DateTime.AddZone

[Anonymous]

I'm sure you know that Z is for ZULU time. But perhaps Power Query is changing it to your local time zone.

--Nate

[jmg80525]

I didn't know that Z meant Zulu and I didn't realize the each hourly offset from the Prime Meridian is also represented by a letter.  HT to Wikipedia. 

 I'm in MST -7 (T, Tango) with my Power BI Desktop running on  a local laptop, but I am VPNed to the corp office which is in CST -6 (S, Sierra).  (Sorry, had to employ my newly learned knowledge:)  I have no idea why it would choose -6 when casting to DateTimeZone in Power Query.