🌎 Timezone Settings

Introduction

Dealing with timezones can be a challenging task, and it's a complexity that many Business Intelligence (BI) tools grapple with, including our own. This document provides a comprehensive overview of how we manage timezone calculations.

The Problem: Your database typically records timestamps in a specific timezone, often in UTC. However, there might be a need to process data in a different timezone, such as the one your business operates in.

Let's walk through a few examples where this discrepancy might impact your reporting system.

Problem 1: Aggregating Timestamp Into Daily Metrics

In a scenario where we aim to aggregate timestamps to calculate "Daily Orders," and given that your e-commerce business operates in Vietnam (UTC+7), the use of a UTC+0 timezone and UTC+7 time reference will yield two different results:

• An order placed at 2020-01-02 23:00:00 UTC should belong to 2020-01-03 for your business, as it happens at 6 am the next day UTC+7.
• When aggregating daily orders, that order should count towards 2020-01-03 instead of 2020-01-02.

Problem 2: Resolving Relative Dates

Users can use Relative Date Syntax to input expressions like yesterday or 1 week ago, and Holistic will translate them into precise dates.

However, if you are in timezone UTC+7, the expression today resolves to n+1, and yesterday resolves to n.

Solution: Holistics’s Timezone Settings

Holistics converts timestamps and relative dates to the specified timezone by detecting the database timezone and rewriting SQL queries accordingly. The underlying data remains unchanged, and the query results are adjusted using Holistics's timezone settings, providing a tailored and reliable experience with a variety of timezone options.

1. Organization Timezone

To change the default timezone for all explorations and reports, go to Admin Settings > General Settings > Organization Timezone:

2. Dashboard Timezone

For companies with teams in different countries, the Dashboard Timezone feature allows each team to view data in their local timezones, overriding the Organization Timezone at the dashboard level.

This enables tailored dashboards, such as creating one for Vietnamese teams with their local timezone (UTC+07:00) while other reports and dashboards maintain the default Organization Timezone (UTC+08:00).

Dynamic Dashboard Timezone

You can enable viewers to flexibly change the timezone when viewing a dashboard by toggling the switch below.

To prevent unnecessary dashboard loading when users initially open it, we advise turning off the Dashboard Auto-load function.

3. Embedded Analytics Timezone

For timezone settings for Embedded dashboards, please visit Embedded Analytics Timezone Settings doc.

caution

Our Timezone Settings are not applied to Adhoc Query and Transform Model Creation View:

• We keep your original query untouched to ensure that the data result matches the query logic. It not only enables the users to access raw data with minor modification but also allows them to define any query logic without interfering with Holistics logic.
• However, the settings still affect all of your reports although they are built from transform models.

Supported Databases

We support all DBs for this feature. Below details the database-specific notes:

DBs that have version constraint

• Microsoft SQL Server (from version 2016)
• Clickhouse (from version 1.1.54362, released on 2018-03-11)

DBs that require additional setup

• Snowflake
• MySQL

Differences between Supported and Unsupported Databases

	Date Filter	Query Processing	Holistics Internal Processing
Supported Databases	Use your Organization Timezone	Use your Organization Timezone	Use your Organization Timezone
Unsupported Databases	Use your Organization Timezone	Use your default Database Timezone	Use UTC

FAQs

I want to skip the timezone conversion for specific columns, what should I do?

Currently, Holistics automatically converts all your time-based data, but we are working on a feature that will allow you to skip the conversion for certain columns. Stay tuned for updates!

Is this feature supported for all Holistics versions (2.0 / 2.5 / 2.7 / 3.0 / 4.0)?

Due to differences in the working mechanism, we only support the new Timezone Setting for Holistics version 3.0 onwards.

I am on version 3.0+, but I cannot see this feature available on my end. What should I do?

This feature might be disabled for the following reasons:

1. You are using unsupported database versions, or haven’t set up your database yet: Supported Databases.
2. Your team already converted the timezone outside Holistics and asked us to skip this feature for your account.