Skip to main content

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:

  1. 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.

  2. 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.

Choosing the Data 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.

image-600 image-600

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.

note

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 TypeData Type
PostgreSQL / Amazon RedshiftTIMESTAMP
MySQL / MariaDB / Amazon AuroraDATETIME(p)
Google BigQueryDATETIME
PrestoDBTIMESTAMP
TrinoTIMESTAMP
SnowflakeTIMESTAMP_NTZ
Microsoft SQL ServerDATETIME, DATETIME2
OracleTIMESTAMP
VerticaTIMESTAMP
DatabricksTIMESTAMP_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 TypeData Type
PostgreSQL / Amazon RedshiftTIMESTAMP WITH TIME ZONE
MySQL / MariaDB / Amazon AuroraTIMESTAMP
Google BigQueryTIMESTAMP
PrestoDBTIMESTAMP WITH TIME ZONE
TrinoTIMESTAMP WITH TIME ZONE
SnowflakeTIMESTAMP_TZ
Microsoft SQL ServerDATETIMEOFFSET
OracleTIMESTAMP WITH TIME ZONE
VerticaTIMESTAMPTZ
DatabricksTIMESTAMP