TRUNC_TIME - MaxCompute - Alibaba Cloud Documentation Center

This TRUNC_TIME function truncates a date or time value to the time unit specified by date_part and returns a STRING value.

Syntax

STRING TRUNC_TIME(DATE|DATETIME|TIMESTAMP|TIMESTAMP_NTZ <date>, STRING <date_part>)

-- Standard example.
-- Returns 2025-06.
SELECT TRUNC_TIME(DATETIME '2025-06-10 14:20:30', 'month');

Parameters

date: Required. The date or timestamp to be truncated. The value can be of the DATE, DATETIME, TIMESTAMP, or TIMESTAMP_NTZ type.
If you use the MaxCompute V1.0 data type edition, the input can also be a STRING value. The string must be in the DATETIME format yyyy-mm-dd hh:mi:ss, such as 2025-07-21 00:00:00. The value is implicitly converted to the DATETIME type before the calculation.
date_part: Required. The time unit for truncation. This parameter is a case-insensitive STRING constant. Valid values: year, month, day, and hour.

Return value

Returns a STRING value. The format of the returned string depends on the value of date_part.

For time zone-related data types, such as DATETIME and TIMESTAMP, the TRUNC_TIME function first converts the local time to UTC. Then, it truncates the time based on date_part and returns the result as a STRING value.

If date_part is 'year', the function returns a value in the yyyy format.
If date_part is 'month', the function returns a value in the yyyy-mm format.
If date_part is 'day', the function returns a value in the yyyy-mm-dd format.
If date_part is 'hour', the function returns a value in the yyyy-mm-dd hh:mm:ss format.

The following rules apply to returns:

If the data type of date is not DATE, DATETIME, TIMESTAMP, or TIMESTAMP_NTZ, an error is returned.
If date_part is NULL, an error is returned.

Examples

Example 1: The input is a DATETIME value.

For example, assume that the time zone of the current session or project is UTC+8 and the local time is DATETIME '2025-06-03 07:15:08'. The TRUNC_TIME function first converts this local time to the UTC time '2025-06-02 23:15:08'. Then, the function truncates the time based on date_part and returns the result as a STRING value.

SET odps.sql.timezone=Asia/Shanghai;
SELECT 
       TRUNC_TIME(DATETIME '2025-06-03 07:15:08','year') AS tc_year,
       TRUNC_TIME(DATETIME '2025-06-03 07:15:08','month') AS tc_month,
       TRUNC_TIME(DATETIME '2025-06-03 07:15:08','day') AS tc_date,
       TRUNC_TIME(DATETIME '2025-06-03 07:15:08','hour') AS tc_hour;
       
-- The following result is returned.
+------------+------------+------------+------------+
| tc_year    | tc_month   | tc_date    | tc_hour    |
+------------+------------+------------+------------+
| 2025       | 2025-06    | 2025-06-02 | 2025-06-02 23:00:00 |
+------------+------------+------------+------------+

Example 2: The input is a DATE value.

For example, if the input is DATE '2025-06-10', the TRUNC_TIME function truncates this value based on the time unit specified by date_part and returns a STRING value.

SELECT 
       TRUNC_TIME(DATE '2025-06-10','year' ),
       TRUNC_TIME(DATE '2025-06-10','month' ),
       TRUNC_TIME(DATE '2025-06-10','day' );

-- The following result is returned.
+------------+------------+------------+
| _c0        | _c1        | _c2        |
+------------+------------+------------+
| 2025       | 2025-06    | 2025-06-10 |
+------------+------------+------------+

Example 3: The time is a STRING type.

For example, if the input is the STRING value '2025-06-03 15:30:30', you must set the data type edition of your MaxCompute project to V1.0. This allows the value to be implicitly converted to the DATETIME type for the calculation. Otherwise, an error is returned.

-- The time zone of the current session or project is UTC+8.
SET odps.sql.timezone=Asia/Shanghai;
-- Set the data type edition to V1.0.
SET odps.sql.type.system.odps2=false;

-- The time is first converted to UTC and then truncated to the hour.
SELECT TRUNC_TIME('2025-06-03 15:30:30','hour');

-- The following result is returned.
+------------+
| _c0        |
+------------+
| 2025-06-03 07:00:00 |
+------------+

Related functions

The TRUNC_TIME function is a date function. For more information about date calculation and date conversion functions, see Date functions.