TIME_ADD - MaxCompute - Alibaba Cloud Documentation Center

Adds an interval to a time value based on the specified time unit.

Syntax

BIGINT TIME_ADD(BIGINT <time>, BIGINT <delta>, STRING <date_part>)

Example:

-- Returns 5001234.
SELECT TIME_ADD(1234, 5, 'second');

Parameters

Parameter	Type	Required	Description
`time`	BIGINT	Yes	A time value in microseconds. Use TO_TIME to construct this value. Must be BIGINT — passing any other type returns an error. In the MaxCompute 1.0 data type edition, STRING and DOUBLE values are implicitly converted to BIGINT before calculation.
`delta`	BIGINT	Yes	The interval to add. A positive value adds to the time; a negative value subtracts from it. In the MaxCompute 1.0 data type edition, STRING and DOUBLE values are implicitly converted to BIGINT before calculation.
`date_part`	STRING constant	Yes	The time unit. Must be a string constant — a non-constant value, unsupported format, or wrong data type returns an error.

Supported `date_part` values:

Time unit	Values
Hour	`HOUR`, `hh`
Minute	`MINUTE`, `mi`
Second	`SECOND`, `ss`
Millisecond	`MILLIS`, `ff3`
Microsecond	`MICROS`, `ff6`

Return value

Returns a BIGINT value representing a time value in microseconds.

If time is not a BIGINT, an error is returned.
If date_part is not a constant, is in an unsupported format, or is another data type, an error is returned.
If time, delta, or date_part is NULL, returns NULL.

Examples

Add microseconds

-- Returns 1239.
SELECT TIME_ADD(1234, 5, 'ff6');

Add milliseconds

-- Enable MaxCompute V2.0 data types.
SET odps.sql.type.system.odps2 = true;

-- Returns 56680123456.
SELECT TIME_ADD(TO_TIME(TIMESTAMP_NTZ '2025-03-27 15:39:40.123456'), 5, 'ff3');

Add seconds

-- Returns 56385000000.
SELECT TIME_ADD(TO_TIME(15,39,40), 5, 'second');

Add hours

-- Enable MaxCompute V2.0 data types.
SET odps.sql.type.system.odps2 = true;

-- Returns 74380000000.
SELECT TIME_ADD(TO_TIME(TIMESTAMP '2025-03-27 15:39:40'), 5, 'hh');

Format the result as a human-readable time string

Use FORMAT_TIME to convert the returned microsecond value to a readable format.

-- Returns 15:44:40.
SELECT FORMAT_TIME(TIME_ADD(TO_TIME('15:39:40', '%H:%M:%S'), 5, 'mi'), '%H:%M:%S');

Handle NULL inputs

If any parameter is NULL, the function returns NULL.

-- Returns NULL.
SELECT TIME_ADD(NULL, 5, 'second');

-- Returns NULL.
SELECT TIME_ADD(TO_TIME('15:39:40', '%H:%M:%S'), NULL, 'second');

-- Returns NULL.
SELECT TIME_ADD(TO_TIME('15:39:40', '%H:%M:%S'), 5, NULL);

-- Returns NULL.
SELECT TIME_ADD(NULL, NULL, NULL);

Related functions

For more functions that perform time calculations and conversions, see Date and time functions.