Timezone Settings

(This feature is currently in active development. Please read our FAQs if you want to become beta testers)

Introduction

Your database often operates under a default timezone (normally UTC), but you will likely want every calculation or display to be in a different timezone.

This page describes how Holistics help you deal with this timezone issue. Understanding different timezone settings correctly is important for you to get the right results when working with your data.

Organization Timezone

Organization Timezone should be the timezone where you want to process and display your data, normally it's a timezone that your business operates in. For example, if you run an E-commerce business in Vietnam (UTC+7), regardless of what timezone your database store your data, you will likely want every calculation or display to be based in UTC+7.

Organization Timezone will have effect in the following scenarios:

  • Datetime aggregation: Aggregating records by date.
  • Datetime condition (Datetime filter): Applying filters by dates or date ranges.
  • Relative date resolution: Resolving text like today, yesterday into absolute dates.
  • Datetime rendering to viewers (end-users)

Date-time Aggregation

When aggregating a metric daily, Holistics uses Organization Timezone to determine the time boundary between different days.

For example, given your ecommerce business 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 6am the next day UTC+7. When aggregating daily orders, that order should count towards 2020-01-03 instead of 2020-01-02.

Without Organization Timezone, the daily summary will be based on Database Timezone (UTC), which might not be what you want.

Demonstrated by SQL:

# UTC+07
select
date_trunc('day', 'ordered_at' AT TIME ZONE 'UTC+7')
count(*)
from ...
group by 1
# UTC
select
date_trunc('day', 'ordered_at')
count(*)
from ...
group by 1

Exact datetime conditions (datetime filter)

Similar to the above (datetime aggregation), Organization Timezone is used when you filter your data by particular time, or time range.

For example, given your business in UTC+7, when looking at orders for the entire month of March 2021, the actual condition will apply for the period [2021-03-01T00:00:00+07, 2021-04-01T00:00:00+07)

The generated WHERE condition will look like:

WHERE
ordered_at AT TIME ZONE 'UTC+7' >= '2021-03-01 00:00:00'

Relative date resolutions

Holistics has a concept called 'relative date' where you can type text like yesterday, 1 week ago and Holistics will auto-resolve them into proper date. Organization Timezone will be the default timezone used when such resolution happens.

In the below diagram with Organization Timezone set to UTC+7, today will resolve to day n+1, and yesterday will resolve to day n.

Timezone being displayed to end users

When exploring any data or viewing any dashboard, viewers will be able to see the timezone in which queries of time-based data are run and displayed on the top right corner. The datetime values in the data table will be in the defined timezone.

When exporting an Excel or CSV file, timezone information will be rendered in datetime columns.

Configuring Organization Timezone

You can change Organization Timezone in Admin Settings > Timezone section. By default, the Organization Timezone is set as UTC.

caution

Changing Organization Timezone will affect the underlying SQL queries being generated for every report, thus their results. Please do this carefully and considerately.

Supported Databases

Here are all databases that Holistics supports Timezone Settings up to now:

  • PostgreSQL
  • Redshift
  • BigQuery

Coming soon:

  • Snowflake (Nov 2021)
  • MySQL, Microsoft SQL Server (Dec 2021)
  • Others: Early 2022

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. What should I do if I want to be the beta tester of timezone feature?

If you find it interesting to try our beta version, please contact us via [email protected] However, please take a look at these following important notes first:

2.1. The beta version is only available for Postgresql, Redshift and BigQuery up to now (but we're working on other databases)
Only data that stores on these databases can be applied our new Timezone Settings. Data coming from other databases will be handled as before. For more information on technical differences among these databases, please read our FAQs here.

Thus, if you are using both supported and unsupported databases, it can somehow mess up your dashboard data.

2.2. The beta version requires you to remove all logics that handle timezone issues in Data Modeling

  • If you have already handled the timezone issues by transforming your data models, you need to remove these logics to apply the new timezone setting.
  • However, in the worst case, if you face some issues during the time using our beta version, we need to disable it on your account. It means that you need to re-add your logic to data models afterwards. Therefore, we recommend you to only join the beta test if you do not add any logics to handle timezone issue, or your logics are very simple to re-add.

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

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