TIME_SUB - MaxCompute - Alibaba Cloud Documentation Center

The TIME_SUB function subtracts a specified interval delta from a time value time using a specified time unit date_part.

Syntax

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

-- A standard example.
-- Returns 1233995.
SELECT TIME_SUB(1234000, 5, 'MICROS');

Parameters

time: Required. A time value in microseconds. The value must be of the BIGINT type. You can use the TO_TIME function to construct this value.
In the MaxCompute V1.0 data type edition, if this parameter is of the STRING or DOUBLE type , it is implicitly converted to the BIGINT type for calculation.
delta: Required. The interval to subtract. This parameter is of the BIGINT type. If delta is greater than 0, the interval is subtracted. Otherwise, the interval is added.
In the MaxCompute V1.0 data type edition, if this parameter is of the STRING or DOUBLE type , it is implicitly converted to the BIGINT type for calculation.
date_part: Required. The time unit of the interval. This parameter is a STRING constant. Valid values:
- Hour: HOUR or hh.
- Minute: MINUTE or mi.
- Second: SECOND or ss.
- Millisecond: MILLIS or ff3.
- Microsecond: MICROS or ff6.

Return value

Returns a BIGINT value. The time value is expressed in microseconds. The following rules apply:

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

Examples

Example 1: Subtract an interval in microseconds ('ff6').
```
-- Returns 1233995.
SELECT TIME_SUB(1234000, 5, 'ff6');
```

Example 2: Subtract an interval in hours ('hour').

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

Example 3: Subtract an interval in milliseconds ('millis').

-- 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 the FORMAT_TIME function to convert the returned BIGINT time value to a STRING value in a specified format.

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

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

Example 5: Handle NULL inputs.

-- If any parameter is NULL, NULL is returned.
-- 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

You can use the FORMAT_TIME function to convert the BIGINT time value returned by the TIME_SUB function to a STRING value in a specified format.

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