Convert a UTC timestamp to a specified time zone using FROM_UTC_TIMESTAMP - MaxCompute

Converts a UTC timestamp to a specified time zone and returns a TIMESTAMP value.

Important

Use TO_TIMESTAMP_NTZ instead of FROM_UTC_TIMESTAMP wherever possible. FROM_UTC_TIMESTAMP applies a session or project time zone offset when the input is BIGINT, which can produce unexpected results.

Syntax

TIMESTAMP FROM_UTC_TIMESTAMP(BIGINT|STRING|DATETIME|TIMESTAMP|TIMESTAMP_NTZ <timestamp>, STRING <time_zone>)

Parameters

Parameter	Type	Required	Description
`timestamp`	BIGINT, STRING, DATETIME, TIMESTAMP, or TIMESTAMP_NTZ	Yes	The UTC timestamp to convert. See the input type rules below.
`time_zone`	STRING	Yes	The target time zone. For example, `Asia/Shanghai`, `Etc/GMT`, or `PST`.

Input type rules for `timestamp`:

BIGINT — Treated as milliseconds. The function adds the value to the epoch 1970-01-01 00:00:00 UTC, then applies the current session or project time zone offset to determine the input timestamp. The output is affected by the session or project time zone setting.
STRING — Must be in yyyy-mm-dd or yyyy-mm-dd hh:mi:ss format. Values in other formats return NULL.
DATETIME, TIMESTAMP, TIMESTAMP_NTZ — Converted directly from UTC to the target time zone. The session or project time zone setting does not affect the input or output.

Return value

Returns a TIMESTAMP value in the specified time zone.

Condition	Result
`timestamp` is not BIGINT, STRING, DATETIME, TIMESTAMP, or TIMESTAMP_NTZ	Error
`timestamp` is a STRING not matching the required format	NULL
`timestamp` is NULL	Error
`time_zone` is NULL	NULL

Usage notes

Session time zone affects BIGINT input only. When timestamp is BIGINT, the result changes with the odps.sql.timezone setting. For all other input types, the session or project time zone has no effect.
Instance tunnel results may differ. If you run this function through a MaxCompute client with use_instance_tunnel set to true, the result displayed in odpscmd may differ from the result in logview. The logview result is authoritative.

Examples

BIGINT input: session time zone is Asia/Shanghai

The input is 0 milliseconds (epoch time). The function adds 0 ms to 1970-01-01 00:00:00 UTC, then applies the Asia/Shanghai offset (+8 hours) to get the input timestamp 1970-01-01 08:00:00. Converting that to Asia/Shanghai returns 1970-01-01 16:00:00.

SET odps.sql.timezone = Asia/Shanghai;

SELECT  FROM_UTC_TIMESTAMP(0, 'Asia/Shanghai') AS timestamp1
       ,FROM_UTC_TIMESTAMP(1230000,'Etc/GMT')  AS timestamp2
       ,FROM_UTC_TIMESTAMP(-1230000,'Etc/GMT') AS timestamp3;

Result:

+---------------------+---------------------+---------------------+
| timestamp1          | timestamp2          | timestamp3          |
+---------------------+---------------------+---------------------+
| 1970-01-01 16:00:00 | 1970-01-01 08:20:30 | 1970-01-01 07:39:30 |
+---------------------+---------------------+---------------------+

BIGINT input: session time zone is Etc/GMT

Same query with odps.sql.timezone set to Etc/GMT. The session offset changes, so the results differ.

SET odps.sql.timezone = Etc/GMT;

SELECT  FROM_UTC_TIMESTAMP(0, 'Asia/Shanghai') AS timestamp1
       ,FROM_UTC_TIMESTAMP(1230000,'Etc/GMT')  AS timestamp2
       ,FROM_UTC_TIMESTAMP(-1230000,'Etc/GMT') AS timestamp3;

Result:

+---------------------+---------------------+---------------------+
| timestamp1          | timestamp2          | timestamp3          |
+---------------------+---------------------+---------------------+
| 1970-01-01 08:00:00 | 1970-01-01 00:20:30 | 1969-12-31 23:39:30 |
+---------------------+---------------------+---------------------+

Non-BIGINT input: session time zone has no effect

For STRING, DATETIME, TIMESTAMP, and TIMESTAMP_NTZ inputs, the function converts the value directly from UTC to the target time zone. The odps.sql.timezone setting is irrelevant.

-- Session time zone is Asia/Shanghai, but it does not affect the output.
SET odps.sql.timezone = Asia/Shanghai;

-- STRING (yyyy-mm-dd): returns 2025-08-31 09:00:00
SELECT FROM_UTC_TIMESTAMP('2025-08-31', 'Asia/Seoul');

-- STRING (yyyy-mm-dd hh:mi:ss): returns 2025-03-05 23:30:15
SELECT FROM_UTC_TIMESTAMP('2025-03-05 15:30:15', 'Asia/Shanghai');

-- STRING with sub-second precision: returns 2025-03-05 08:00:00.123456789
SELECT FROM_UTC_TIMESTAMP('2025-03-05 00:00:00.123456789', 'Asia/Shanghai');

-- DATETIME: returns 2025-03-04 16:00:00
SELECT FROM_UTC_TIMESTAMP(DATETIME '2025-03-05 00:00:00', 'PST');

-- TIMESTAMP: returns 2025-03-06 00:30:15.123
SELECT FROM_UTC_TIMESTAMP(TIMESTAMP '2025-03-05 15:30:15.123', 'Asia/Seoul');

-- TIMESTAMP_NTZ: returns 2025-03-05 23:30:15.123
SELECT FROM_UTC_TIMESTAMP(TIMESTAMP_NTZ '2025-03-05 15:30:15.123', 'Asia/Shanghai');

Related functions

FROM_UTC_TIMESTAMP is a date function. For other date calculation and conversion functions, see Date functions.