Data Time Zone
Overview
There are two types of date-time values returned by SQL queries, time-zone-agnostic and time-zone-aware. The data time zone is a configuration of a datasource that has two important functions:
-
It tells Cluvio that any time-zone-agnostic date-time values in query results can be assumed to be in the data time zone. For example, if you have a PostgreSQL datasource containing table columns of type
TIMESTAMP (WITHOUT TIME ZONE)and you configure your PostgreSQL datasource with a data time zone of UTC, Cluvio interprets the values in these columns to be in UTC and they will be shown in UTC on any dashboard unless a different dashboard time zone is set, in which case Cluvio converts the values from UTC to the dashboard time zone. -
It tells Cluvio in what time zone any date-time values returned by the datasource should be shown on a dashboard, unless overruled by a dashboard time zone.
The data time zone of a datasource should always be set to the time zone
that you wish Cluvio to assume for date-time values that have no time zone
information. If your database only contains time-zone-aware date-time values
(e.g. TIMESTAMP WITH TIME ZONE in PostgreSQL), set the data time zone to
UTC or your preferred default time zone for the data on your dashboards.
With a data time zone configured on a datasource, all date-time values shown on a dashboard have a predictable time zone interpretation: Either the dashboard time zone or the data time zone (if no dashboard time zone is configured).
Configuration
To configure a data time zone for a datasource, open the datasource edit dialog and choose the correct time zone from the list of time zones.
We recommend to configure all pre-existing datasources with a data time zone. All newly created datasources must have a data time zone.
Data Types
Time-Zone-Agnostic Data
Time-zone-agnostic data types have the property that the database or connection
time zone configuration has no influence on the storage & retrieval of the
values. Common time-zone-agnostic data types are TIMESTAMP in PostgreSQL
or DATETIME in MySQL. DATE-only and TIME-only types are always
time-zone-agnostic.
Date-time functions of some databases return values in the session or
database time zone as a time-zone-agnostic type. For example, CURRENT_TIMESTAMP
in MySQL returns a DATETIME value representing the current date & time in the
current session time zone (which is UTC in Cluvio by default). Similarly,
CURRENT_TIMESTAMP in Microsoft SQL Server returns a DATETIME value that
represents the current date & time in the database time zone (as there is
no concept of session time zone in Microsoft SQL Server).
The following table lists database-specific data types that are time-zone-agnostic.
| Database Type | Data Type |
|---|---|
| PostgreSQL / Amazon Redshift | TIMESTAMP |
| MySQL / MariaDB / Amazon Aurora | DATETIME(p) |
| Google BigQuery | DATETIME |
| PrestoDB | TIMESTAMP |
| Trino | TIMESTAMP |
| Snowflake | TIMESTAMP_NTZ |
| Microsoft SQL Server | DATETIME, DATETIME2 |
| Oracle | TIMESTAMP |
| Vertica | TIMESTAMP |
| Databricks | TIMESTAMP_NTZ |
Time-Zone-Aware Data
Time-zone-aware data types can be affected by the database or connection time zone
configuration both on storage and retrieval. Common time-zone-aware data types
are TIMESTAMP WITH TIME ZONE in PostgreSQL or DATETIMEOFFSET in Microsoft
SQL Server.
The following table lists database-specific data types that are time-zone-aware.
| Database Type | Data Type |
|---|---|
| PostgreSQL / Amazon Redshift | TIMESTAMP WITH TIME ZONE |
| MySQL / MariaDB / Amazon Aurora | TIMESTAMP |
| Google BigQuery | TIMESTAMP |
| PrestoDB | TIMESTAMP WITH TIME ZONE |
| Trino | TIMESTAMP WITH TIME ZONE |
| Snowflake | TIMESTAMP_TZ |
| Microsoft SQL Server | DATETIMEOFFSET |
| Oracle | TIMESTAMP WITH TIME ZONE |
| Vertica | TIMESTAMPTZ |
| Databricks | TIMESTAMP |