Question

Snowflake and Looker Date Transfer Issue (snowflake time zone related) - about timestamp_ntz

  • 18 January 2021
  • 17 replies
  • 1161 views

there’s multiple data types available in snowflake - Timestamp_tz, timestamp_ntz.

when we use dimension group function in lookML (time type), and I bring out a filter using dimension group field, for example, we want data after 2020.01.01, LOOKER will generate TIMESTAMP_TZ in the query rather than timestamp_ntz. and I didn’t find any way to control this...  (I tried including convert_tz in look ML in that group dimension but it didn’t work)

basically it will generate a query like this. 

 

EXTRACT('EPOCH', CAST(TO_TIMESTAMP('2020-01-01') AS TIMESTAMP_TZ))

 

I want it to be: 

 

EXTRACT('EPOCH', CAST(TO_TIMESTAMP('2020-01-01') AS TIMESTAMP_NTZ))

 

So is there any way to let it generate TIMESTAMP_NTZ? If it’s not, the outcome from snowflake and Looker will be not consistent when we include a comparison logic in where clause. Thank you!


This topic has been closed for comments

17 replies

there’s multiple data types available in snowflake - Timestamp_tz, timestamp_ntz.

when we use dimension group function in lookML (time type), and I bring out a filter using dimension group field, for example, we want data after 2020.01.01, LOOKER will generate TIMESTAMP_TZ in the query rather than timestamp_ntz. and I didn’t find any way to control this...  (I tried including convert_tz in look ML in that group dimension but it didn’t work)

basically it will generate a query like this. 

 

EXTRACT('EPOCH', CAST(TO_TIMESTAMP('2020-01-01') AS TIMESTAMP_TZ))

 

I want it to be: 

 

EXTRACT('EPOCH', CAST(TO_TIMESTAMP('2020-01-01') AS TIMESTAMP_NTZ))

 

So is there any way to let it generate TIMESTAMP_NTZ? If it’s not, the outcome from snowflake and Looker will be not consistent when we include a comparison logic in where clause. Thank you!

there’s multiple data types available in snowflake - Timestamp_tz, timestamp_ntz.

when we use dimension group function in lookML (time type), and I bring out a filter using dimension group field, for example, we want data after 2020.01.01, LOOKER will generate TIMESTAMP_TZ in the query rather than timestamp_ntz. and I didn’t find any way to control this...  (I tried including convert_tz in look ML in that group dimension but it didn’t work)

basically it will generate a query like this. 

 

EXTRACT('EPOCH', CAST(TO_TIMESTAMP('2020-01-01') AS TIMESTAMP_TZ))

 

I want it to be: 

 

EXTRACT('EPOCH', CAST(TO_TIMESTAMP('2020-01-01') AS TIMESTAMP_NTZ))

 

So is there any way to let it generate TIMESTAMP_NTZ? If it’s not, the outcome from snowflake and Looker will be not consistent when we include a comparison logic in where clause. Thank you!

Hello,

 

Please go through this link: https://hevodata.com/learn/snowflake-timestamp/ 

The above link gives you information.

If you want to learn more about Snowflake, feel free to contact me.

 

Thanks & Regards,

Anita

 

Hi @yilin.liu.

Were you able to fix this issue?

I’m encountering a similar issue but some of our underlying configuration is different. I put some details at the end. Thanks!

-Greg

----

Our field is a TIMESTAMP_TZ type in Snowflake so we have it set to datatype: timestamp in the LookML. Both Snowflake and Looker are worktime employee monitoring software best using UTC but rather America/New_York. When Looker generates the SQL, the SQL looks OK but it can produce some unexpected results.

For example, the TIMESTAMP_TZ value is often midnight, so an example record would look like 2019-12-30 00:00:00.000000000 +00:00. When I set a filter for that date in Looker, the generated SQL is:

