(This feature is currently in active development. Please reach out if you want to become beta tester)
There are 2 problems you usually face when working with Timezone:
- 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.
- 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.
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
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
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.
Setting this value to anything other than the time zone that your database is in may lead to unexpected results.
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
yesterdayinto absolute dates.
- Datetime rendering to viewers (end-users)
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
Without Organization Timezone, the daily summary will be based on Database Timezone (UTC), which might not be what you want.
Demonstrated by SQL:
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
The generated WHERE condition will look like:
Relative date resolutions
Holistics has a concept called 'relative date' where you can type text like
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
yesterday will resolve to day
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.
Changing Organization Timezone will affect the underlying SQL queries being generated for every report, thus their results. Please do this carefully and considerately.