MaxCompute:TO_TIME

Signature 1: parse a time string

Parses a STRING value using a specified format and returns a BIGINT time value in microseconds.

BIGINT TO_TIME(STRING <value>, STRING <format>)

-- Returns 48273000000.
SELECT TO_TIME('13:24:33', '%H:%M:%S');

Signature 2: construct from hour, minute, and second

Constructs a BIGINT time value in microseconds from separate BIGINT values for hours, minutes, and seconds.

BIGINT TO_TIME(BIGINT <hour>, BIGINT <minute>, BIGINT <second>)

-- Returns 55800000000.
SELECT TO_TIME(15, 30, 0);

Signature 3: extract the time part from a TIMESTAMP

Extracts the time part from a TIMESTAMP value in a specified time zone and returns a BIGINT time value in microseconds. The date and nanosecond parts are discarded.

BIGINT TO_TIME(TIMESTAMP <timestamp>[, STRING <time_zone>])

-- Returns 55801123000.
SELECT TO_TIME(TIMESTAMP '2019-09-01 15:30:01.123');

Signature 4: extract the time part from a TIMESTAMP_NTZ

Extracts the time part from a TIMESTAMP_NTZ (timestamp without time zone) value and returns a BIGINT time value in microseconds. No time zone conversion is applied.

BIGINT TO_TIME(TIMESTAMP_NTZ <timestamp_ntz>)

-- Returns 55801123456.
SELECT TO_TIME(TIMESTAMP_NTZ '2025-09-01 15:30:01.123456');

Format specifiers

The format parameter accepts the following specifiers. Two style families are supported: strftime-style (%H, %M, %S) and Oracle-style (hh, mi, ss).

Valid format examples: hh:mi:ss, HH:MI:SS, %H:%M:%S, %H:%M:%E6S, %H:%M:%E*S.

Signature 1: TO_TIME(STRING, STRING)

Parse time strings with common formats:

-- Standard strftime format.
-- Returns 48273000000.
SELECT TO_TIME('13:24:33', '%H:%M:%S');

-- Oracle-style format.
-- Returns 46860000000.
SELECT TO_TIME('13:01:00', 'hh:mi:ss');

-- Hours and minutes only.
-- Returns 46860000000.
SELECT TO_TIME('13:01', 'hh:mi');

Parse time strings with sub-second precision:

-- Minutes and seconds only.
-- Returns 781000000.
SELECT TO_TIME('13:01', 'mi:ss');

-- ff3 matches milliseconds (3 decimal places).
-- Returns 781123000.
SELECT TO_TIME('13:01.123', 'mi:ss.ff3');

-- %E6S matches microseconds (6 decimal places).
-- Returns 48273123456.
SELECT TO_TIME('13:24:33.123456', '%H:%M:%E6S');

-- %E*S auto-detects fractional precision.
-- Returns 48273123456.
SELECT TO_TIME('13:24:33.123456', '%H:%M:%E*S');

Handle invalid formats and NULL inputs:

-- %h is not a valid specifier; use %H.
-- Returns NULL.
SELECT TO_TIME('13:24:33', '%h:%m:%s');

-- NULL format returns NULL.
-- Returns NULL.
SELECT TO_TIME('13:24:33', NULL);

Signature 2: TO_TIME(BIGINT, BIGINT, BIGINT)

Construct a time value from hour, minute, and second:

-- Returns 55800000000.
SELECT TO_TIME(15, 30, 0);

Calling with only two arguments returns NULL:

-- Only two arguments are passed; does not match the three-argument signature.
-- Returns NULL.
SELECT TO_TIME(15, 30);

NULL inputs return NULL:

-- Returns NULL.
SELECT TO_TIME(15, 30, NULL);

-- Returns NULL.
SELECT TO_TIME(15, NULL, 0);

-- Returns NULL.
SELECT TO_TIME(NULL, 30, 0);

Signature 3: TO_TIME(TIMESTAMP[, STRING])

Extract the time part using the default time zone:

-- Returns 55801123000.
SELECT TO_TIME(TIMESTAMP '2025-09-01 15:30:01.123');

Convert the result to a readable time string using FORMAT_TIME:

-- Returns '15:30:01.123'.
SELECT FORMAT_TIME(TO_TIME(TIMESTAMP '2025-09-01 15:30:01.123'), "%H:%M:%S.ff3");

Specify a target time zone:

-- Explicitly pass Asia/Shanghai.
-- Returns 55801123000.
SELECT TO_TIME(TIMESTAMP '2025-09-01 15:30:01.123', 'Asia/Shanghai');

Cross-time zone conversions:

-- Tokyo is UTC+9, 1 hour ahead of Shanghai (UTC+8).
-- 15:30 in Shanghai -> 16:30 in Tokyo.
-- Returns 59401123456.
SELECT TO_TIME(TIMESTAMP '2025-09-01 15:30:01.123456', 'Asia/Tokyo');

-- Bangkok is UTC+7, 1 hour behind Shanghai.
-- 15:30 in Shanghai -> 14:30 in Bangkok.
-- Returns 52201123456.
SELECT TO_TIME(TIMESTAMP '2025-09-01 15:30:01.123456789', 'Asia/Bangkok');

Signature 4: TO_TIME(TIMESTAMP_NTZ)

TIMESTAMP_NTZ stores a timestamp without time zone information. TO_TIME extracts the time part directly, with no conversion.

-- Returns 55801123456.
SELECT TO_TIME(TIMESTAMP_NTZ '2025-09-01 15:30:01.123456789');