TIME_DIFF - MaxCompute - Alibaba Cloud Documentation Center

The TIME_DIFF function calculates the difference between end_time and start_time and returns the result in the time unit specified by date_part.

Syntax

BIGINT TIME_DIFF(BIGINT <end_time>, BIGINT <start_time>, STRING <date_part>)

-- A standard example.
-- Returns 1106600.
SELECT TIME_DIFF(1230000, 123400, 'ff6');

Parameters

end_time, start_time: Required. A BIGINT value that represents time in microseconds. Use the TO_TIME function to construct this value.
In the MaxCompute V1.0 data type edition, if a parameter is of the STRING or DOUBLE type, it is implicitly converted to the BIGINT type for calculation.
date_part: Required. A STRING constant. Specifies the time unit of the return value. The following values are supported for this parameter:
- 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 following rules apply:

If end_time or start_time is not of the BIGINT type, an error is returned. This behavior applies to MaxCompute V2.0. In V1.0, non-BIGINT types are implicitly cast.
If end_time is less than start_time, a negative value is returned.
If any argument is NULL, the function returens NULL.
If date_part is not a constant, is in an unsupported format, or is of another data type, an error is returned.

Examples

Example 1: Calculate the difference in microseconds ('ff6') between two time values.
```
-- Returns 1106600.
SELECT TIME_DIFF(1230000, 123400, 'ff6');
```
Example 2: Calculate the difference in milliseconds ('ff3') between two time values created with the TO_TIME function.
```
-- Returns -20000.
SELECT TIME_DIFF(TO_TIME(15,39,10), TO_TIME(15,39,30), 'ff3');
```

Example 3: Calculate the difference in seconds ('second') between two time values.

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

-- Returns 10.
SELECT TIME_DIFF(
TO_TIME(TIMESTAMP '2025-03-27 15:39:40'),
TO_TIME(TIMESTAMP '2025-03-27 15:39:30'),
'second'
);

Example 4: Calculate the difference in hours ('hour') between two time values created with the TO_TIME function.

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

-- The actual difference is about 2 hours and 34 minutes. The value is rounded down to 2.
-- Returns 2.
SELECT TIME_DIFF(TO_TIME(12,14,10), TO_TIME(09,39,30), 'hour');

Example 5: An example with a NULL input.

-- If any parameter is NULL, NULL is returned.
-- Returns NULL.
SELECT TIME_DIFF(NULL, TO_TIME(12,0,0), 'minute');
-- Returns NULL.
SELECT TIME_DIFF(TO_TIME(12,0,0), NULL, 'minute');
-- Returns NULL.
SELECT TIME_DIFF(TO_TIME(12,0,0), TO_TIME(12,10,0), NULL);

Related functions

TIME_DIFF is a date and time function. For more information about other date and time functions, see Date and time functions.