Use TO_TIMESTAMP_NTZ to Convert Data Types for Accurate UTC Storage - MaxCompute

TO_TIMESTAMP_NTZ converts date and time data of different input types to a TIMESTAMP_NTZ value. Three overloads are supported: a TIMESTAMP input with a target time zone, a formatted STRING input, and a BIGINT epoch offset.

Syntax

Overload 1: Convert TIMESTAMP to TIMESTAMP_NTZ

TIMESTAMP_NTZ TO_TIMESTAMP_NTZ(TIMESTAMP <timestamp>, STRING <time_zone>)

Overload 2: Convert a formatted STRING to TIMESTAMP_NTZ

TIMESTAMP_NTZ TO_TIMESTAMP_NTZ(STRING <date>, STRING <format> [, STRING <time_zone>])

Overload 3: Convert a BIGINT epoch offset to TIMESTAMP_NTZ

TIMESTAMP_NTZ TO_TIMESTAMP_NTZ(BIGINT <time>, STRING <date_part>)

Parameters

Overload 1 parameters

Parameter	Required	Type	Description
`timestamp`	Yes	TIMESTAMP	The `TIMESTAMP` value to convert.
`time_zone`	Yes	STRING	The target time zone. For supported values, see Time zones.

Overload 2 parameters

Parameter	Required	Type	Description
`date`	Yes	STRING	A string representing the date and time in the format specified by `format`.
`format`	Yes	STRING	The format pattern that describes how `date` is structured. See Format elements below.
`time_zone`	No	STRING	The time zone of the input string. Defaults to UTC if not specified. For supported values, see Time zones.

Overload 3 parameters

Parameter	Required	Type	Description
`time`	Yes	BIGINT	The numeric time offset to convert.
`date_part`	Yes	STRING	The unit of the `time` value. Valid values: `MILLIS` (milliseconds), `MICROS` (microseconds), `NANOS` (nanoseconds).

Format elements

The format parameter in Overload 2 supports the following elements.

Date elements

Element	Description
`%Y` / `yyyy` / `YYYY`	Year with century (e.g., 2025).
`%y`	Year without century (00–99). Leading zero is optional.
`%m` / `mm` / `MM`	Month as a decimal number (01–12).
`%B`	Full month name (e.g., January).
`%b` / `%h`	Abbreviated month name (e.g., Jan).
`%d` / `dd` / `DD`	Day of month as a decimal number (01–31).
`%e`	Day of month without a leading zero (1–31). For example, `2` instead of `02`.
`%F`	Equivalent to `%Y-%m-%d`.

Time elements

Element	Description
`%H` / `hh` / `HH`	Hour on a 24-hour clock (00–23).
`%M` / `mi` / `MI`	Minute as a decimal number (00–59).
`%S` / `ss` / `SS`	Second as a decimal number (00–60).
`%X`	Time in `HH:MM:SS` format.
`%T`	Equivalent to `%H:%M:%S`.
`ff3`	Milliseconds as a three-digit decimal (000–999). Use `%S.ff3` to match `00.123`.
`%Ez`	Numeric time zone offset (`+HH:MM` or `-HH:MM`).
`%E<n>S`	Seconds with `<n>` decimal places. For example, `%E3S` matches `00.123`.
`%E*S`	Seconds with full 6-digit precision. For example, `00.123456`.

Return values

Overload 1 return values

Returns a TIMESTAMP_NTZ value in the specified time zone.

Condition	Result
`timestamp` is NULL or is not a `TIMESTAMP` value	Error
`time_zone` is NULL	NULL

Overload 2 return values

Returns a TIMESTAMP_NTZ value. The session or project time zone does not affect the input or output.

Condition	Result
`date`, `format`, or `time_zone` is NULL	NULL
`date` does not match `format`, or `format` is invalid	NULL

Overload 3 return values

Returns a TIMESTAMP_NTZ value computed by adding time to the epoch 1970-01-01 00:00:00 UTC. The session or project time zone does not affect the input or output.

Condition	Result
`time` is NULL	Error
`date_part` is NULL	NULL

Usage notes

Session time zone and Overload 1: When you pass a TIMESTAMP value, MaxCompute interprets it in the current session or project time zone (odps.sql.timezone) before converting to the target time zone. Changing odps.sql.timezone changes the result.
Session time zone and Overloads 2 and 3: The session or project time zone does not affect the conversion. The time_zone parameter in Overload 2 and the epoch-based arithmetic in Overload 3 are independent of odps.sql.timezone.
NULL handling: Overloads 2 and 3 return NULL for NULL inputs, except time in Overload 3, which raises an error. Overload 1 raises an error when timestamp is NULL.
Missing year in Overload 2: If the format string omits a year element, the year defaults to 1970.

Examples

Convert TIMESTAMP to TIMESTAMP_NTZ

