TIME_DIFF - MaxCompute - Alibaba Cloud Documentation Center

TIME_DIFF calculates the difference between two time values and returns the result as a BIGINT in the time unit you specify.

Syntax

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

Quick example:

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

Parameters

end_time, start_time

Required. BIGINT values representing time in microseconds. Use TO_TIME to construct these values.

In the MaxCompute V1.0 data type edition, STRING and DOUBLE inputs are implicitly converted to BIGINT. In MaxCompute V2.0, non-BIGINT inputs return an error.

date_part

Required. A STRING constant specifying the time unit of the return value.

Unit	Accepted 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 representing the difference in the specified unit. The following rules apply:

If end_time or start_time is not BIGINT, an error is returned (MaxCompute V2.0 behavior; V1.0 implicitly casts).
If end_time is less than start_time, a negative value is returned.
If any argument is NULL, NULL is returned.
If date_part is not a constant, is in an unsupported format, or is not a STRING, an error is returned.

Usage notes

Truncation, not rounding

TIME_DIFF truncates the result to the specified unit — it does not round. For example, a difference of 2 hours and 34 minutes returns 2 when date_part is 'hour', not 3.

Examples

Compare one pair of times across multiple units

The following examples use the same two time values — 09:39:30 and 12:14:10 — to show how date_part affects the result.

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

-- Returns 2. The actual difference is 2h 34m 40s, truncated to hours.
SELECT TIME_DIFF(TO_TIME(12,14,10), TO_TIME(09,39,30), 'hour');

-- Returns 154. Truncated to whole minutes.
SELECT TIME_DIFF(TO_TIME(12,14,10), TO_TIME(09,39,30), 'minute');

-- Returns 9280. Truncated to whole seconds.
SELECT TIME_DIFF(TO_TIME(12,14,10), TO_TIME(09,39,30), 'second');

Calculate the difference in microseconds

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

Calculate the difference in milliseconds

-- end_time (15:39:10) is earlier than start_time (15:39:30), so the result is negative.
-- Returns -20000.
SELECT TIME_DIFF(TO_TIME(15,39,10), TO_TIME(15,39,30), 'ff3');

Calculate the difference from a TIMESTAMP

-- 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'
);

Handle NULL inputs

Any NULL argument causes the function to return NULL.

-- 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.