User Parameters Use Case - Automatic Time Zone Conversion

[TriAnthony]

User Parameters Use Case - Automatic Time Zone Conversion

This article explains one of the example use cases of User Parameters laid out in User Parameters - What They Are and What They're For (Example Use Cases).

In addition to connection settings, User Parameters can also be used in table queries in live models to provide tailored data to end users. One of the most common uses of this type of parameter is for customizing a formula to achieve an automatic localization, e.g. time zone conversion.

The idea is to use a parameter to associate each user group to their respective time zone name or UTC offset, then use the parameter to customize the table query and convert the default time zone to users' local time zones.

Let's walk through an example of an automatic time zone conversion on a Snowflake live model. The first step is to create the user parameter. When creating parameters for a query, be sure to select Custom Live Queries in the Applicable Areas field.

Once you've created the user parameter, the next step is to assign a value for each group (or individual user, if needed). For this use case, the value should represent the time zones of the users, either the standardized name or the UTC offset, depending on the database that you use. Snowflake, in particular, has a pre-built time zone conversion function called CONVERT_TIMEZONE that accepts IANA standardized time zone names as inputs. This function greatly simplifies the conversion formula because it automatically takes into account daylight saving time/summer time. Below is an example of user groups and value assignment for the time zone parameter.

Now that the parameter values have been assigned to groups, the final step is to create/modify your table query(ies). Let's take a table called FactPayments in an insurance company's data model as an example. This is the original query of the table:

SELECT
     P.CLAIM_KEY
     , P.POLICY_KEY
     , P.PAYMENT_DT_TIME
     , P.PAIDAMOUNT
     , P.STATUS
     , PO.CUSTOMERID
FROM "INSURANCE_CLAIMS"."PAYMENTS" P
JOIN "INSURANCE_CLAIMS"."POLICIES" PO
ON P."POLICY_KEY" = PO."POLICY_KEY"

In this example, payment dates and timestamps are stored in UTC in the PAYMENT_DT_TIME column. To convert the timestamps to users' local timezones, add a new calculated column utilizing Snowflake's CONVERT_TIMEZONE function and the user parameter. To add a parameter to a query, type two curly brackets, then select the parameter from the dropdown menu. Here is how the conversion formula should look like:

CONVERT_TIMEZONE(UTC , {{TIMEZONE}}, PAYMENT_DT_TIME)

Here is the full, updated query of the FactPayments table:

SELECT
     P.CLAIM_KEY
     , P.POLICY_KEY
     , P.PAYMENT_DT_TIME
     , CONVERT_TIMEZONE('UTC', '{{TIMEZONE}}', P.PAYMENT_DT_TIME) AS PAYMENT_DT_TIME_LOCAL //Date with timestamp
     , DATE_TRUNC('DAY', PAYMENT_DT_TIME_LOCAL) AS PAYMENT_DT_LOCAL //Date without timestamp
     , P.PAIDAMOUNT
     , P.STATUS
     , PO.CUSTOMERID
FROM "INSURANCE_CLAIMS"."PAYMENTS" P
JOIN "INSURANCE_CLAIMS"."POLICIES" PO
ON P.POLICY_KEY = PO.POLICY_KEY

Save the query and repeat the same step for all other tables that require time zone conversion. Once completed, republish the model and revisit your dashboard. For this example, here is how the data originally looks with dates in UTC time zone:

Here is how it looks for users in the U.S. Eastern time zone:

Note: if your database does not have a pre-built time zone conversion function, you can use UTC offsets as the values of the parameter. For example, for Eastern Standard Time (UTC-5), the value for the parameter should be -5. You can then add the offset to the timestamp in the query using the date manipulation function that your database supports (DATEADD for SQL Server, Snowflake, and Amazon Redshift, DATE_ADD for MySQL, + INTERVAL for PostgreSQL and Oracle). The limitation of this approach is that it doesn't automatically account for daylight saving time. Therefore, you will need to build logic in the query to handle this aspect (e.g., get the daylight saving period for each time zone, then use a CASE statement to check if each date falls within that period).