TIME_TRUNC - MaxCompute - Alibaba Cloud Documentation Center

Truncates a time value to the specified granularity and returns the result as a BIGINT in microseconds.

Unlike extraction functions that return a numeric component (such as the hour number), truncation returns the same type of value with lower-precision fields cleared. For example, truncating 15:31:38.776 to the hour returns the BIGINT equivalent of 15:00:00, not the number 15.

Syntax

BIGINT TIME_TRUNC(BIGINT <time>, STRING <date_part>)

Example

-- Returns 1234567890.
SELECT TIME_TRUNC(1234567890L, 'ff6');

Parameters

Parameter	Type	Required	Description
`time`	BIGINT	Yes	A time value in microseconds. Use TO_TIME to construct this value. In the MaxCompute 1.0 data type version, STRING and DOUBLE values are implicitly converted to BIGINT.
`date_part`	STRING	Yes	The granularity to truncate to. Must be a STRING constant. See the table below for valid values.

Valid values for date_part

Value	Aliases	Description
`MICROSECOND`	`MICROS`, `ff6`	No truncation — microsecond is the smallest unit.
`MILLISECOND`	`MILLIS`, `ff3`	Truncates to the nearest millisecond. For example, `15:30:00.123456` truncates to `15:30:00.123`.
`SECOND`	`SS`	Truncates to the nearest second.
`MINUTE`	`MI`	Truncates to the nearest minute.
`HOUR`	`HH`	Truncates to the nearest hour.

Return value

Returns a BIGINT value in microseconds.

Condition	Result
`time` is not a BIGINT	Error
`date_part` is non-constant, in an unsupported format, or of a non-STRING type	Error
`time` or `date_part` is NULL	NULL

Examples

Truncate to different granularities

The following examples use the same input time 15:31:38.776 to show what each granularity clears.

-- Input: 15:31:38.776

-- Truncate to the hour. Returns 15:00:00.
SELECT FORMAT_TIME(
  TIME_TRUNC(TO_TIME('15:31:38.776', 'HH:MI:SS.ff3'), 'HOUR'),
  '%H:%M:%S'
) AS truncated_hour;

-- Truncate to the minute. Returns 15:31:00.
SELECT FORMAT_TIME(
  TIME_TRUNC(TO_TIME('15:31:38.776', 'HH:MI:SS.ff3'), 'MINUTE'),
  '%H:%M:%S'
) AS truncated_minute;

-- Truncate to the second. Returns 15:31:38.
SELECT FORMAT_TIME(
  TIME_TRUNC(TO_TIME('15:31:38.776', 'HH:MI:SS.ff3'), 'SECOND'),
  '%H:%M:%S'
) AS truncated_second;

Truncate a raw microsecond value

-- Truncate to microsecond (ff6) — no change. Returns 1234567890.
SELECT TIME_TRUNC(1234567890, 'ff6');

-- Truncate to millisecond (ff3). Returns 1234567000.
SELECT TIME_TRUNC(1234567890, 'ff3');

Handle NULL inputs

-- Returns NULL when time is NULL.
SELECT TIME_TRUNC(NULL, 'SECOND');

-- Returns NULL when date_part is NULL.
SELECT TIME_TRUNC(TO_TIME(12, 00, 00), NULL);

Related functions

TIME_TRUNC is a time function. For other functions that calculate or transform time values, see Date and time functions.