Skip to main content

Built-in Time Zone

The built-in time zone transform tz performs a time zone conversion to the dashboard time zone in SQL.

note

All values SELECTed by a query of type TIMESTAMP, TIMESTAMP WITH TIME ZONE, DATETIME, etc. are automatically converted to the configured dashboard time zone by Cluvio. You must not use the tz transform for that purpose.

This transform should only be used when the resulting expression does not appear in the SELECT clause of a query or is not a timestamp type. For example, the tz transform can be used to construct text expressions that contain a timestamp in the dashboard time zone.

caution

Do not use the tz transform on result timestamps without a subsequent type conversion. For example, SELECT {my_timestamp_column:tz} ... will produce incorrect results. The tz transform always returns a TIMESTAMP WITHOUT TIME ZONE (or equivalent) which Cluvio then incorrectly assumes to represent a timestamp in the datasource data time zone, performing another conversion to the dashboard time zone.

Usage​

In PostgreSQL, the tz transform could be used as follows to embed the current timestamp in the dashboard time zone in a text value.

SELECT
// highlight-next-line
  'The current time is ' || ({CURRENT_TIMESTAMP:tz})::text
Syntax
{[<expr>]:tz}

The generated SQL for the tz transform is database-specific and an expression of type TIMESTAMP WITHOUT TIME ZONE (or equivalent). Below is the generated SQL for the above PostgreSQL query when the dashboard time zone is configured as Europe/Berlin while the datasource data time zone is UTC.

SET TIME ZONE 'UTC'; -- the datasource data time zone
SELECT 'The current time is ' || (CURRENT_TIMESTAMP AT TIME ZONE 'Europe/Berlin')::text; -- the dashboard time zone

Note that the datasource data time zone (which is also the session time zone) in this example has no effect, as we are returning a text value.

In contrast, the following is incorrect as it results in a double time zone conversion:

-- DO NOT DO THIS!
SELECT {CURRENT_TIMESTAMP:tz}

The resulting SQL with dashboard time zone Europe/Berlin and data time zone UTC would be:

-- DO NOT DO THIS!
SET TIME ZONE 'UTC';
SELECT CURRENT_TIMESTAMP AT TIME ZONE 'Europe/Berlin';

Because the expression CURRENT_TIMESTAMP AT TIME ZONE 'Europe/Berlin' has type TIMESTAMP WITHOUT TIME ZONE, Cluvio will think this value is to be assumed to be in UTC (the data time zone) and perform another conversion to Europe/Berlin.

Configuration​

The built-in timezone transform does not have its own configuratiom, its behavior is configured via:

Value Selectors​

You can make use of the dashboard time zone selection as a value parameter with the following options:

  • {tz} The name of the selected time zone (e.g. Europe/Berlin) or NULL if no dashboard time zone is configured.

    SELECT {tz} -- the name of the selected time zone