Timezone Settings

(This feature is currently in active development. Please reach out if you want to become beta tester)

Introduction

There are 2 problems you usually face when working with Timezone:

  1. Process datetime with no timezone information: Sometimes datetime fields stored in your database don't have timezone information, thus Holistics does not know how to offset the time correctly.
  2. Change the processed timezone: 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 timezones. Understanding different timezone settings correctly is important for you to get the right results when working with your data.

Database Timezone

In your database, sometimes time columns doesn't contain the timezone part. When such case happens, Holistics uses this setting to offset the time correctly.

Your database often operates under a default timezone. Holistics needs to know your database timezone in advance so we can offset the time correctly.

For example, the following column ordered_at (type datetime) only contains date + time data. If you set your Database Timezone as "UTC", Holistics will treat the column as datetime in UTC.

Depending on your database (PostgreSQL, Snowflake, etc), those time fields with no timezone usually have type timestamp without time zone or datetime.

Configuring Database Timezone

Database Timezone is configured per data source in the Data Source dialog when you connect to your database. You can also change this setting later on.

caution

Setting this value to anything other than the time zone that your database is in may lead to unexpected results.

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.