FROM_UTC_TIMESTAMP command format, parameters, and return value - MaxCompute

The FROM_UTC_TIMESTAMP function converts a timestamp from Coordinated Universal Time (UTC) to a specified time zone.

Usage notes

To ensure clear and consistent time zone handling, use the TO_TIMESTAMP_NTZ function. This helps avoid unexpected results that can occur due to the way the FROM_UTC_TIMESTAMP function processes time zones.

Syntax

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

Parameters

timestamp: Required. The timestamp to convert. This parameter supports the BIGINT, STRING, DATETIME, TIMESTAMP, and TIMESTAMP_NTZ data types.
- If the input is a STRING, the format must be yyyy-mm-dd or yyyy-mm-dd hh:mi:ss.
- If the input is a BIGINT value in milliseconds, the function adds this value to the epoch time 1970-01-01 00:00:00 UTC. Then, an offset is applied based on the current session or project time zone to determine the input timestamp.
time_zone: Required. A STRING that specifies the target time zone.

Return value

Returns a TIMESTAMP value in the specified time zone. The following rules apply:

If timestamp is not a BIGINT, STRING, DATETIME, TIMESTAMP, or TIMESTAMP_NTZ type, an error is returned.
If timestamp is a STRING value that does not conform to the required format, NULL is returned.
If timestamp is NULL, an error is returned.
If time_zone is NULL, NULL is returned.

Examples

Example 1: If the input parameter is a BIGINT, the function adds the millisecond value to the epoch time 1970-01-01 00:00:00 UTC. Then, an offset is applied based on the current session or project time zone to determine the input timestamp.

-- Set the session/project time zone to Asia/Shanghai.
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;
-- The following result is returned:
+---------------------+---------------------+---------------------+
| timestamp1          | timestamp2          | timestamp3          |
+---------------------+---------------------+---------------------+
| 1970-01-01 16:00:00 | 1970-01-01 08:20:30 | 1970-01-01 07:39:30 |
+---------------------+---------------------+---------------------+


-- Set the session/project time zone to Etc/GMT.
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;
-- The following result is returned:
+---------------------+---------------------+---------------------+
| timestamp1          | timestamp2          | timestamp3          |
+---------------------+---------------------+---------------------+
| 1970-01-01 08:00:00 | 1970-01-01 00:20:30 | 1969-12-31 23:39:30 |
+---------------------+---------------------+---------------------+

Example 2: If the input parameter is another supported type, the function converts the timestamp from UTC to the specified time zone. In this case, the input and output timestamps are not affected by the session or project time zone.

-- The session/project time zone setting does not affect the input or output timestamps.
SET odps.sql.timezone = Asia/Shanghai;

-- Returns 2025-08-31 09:00:00
SELECT FROM_UTC_TIMESTAMP('2025-08-31', 'Asia/Seoul');

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

-- Returns 2025-03-05 08:00:00.123456789
SELECT FROM_UTC_TIMESTAMP('2025-03-05 00:00:00.123456789','Asia/Shanghai');

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

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

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

Note

If you run this function on a MaxCompute client with `use_instance_tunnel` set to `true`, the results displayed in `odpscmd` and `logview` may be inconsistent. The result in `logview` prevails.

Related functions

FROM_UTC_TIMESTAMP is a date function. For more information about functions related to date calculations and conversions, see Date functions.