Skip to main content

Timezone Settings

Introduction​

Timezones are a complicated beast that many BI tools are fighting with (and so do we). This document explains how we handle Timezone calculations.

The Problem: Your database stores timestamp under a specific timezone (normally UTC). However, this might not be the same timezone that you want to process your data in.

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

Impact: Aggregating Timestamp Into Daily Metrics​

Consider the below example where we want to perform a timestamp ⇒ day aggregation to calculate "Daily Orders". Using a UTC+0 timezone and UTC+7 time reference will yield 2 different results:

  • Your e-commerce business operates in Vietnam (UTC+7)
  • 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.

Impact: Resolving relative dates​

Using Holistics' Relative Date Syntax, users can type texts like yesterday, 1 week ago and Holistics will resolve them into proper exact dates. In the below diagram with the timezone at UTC+7, today will resolve to n+1, and yesterday will resolve to n.

This is especially important when you send daily email reports with relative date syntax.

Solution: Organization Timezone​

Holistics solves this by allowing you to adjust a setting called "Organization Timezone". This indicates the timezone that the business operates in.

Behind the scene, Holistics converts all timestamps and relative dates to the specified timezone before going with the calculation. Holistics does that by detecting the timezone used by your databases and rewriting the SQL queries to convert to the Organization Timezone.

caution
  • Some special databases require an additional setup for timezone information. Read more.
  • 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.

How to change Organization Timezone​

To change this setting, go to Admin Settings > General Settings > Organization Timezone:

caution

Changing Organization Timezone will likely affect reports results across the board.

Table columns affected by Timezone​

Only fields whose types contain time elements (datetime, timestamp, timestamp without time zone, etc) will be under effect of our smart Timezone adjustment.

Fields with date type will not be affected.

Where Timezone affects your Reporting System​

Timezone can affect how your data is processed, more specifically in these cases:

  • Aggregation: Aggregating records by date.
  • Condition: Applying conditions to limit by dates or date ranges.
  • Relative Date Resolution: Resolving text like today, yesterday into absolute dates.

Timezone displayed when viewing data​

When viewing any dashboards, viewers can see the timezone where this dashboard is processed at the top right corner:

Multiple Timezones for Different Business Units (upcoming)​

(This feature is still under development)

What if your company has multiple teams in different countries, and each team needs to view their data in their own timezones?

Specifically, you run a regional e-commerce website with operations in multiple countries. Each country has its own independent business team. All countries operate in a single website with the same backend database, but each country wants to report data in its own timezone.

For this problem, we introduce Dashboard-level Timezone. This allows you to override the Organization Timezone at the dashboard level.

For example, your business operates in both Singapore (headquarter) and Vietnam, and you already configured Singapore time (UTC+08:00) as Organization Timezone. If you want to build a separate dashboard for Vietnamese teams at their local timezone, you only need to change its Dashboard Timezone to Vietnam time (UTC+07:00). As a result, this dashboard will default to run in UTC+07:00, while other reports/dashboards still run in UTC+08:00.

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:

FAQs​

1. What happens if I use the database that Holistics does not support Timezone Settings?​

In case Holistics has not supported Timezone Settings for your database yet, please take note of the main technical differences compared to supported ones:

ItemSupported DatabasesUnsupported Databases

Query Processing

Use your Organization Timezone

Use your default database timezone

Holistics Internal Processing

Use your Organization Timezone

Use UTC

Date Filter

Use your Organization Timezone

Use your Organization Timezone

2. Is this feature supported for all Holistics versions?​

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

Let us know what you think about this document :)