WHERE TRANSACTION_DATE = TO_DATE(TO_TIMESTAMP('2019-12-30')

But when Looker runs that query, the record doesn’t appear in the results! Behind the scenes, Snowflake is doing a timezone conversion (because it is type TIMESTAMP_TZ) and thinks the record date is actually 12/29/2019.

The “convert_tz” LookML parameter has no effect on the generated SQL. As a workaround, I can put this in the LookML: sql: ${TABLE}.TRANSACTION_DATE::TIMESTAMP_NTZ

But I’d still like to learn more about this behavior. Maybe I’m missing something.

We are using Looker version 7.20.29.


Thank You!

Hi Greg! I believe we are encountering exactly the same issue cause by time zone. 

 

below are what I did and it solved the issue: 

  1. I asked dev team to set my snowflake account to UTC rather than Los Angeles. 
  2. in looker snowflake connection settings, I set both database time zone and query time zone as UTC as well. 

Both need to be set up so that the issue was solved: 

 

So I recommend bringing the query looker generated to snowflake, decomposing it to see how snowflake is treating the query step by step, especailly the steps related to date transformation. that’s how I found out the problem. 

to be more specific, i found there’s a step that snowflake added a time zone component to the date I entered to the comparison filter. that step is “ 

CAST(TO_TIMESTAMP('2020-12-01') AS TIMESTAMP_TZ)” 

and there’s 8 hours between UTC time and Los Angeles time, so snowflake is adding  + 8000 at the end. and when i want the dates equal or greater than that date, 2020-12-01 will not be included. so that’s why there’s always one day discrepancy.

2020-12-01 00:00:00.000 +8000

so after dev team reset up my account, that step will return

2020-12-01 00:00:00.000 +0000

so that's how it been solved. Hope this helps!.. 

 

your workaround looks smart and I didn’t even think about it! I will definitely try something similar! 

Best,

Yilin

Hi @yilin.liu.

Were you able to fix this issue?

I’m encountering a similar issue but some of our underlying configuration is different. I put some details at the end. Thanks!

-Greg

----

Our field is a TIMESTAMP_TZ type in Snowflake so we have it set to datatype: timestamp in the LookML. Both Snowflake and Looker are not using UTC but rather America/New_York. When Looker generates the SQL, the SQL looks OK but it can produce some unexpected results.

For example, the TIMESTAMP_TZ value is often midnight, so an example record would look like 2019-12-30 00:00:00.000000000 +00:00. When I set a filter for that date in Looker, the generated SQL is:

WHERE TRANSACTION_DATE = TO_DATE(TO_TIMESTAMP('2019-12-30')

But when Looker runs that query, the record doesn’t appear in the results! Behind the scenes, Snowflake is doing a timezone conversion (because it is type TIMESTAMP_TZ) and thinks the record date is actually 12/29/2019.

The “convert_tz” LookML parameter has no effect on the generated SQL. As a workaround, I can put this in the LookML: sql: ${TABLE}.TRANSACTION_DATE::TIMESTAMP_NTZ

But I’d still like to learn more about this behavior. Maybe I’m missing something.

We are using Looker version 7.20.29.

Userlevel 3

Hi Yilin,

 

No problem!

 

Do post your finding out here if you manage to get to the bottom of it, as others might come across the same!

 

Have a good weekend,

thank you Maddie!!!

Userlevel 3

Hi Yilin,

 

That is odd - I think my recommendation here is to reach out to support, as it does not seem to be coming from settings and it could well be a bug.

 

Best,

I did turn that on but still not showing up..

Userlevel 3

Hi Yilin,

 

That detail is only visible if you enable User Specific Time Zones, so try to turn it on for a little while to see what your user Time Zone was set to:

 

 

Best,

so i didn’t see any place to change the time zone, does that mean my manager controlled it so that we cannot change by ourselves? 

 

Userlevel 3

Hi Yilin,

 

I think that might be because your own user profile has been set up with a specific timezone:

 

This is in your user profile.

 

Please update that to Los Angeles as well and let me know if you still see the conversion being applied.

 

 

Best,

Hi Maddie,

 

For application time zone, I also set it as Los Angeles. 

and I don’t want my user have the ability to change their own time zones..that will cause some confusion I believe.. I would rather set the time zone for them.

and I found a weird thing - below are my set up:

snowflake timezone - Los Angeles  (dev team set that up, and I prefer not to change it even though we are in DC)..

Looker application zone - Los Angeles (just for it be the same as snowflake, which i believe it’s better solve my issue)

User Specific Time zones - Disabled

Database Connection set up - 1) Database timezone - Los Angeles  2) Query Time zone - Los Angeles.  (same as the previous settings)

 

I believe those are all consistent.. But, in my interface,, it’s showing I am in New York..

 

after setting those up, it’s still generating timestamp_tz. . :(

 

Does enabling User specific time zone is the only way to get it right? thank you Maddie!

Userlevel 3

Hi Yilin,

 

No problem, we’ll get to the bottom of it, even if I fall asleep on my keyboard haha!

 

Have a little look in your Settings:

Have you got User Specific Time Zones enabled by any chance? And what Application Time zone have you set up?

 

 

Best,

Hi Maddie,

thank you for the answer. But I tried setting all of those to be the same as America/Los_Angeles. 

but it still doesn’t work.. 

as you could see, in looker view, this is the dimension group (occupancy) I set up. 

and in the explore, as you could see, i used that occupancy filter I set up in the view, but it’s still generating as timestamp_tz, not timestamp_ntz..

 

 

hopefully this explain a little bit more! thank you maddie so much!!

Best,

Yilin

Userlevel 3

Hi Yilin,

 

I see. I think this is to do with how you configure your connection.

 

Is there a need for all new dates to be by default set to no timezone when you create the views from tables? If that is the case, then setting your connection and timezone to be the same:

 

All dates will be generated with a cast as TIMESTAMP_NTZ by default.

 

Otherwise you can manually apply a cast to TIMESTAMP_NTZ using:

CAST(${TABLE}.created_at AS TIMESTAMP_NTZ)

 

Please let me know if this helps.

 

Best,

yeah.. I didn’t find a way to let query generate “AS TIMESTAMP_NTZ”, the default is “AS TIMESTAMP_TZ”, how to let it generate the first way?  I don’t want it consider time zone difference..

Userlevel 3

Hi Yilin,

 

Typically, all functions which work on Snowflake can also be applied in Looker.

 

Are you encountering any errors?

 

 

Best,