TO_UTC_TIMESTAMP Function Syntax and UTC Conversion Logic - MaxCompute

Converts a timestamp in a specified time zone to a Coordinated Universal Time (UTC) timestamp. For example, TO_UTC_TIMESTAMP('2025-03-05 15:30:15', 'Asia/Shanghai') returns 2025-03-05 07:30:15.

Usage notes

Use TO_TIMESTAMP_NTZ for time zone handling when consistency is critical. TO_UTC_TIMESTAMP may return unexpected results in certain time zone configurations.

BIGINT inputs are session-aware. When the input is a BIGINT (milliseconds since epoch), the function first applies the current session or project time zone to interpret the millisecond value before converting to UTC. All other input types (STRING, DATETIME, TIMESTAMP, TIMESTAMP_NTZ) are not affected by the session or project time zone.

For the time_zone parameter, use IANA time zone names such as Asia/Shanghai, America/New_York, or Etc/GMT. Time zone abbreviations like PST are supported, but IANA names are preferred because abbreviations can be ambiguous. For the full list of supported time zones, see Time zones.

Syntax

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

Parameters

timestamp: Required. The timestamp to convert. Accepts BIGINT, STRING, DATETIME, TIMESTAMP, and TIMESTAMP_NTZ.
- STRING: The date format must be yyyy-mm-dd or yyyy-mm-dd hh:mi:ss.
- BIGINT: The value represents milliseconds. The function adds this value to 1970-01-01 00:00:00 UTC, then applies an offset based on the current session or project time zone to determine the input timestamp. See Example 1: BIGINT input.
time_zone: Required. A STRING specifying the time zone for the input timestamp.

Return value

Returns a TIMESTAMP representing the UTC equivalent of the input.

Condition	Result
`timestamp` is an unsupported type	Error
`timestamp` is a STRING in an invalid format	NULL
`timestamp` is NULL	Error — the function requires a timestamp to determine a valid conversion baseline
`time_zone` is NULL	NULL — a NULL time zone is treated as an absent argument

Examples

Example 1: BIGINT input — session time zone aware

For BIGINT inputs, the session or project time zone determines how the millisecond value is interpreted before conversion to UTC.

-- Session time zone: Asia/Shanghai (UTC+8)
SET odps.sql.timezone = Asia/Shanghai;
SELECT  TO_UTC_TIMESTAMP(0, 'Asia/Shanghai') AS TIMESTAMP1
        ,TO_UTC_TIMESTAMP(1230000,'Etc/GMT') AS TIMESTAMP2
        ,TO_UTC_TIMESTAMP(-1230000,'Etc/GMT') AS TIMESTAMP3;
-- Result:
+---------------------+---------------------+---------------------+
| timestamp1          | timestamp2          | timestamp3          |
+---------------------+---------------------+---------------------+
| 1970-01-01 00:00:00 | 1970-01-01 08:20:30 | 1970-01-01 07:39:30 |
+---------------------+---------------------+---------------------+

-- Session time zone: Etc/GMT (UTC+0) — same inputs, different results
SET odps.sql.timezone = Etc/GMT;
SELECT  TO_UTC_TIMESTAMP(0, 'Asia/Shanghai') AS TIMESTAMP1
        ,TO_UTC_TIMESTAMP(1230000,'Etc/GMT') AS TIMESTAMP2
        ,TO_UTC_TIMESTAMP(-1230000,'Etc/GMT') AS TIMESTAMP3;
-- Result:
+---------------------+---------------------+---------------------+
| timestamp1          | timestamp2          | timestamp3          |
+---------------------+---------------------+---------------------+
| 1969-12-31 16:00:00 | 1970-01-01 00:20:30 | 1969-12-31 23:39:30 |
+---------------------+---------------------+---------------------+

If you run queries on a MaxCompute client with use_instance_tunnel set to true, the results in odpscmd and Logview may differ. Use the result from Logview.

Example 2: Non-BIGINT input — session time zone has no effect

For STRING, DATETIME, TIMESTAMP, and TIMESTAMP_NTZ inputs, the session or project time zone has no effect on either the input or the output.

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

-- Returns 2025-08-30 15:00:00
SELECT TO_UTC_TIMESTAMP('2025-08-31', 'Asia/Seoul');

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

-- Returns 2025-03-04 16:00:00.123456789
SELECT TO_UTC_TIMESTAMP('2025-03-05 00:00:00.123456789','Asia/Shanghai');

-- Returns 2025-03-05 08:00:00
SELECT TO_UTC_TIMESTAMP(DATETIME '2025-03-05 00:00:00','PST');

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

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

Related functions

TO_UTC_TIMESTAMP is a date and time function. For more information about date calculations and conversions, see Date functions.