How to do time zone conversions for Microsoft SQL Server databases using Looker?

From Looker v6.22 and MS SQL Server 2016 version onwards, Looker has started supporting time zone conversions for Microsoft SQL Server databases. Hence you would use the same methods as would use to convert timezones for other databases.

Multiple settings within Looker can be used to convert time-based data:

• System Time Zone
• Database Time Zone
• User Specific Time Zones
• Application Time Zone
• Query Time Zone
• convert_tz LookML parameter
• SQL LookML parameter

System Time Zone  

The system time zone is the time of the server where the Looker application is deployed. This time zone is not configurable through the Looker application.

Database Time Zone

This is the time zone of the database itself. It is not advisable to change this as it may lead to unexpected results. The time-related values in the database are stored in the same time zone.

User Specific Time Zones

This time zone is specific to a Looker user; if a user runs a query with date values, then Looker would convert the result to match the user-specific time zone and display results.

Application Time Zone

This is the default time zone for scheduling data delivery.

Query Time Zone

This time zone is only visible if we have a disabled user-specific time zone, and we can use this as a default query output time zone.

convert_tz LookML parameter

Looker converts time zones by default to user-specific time zones or the system time zone. To avoid this, we can disable the time zone conversion for individual fields by setting the following property in the field’s LookML:

convert_tz: no 

SQL LookML parameter

We can manually define time zone conversions using database functions for LookML dimensions.

Up Next:

Read How to allow dimension/measure tooltip in Looker to show the description to user when pointing over name of the object in a Look (table)?