Troubleshooting Timestamp_tz (Community)

View the original community article here

Last tested: Feb 1, 2021
 

When you autogenerate a new project or Create View from Table from a Snowflake schema, Looker will detect timestamp_tz types in the database and generate cast statements in the generated LookML that correctly handle this datatype. Specifically, in the sql parameter of the dimension groups the LookML generator creates, it will cast the field to timestamp_ntz to account for the actual timezone in the timestamp and not lose any information from the offset timezones.

Example:

sql: CAST(${TABLE}.created_at AS TIMESTAMP_NTZ) ;;

However, there may be some cases where this casting is not autogenerated when the view was created:

• If you create the view manually instead of autogenerating
• If you have a timestamp_tz equivalent type field in a SQL dialect where we do not generate this casting (currently we do so for Snowflake)
• If you are on an older version (before Looker 7.6) where we didn't generate this casting

In these cases, you may encounter strange issues where the values returned in the results don't match the values you've filtered on. Example:

To resolve this, you should add the cast to timestamp_ntz to the sql parameter for any dimension groups based on timestamp_tz type fields, as shown in the example sql parameter above.