How to do time zone conversions for Microsoft SQL Server databases using Looker?
- January 25, 2018
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:
SQL LookML parameter
We can manually define time zone conversions using database functions for LookML dimensions.