Realtime Compute for Apache Flink:TO_TIMESTAMP_TZ

Syntax

TIMESTAMP TO_TIMESTAMP_TZ(VARCHAR date, VARCHAR timezone)
TIMESTAMP TO_TIMESTAMP_TZ(VARCHAR date, VARCHAR format, VARCHAR timezone)

The first version parses date using the default format yyyy-MM-dd HH:mm:ss and interprets it in the specified timezone.

The second version lets you specify a custom format for the input date string before applying the timezone conversion.

Input parameters

Usage notes

• Use IANA time zone names for the timezone parameter. If a non-standard value is provided, the parameter defaults to UTC.

• The format parameter applies only to the second overload. The default format is yyyy-MM-dd HH:mm:ss.

Example

The following example assumes the Flink runtime environment runs in the Asia/Shanghai time zone (UTC+8).

Test data — Table T1

Test statement

SELECT
    TO_TIMESTAMP_TZ(timestamp1, timezone1) AS var1,          -- returns 1970-01-01 08:00:00
    TO_TIMESTAMP_TZ(timestamp2, format2, timezone2) AS var2,  -- returns 1970-01-01 00:00:00
    TO_TIMESTAMP_TZ(timestamp3, format3, timezone3) AS var3   -- returns 1970-01-01 13:00:00
FROM T1;

Test result

Result explanation

• var1: timezone1 is /, which is a non-standard value. The function falls back to UTC. The Flink runtime in Asia/Shanghai (UTC+8) displays the UTC epoch 1970-01-01 00:00:00 as 1970-01-01 08:00:00.

• var2: timezone2 is Asia/Shanghai, which matches the Flink runtime time zone. The input is already in Asia/Shanghai time, so the TIMESTAMP is unchanged: 1970-01-01 00:00:00.

• var3: timezone3 is America/New_York (UTC-5). Midnight in New York equals 1970-01-01 05:00:00 UTC, which the Flink runtime in Asia/Shanghai (UTC+8) displays as 1970-01-01 13:00:00.