TIME_SUB

Syntax

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

Example:

-- Returns 1233995 (5 microseconds before 1,234,000 μs).
SELECT TIME_SUB(1234000, 5, 'MICROS');

Parameters

time

Required. A time value in microseconds, of the BIGINT type. Use TO_TIME to construct this value.

In the Data type version 1.0, if this parameter is of the STRING or DOUBLE type, it is implicitly converted to BIGINT for calculation.

delta

Required. The interval to subtract, of the BIGINT type. If delta is greater than 0, the interval is subtracted. Otherwise, the interval is added.

In the Data type version 1.0, if this parameter is of the STRING or DOUBLE type, it is implicitly converted to BIGINT for calculation.

date_part

Required. The time unit of the interval. Must be a STRING constant. Valid values:

Return value

Returns a BIGINT value representing a time in microseconds.

• If time is not of the BIGINT type, an error is returned.

• If date_part is not a constant, is in an unsupported format, or is of a non-STRING type, an error is returned.

• If time, delta, or date_part is NULL, NULL is returned.

Examples

Example 1: Subtract 5 microseconds using the ff6 alias.

-- Returns 1233995 (5 microseconds before 1,234,000 μs).
SELECT TIME_SUB(1234000, 5, 'ff6');

Example 2: Subtract 5 hours from a time constructed with TO_TIME.

-- Returns 38380000000 (equivalent to 10:39:40).
SELECT TIME_SUB(TO_TIME('15:39:40','%H:%M:%S'), 5, 'hour');

Example 3: Subtract 5 milliseconds using the MaxCompute V2.0 data type edition.

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

-- Returns 56379995000.
SELECT TIME_SUB(TO_TIME(TIMESTAMP '2025-03-27 15:39:40'), 5, 'millis');

Example 4: Use a negative delta to add an interval, and format the result with FORMAT_TIME.

-- Returns 15:39:45 (delta = -5 adds 5 seconds).
SELECT FORMAT_TIME(TIME_SUB(TO_TIME(15,39,40), -5, 'second'),'%H:%M:%S');

-- Enable the MaxCompute V2.0 data type edition.
SET odps.sql.type.system.odps2 = true;
-- Returns 15:44:40.123 (delta = -5 adds 5 minutes).
SELECT FORMAT_TIME(TIME_SUB(TO_TIME(TIMESTAMP_NTZ '2025-03-27 15:39:40.123456'),-5,'minute'),'%H:%M:%S.ff3');

Example 5: NULL propagation — any NULL parameter returns NULL.

-- Returns NULL.
SELECT TIME_SUB(NULL, 10, 'SECOND');

-- Returns NULL.
SELECT TIME_SUB(1234000, NULL, 'ff6');

-- Returns NULL.
SELECT TIME_SUB(1234000, 5, NULL);

Related functions

Use FORMAT_TIME to convert the BIGINT return value to a formatted STRING.

TIME_SUB is a time function. For more information about time calculation and conversion functions, see Date and time functions.