Use DATE_FORMAT_TZ to convert a timestamp to a string with a specified format and time zone - Realtime Compute for Apache Flink

DATE_FORMAT_TZ converts a TIMESTAMP value to a formatted string in the specified time zone.

Syntax

VARCHAR DATE_FORMAT_TZ(TIMESTAMP timestamp, VARCHAR format, VARCHAR timezone)
VARCHAR DATE_FORMAT_TZ(TIMESTAMP timestamp, VARCHAR timezone)

Both forms return a VARCHAR string.

Parameter	Data type	Description
`timestamp`	TIMESTAMP	The timestamp to convert.
`format`	VARCHAR	The output date format. Example: `yyyy-MM-dd HH:mm:ss`.
`timezone`	VARCHAR	The target time zone. Example: `Asia/Shanghai`.

The function shifts the timestamp to the target time zone. For example, Unix epoch 0 in Asia/Shanghai (UTC+8) becomes 1970-01-01 08:00:00.

Test data

timestamp1 (TIMESTAMP)	format1 (VARCHAR)	timezone1 (VARCHAR)
0	yyyy-MM-dd HH:mm:ss	Asia/Shanghai

Test statement

SELECT
   DATE_FORMAT_TZ(timestamp1, format1, timezone1) AS var1,
   DATE_FORMAT_TZ(timestamp1, timezone1) AS var2
FROM T1;

Test result

var1 (VARCHAR)	var2 (VARCHAR)
1970-01-01 08:00:00	1970-01-01 08:00:00

Unix epoch 0 is 1970-01-01 00:00:00 UTC. Shifted to Asia/Shanghai (UTC+8), the result is 1970-01-01 08:00:00.