KNOWLEDGE BASE

Tableau returns a Different Timestamp than what is Displayed in Snowflake When Connecting to Snowflake


Published: 13 Jun 2019
Last Modified Date: 07 Nov 2019

Issue

When connecting to Snowflake, Tableau returns a different timestamp than what is displayed in Snowflake. This issue can arise when working with TIMESTAMP_TZ and TIMESTAMP_LTZ field types, or when returning current_timestamp from the database, while the end-user's machine is set to a different time zone than that specified by the TIMEZONE parameter for their account/session or where the Snowflake database is located in a different time zone. 

Because Tableau will query the database using timestamps in the local machine timezone, if the timezone in Snowflake is different, the data returned may be incorrect, or no data is returned at all.

It should also be noted that, when connecting to Snowflake located in a different time zone, current_timestamp and now() return different values. Namely, now() returns the date in the data source time zone while current_timestamp returns the date in the local machine time zone.

Environment

  • Tableau Desktop
  • Snowflake

Resolution

To ensure Tableau returns the timestamp in the same timezone as specified in Snowflake, please use one of the following options:
 
  1. Change all DateTime fields to TIMESTAMP_NTZ field type in the database, or use Custom SQL to cast fields to this type.
  2. Have the Snowflake admin update the user account so that the TIMEZONE parameter setting matches the timezone of their local machine.
  3. Using initial SQL in Tableau, alter the TIMEZONE parameter for that session so that it matches the timezone of the local machine:
ALTER SESSION SET TIMEZONE = 'UTC';

 

Cause

This behaviour is currently expected. When using the Snowflake ODBC driver, the Snowflake TIMEZONE setting is not respected by the clients. Fields with a specified timezone in Snowflake will always return time in the local machine time zone.

For more information on this behaviour, please see the below Snowflake Community post: ODBC/JDBC TIMESTAMP_LTZ and 3rd party clients

 

Additional Information

The difference between the two values was introduced when Issue ID: 669546 was released in 20183. As documented in Tableau Online Help, "NOW() returns the data source server time." Since current_time is not natively invoked by Tableau Desktop, the value is returned directly from the driver in the local machine time zone.

 
Did this article resolve the issue?