The session time zone is set to Asia/Shanghai. MaxCompute interprets the input TIMESTAMP in that time zone and converts it to the specified target time zone.

SET odps.sql.timezone=Asia/Shanghai;

-- Returns 2025-04-08 08:10:30.123456789
-- Asia/Shanghai (UTC+8) to Etc/GMT (UTC+0): subtract 8 hours
SELECT TO_TIMESTAMP_NTZ(TIMESTAMP '2025-04-08 16:10:30.123456789', 'Etc/GMT');

-- Returns 2025-04-08 17:10:30.123456789
-- Asia/Shanghai (UTC+8) to Asia/Tokyo (UTC+9): add 1 hour
SELECT TO_TIMESTAMP_NTZ(TIMESTAMP '2025-04-08 16:10:30.123456789', 'Asia/Tokyo');

-- Returns 2025-04-08 16:10:30.123456789
-- Same time zone: no offset applied
SELECT TO_TIMESTAMP_NTZ(TIMESTAMP '2025-04-08 16:10:30.123456789', 'Asia/Shanghai');

-- Returns NULL
SELECT TO_TIMESTAMP_NTZ(TIMESTAMP '2025-04-08 16:10:30.123456789', NULL);

Convert a formatted string to TIMESTAMP_NTZ

The session time zone does not affect these queries. The time_zone parameter in the last two examples controls how the input string is interpreted.

SET odps.sql.timezone=Asia/Shanghai;

-- Returns 2017-11-11 00:00:00.123
SELECT TO_TIMESTAMP_NTZ('17-11-11 00:00:00.123', '%y-mm-dd hh:mi:ss.ff3');

-- Returns 2017-11-11 00:00:00.123
-- %y treats '0017' as year 17, then adds the century prefix: 2017
SELECT TO_TIMESTAMP_NTZ('0017-11-11 00:00:00.123', '%y-mm-dd hh:mi:ss.ff3');

-- Returns 1970-11-11 00:00:00.123
-- Year element is missing; defaults to 1970
SELECT TO_TIMESTAMP_NTZ('11-11 00:00:00.123', 'mm-dd hh:mi:ss.ff3');

-- Returns 2025-05-18 15:30:00
SELECT TO_TIMESTAMP_NTZ('2025-05-18 15:30:00 MAY', '%F %X %B');

-- Returns 2025-05-18 15:30:00
SELECT TO_TIMESTAMP_NTZ('2025-05-18 15:30:00 may', '%F %X %h');

-- Returns 2025-05-18 15:30:00
SELECT TO_TIMESTAMP_NTZ('2025-05-18 15:30:00 may', '%F %T %b');

-- Returns 2025-05-18 10:20:00
SELECT TO_TIMESTAMP_NTZ('20250518 10:20:00', 'yyyymm%e %T');

-- Returns 2025-05-18 02:30:00
-- Input includes a +07:00 offset; converted to UTC
SELECT TO_TIMESTAMP_NTZ('2025-05-18 09:30:00+07:00', '%F %H:%M:%S%Ez');

-- Returns 2025-04-08 16:10:30.123456
-- time_zone is Etc/GMT (UTC+0); no offset adjustment
SELECT TO_TIMESTAMP_NTZ('2025-04-08 16:10:30.123456', '%Y-%m-%d %H:%M:%E*S', 'Etc/GMT');

-- Returns 2025-04-08 08:10:30.123
-- Input is in Asia/Shanghai (UTC+8); converted to UTC
SELECT TO_TIMESTAMP_NTZ('2025-04-08 16:10:30.123', '%Y-%m-%d %H:%M:%E3S', 'Asia/Shanghai');

Convert a BIGINT epoch offset to TIMESTAMP_NTZ

The function adds the time value to the epoch 1970-01-01 00:00:00 UTC. The date_part parameter controls the unit. The following examples use the same BIGINT value to show how the unit affects the result.

SET odps.sql.timezone=Asia/Shanghai;

-- Same input value (99999341234), three different units:

-- Returns 1973-03-03 09:35:41.234  (milliseconds)
SELECT TO_TIMESTAMP_NTZ(99999341234, 'MILLIS');

-- Returns 1970-01-02 03:46:39.341234  (microseconds)
SELECT TO_TIMESTAMP_NTZ(99999341234, 'MICROS');

-- Returns 1970-01-01 00:01:39.999341234  (nanoseconds)
SELECT TO_TIMESTAMP_NTZ(99999341234, 'NANOS');

-- Negative values represent offsets before the epoch
-- Returns 1966-10-31 14:24:18.766
SELECT TO_TIMESTAMP_NTZ(-99999341234, 'MILLIS');

-- Returns NULL
SELECT TO_TIMESTAMP_NTZ(99999341234, NULL);

MaxCompute:TO_TIMESTAMP_NTZ