Skip to main content

Time Zones

Coming Soon!

We are preparing this feature for release.

Overview​

Most cases of data analysis involve the filtering and aggregation of data based on date & time information. For better or worse, date & time is always local to a time zone, e.g. 1st of June 2023 at 13:00:00 in Europe/Berlin is a different point in time than 1st of June 2023 13:00:00 in America/Los_Angeles. Added to that is the complexity of regional daylight saving time. As such, time zones must be given attention when performing data analysis.

To illustrate the problem, consider an excerpt from a list of shop orders.

image-300 image-300

To aggregate orders by day, each order must be unambiguously attributed to a particular date (year, month and day). Since day boundaries are regional to a time zone, the order totals aggregated by day can be very different in different time zones, for the same underlying data set.

If we assume the order timestamps to be in UTC and compute the order totals in UTC, we get the following for the above excerpt.

image-300 image-300

In contrast, if we assume the order timestamps to be in UTC and compute the order totals for Europe/Berlin, we get the following for the same excerpt.

image-300 image-300

Similar considerations apply to the filtering of data with relative date-time ranges (e.g. "Yesterday"), the sending of scheduled e-mails and more.

Writing SQL queries with correct time zone conversions can be complicated, not least because of database-specific SQL syntax for time zone conversions. As a SQL-based tool, Cluvio assists analysts with extensive built-in functionality for working with time zones, such that the complexity is reduced to a few configuration options.

Cluvio's Time Zones​

Cluvio's support for time zones comes in three layers:

  1. The organization time zone applies to the sending of alerts and e-mail schedules, as well as a dashboard's refresh anchor time. Importantly, the organization time zone does not, on its own, have any effect on the data shown in reports, but it can be configured as a dashboard's time zone (see below). The organization time zone is preset on signup and can be configured in the organization settings.

  2. A data time zone applies to a datasource and defines the time zone of data queried from that source, unless overridden by a dashboard time zone (see below). It is recommended to configure every datasource with a data time zone for predictable results and to enable the use of dashboard time zones. For further details, see Data Time Zone.

  3. A dashboard time zone applies to a dashboard and defines the effective time zone for all reports, filters and result data on that dashboard. A dashboard time zone can either be the organization time zone, a fixed time zone, or user-selectable. For further details, see Dashboard Time Zone.

Common Configurations​

The following sections describe Cluvio time zone configurations for common use-cases.

Implicit Time Zone​

With a small business that operates only in a single time zone, it is common that date & time information is stored in the database without a time zone. In such an environment, it is understood that the date & time information always refers to the "local" time zone of the business. In this situation, we recommend to only set the data time zone on the datasource to the business's local time zone.

Data in UTC​

Another common approach is that all date-time values are stored in UTC but should be visualized in a "local" time zone. This is often done to simplify conversion to other time zones at a later date, if necessary. In this situation, we recommend to set the data time zone on the datasource to UTC and to configure each of your dashboards with a Dashboard Time Zone (which can be the organization time zone).