TRUNC_TIME Function Reference: Date & Time Truncation in MaxCompute - MaxCompute

TRUNC_TIME truncates a date or time value to the specified time unit and returns a STRING.

Syntax

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

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

Parameters

date

Required. The date or timestamp to truncate. Accepted types: DATE, DATETIME, TIMESTAMP, TIMESTAMP_NTZ.

In the MaxCompute V1.0 data type edition, date also accepts a STRING value in DATETIME format (yyyy-mm-dd hh:mi:ss, for example, 2025-07-21 00:00:00). The value is implicitly converted to DATETIME before the calculation.

date_part

Required. The time unit to truncate to. Must be a case-insensitive STRING constant.

Value	Truncates to	Output format
`year`	The year boundary — drops month, day, and time	`yyyy`
`month`	The month boundary — drops day and time	`yyyy-mm`
`day`	The day boundary — drops the time component	`yyyy-mm-dd`
`hour`	The hour boundary — drops minutes and seconds	`yyyy-mm-dd hh:mm:ss`

Return value

Always returns a STRING. The output format depends on date_part, as shown in the table above.

Unlike functions that preserve the input type, TRUNC_TIME always returns a STRING regardless of whether the input is DATE, DATETIME, TIMESTAMP, or TIMESTAMP_NTZ.

Usage notes

Time zone conversion for time zone-related types

For time zone-related data types, such as DATETIME and TIMESTAMP, TRUNC_TIME first converts the local time to UTC, then truncates based on date_part, and returns the result as a STRING. The output date can therefore differ from the local date.

For example, DATETIME '2025-06-03 07:15:08' in UTC+8 is 2025-06-02 23:15:08 in UTC. Truncating to day returns 2025-06-02, not 2025-06-03.

Truncation vs. extraction

Truncation is not the same as extraction. TRUNC_TIME rounds a timestamp down to a time boundary and returns a STRING. To extract a component (such as the year number or month number) as an integer, use a date extraction function. For more information, see Date functions.

Error cases

If date is not one of the supported types (DATE, DATETIME, TIMESTAMP, TIMESTAMP_NTZ), an error is returned.
If date_part is NULL, an error is returned.

Examples

DATETIME input

The session time zone is UTC+8 (Asia/Shanghai). The input DATETIME '2025-06-03 07:15:08' converts to UTC as 2025-06-02 23:15:08 before truncation.

SET odps.sql.timezone=Asia/Shanghai;

SELECT
    TRUNC_TIME(DATETIME '2025-06-03 07:15:08', 'year')  AS truncated_to_year,
    TRUNC_TIME(DATETIME '2025-06-03 07:15:08', 'month') AS truncated_to_month,
    TRUNC_TIME(DATETIME '2025-06-03 07:15:08', 'day')   AS truncated_to_day,
    TRUNC_TIME(DATETIME '2025-06-03 07:15:08', 'hour')  AS truncated_to_hour;

-- Result:
+--------------------+--------------------+--------------------+---------------------+
| truncated_to_year  | truncated_to_month | truncated_to_day   | truncated_to_hour   |
+--------------------+--------------------+--------------------+---------------------+
| 2025               | 2025-06            | 2025-06-02         | 2025-06-02 23:00:00 |
+--------------------+--------------------+--------------------+---------------------+

DATE input

SELECT
    TRUNC_TIME(DATE '2025-06-10', 'year')  AS truncated_to_year,
    TRUNC_TIME(DATE '2025-06-10', 'month') AS truncated_to_month,
    TRUNC_TIME(DATE '2025-06-10', 'day')   AS truncated_to_day;

-- Result:
+--------------------+--------------------+------------------+
| truncated_to_year  | truncated_to_month | truncated_to_day |
+--------------------+--------------------+------------------+
| 2025               | 2025-06            | 2025-06-10       |
+--------------------+--------------------+------------------+

STRING input (MaxCompute V1.0 data type edition only)

To use a STRING value as input, set the data type edition to V1.0. The string is implicitly converted to DATETIME and then converted to UTC before truncation.

-- Set the time zone to UTC+8.
SET odps.sql.timezone=Asia/Shanghai;
-- Set the data type edition to V1.0 to allow implicit STRING-to-DATETIME conversion.
SET odps.sql.type.system.odps2=false;

SELECT TRUNC_TIME('2025-06-03 15:30:30', 'hour');

-- Result (local time 15:30 UTC+8 converts to 07:00 UTC, then truncated to hour):
+---------------------+
| _c0                 |
+---------------------+
| 2025-06-03 07:00:00 |
+---------------------+

MaxCompute:TRUNC_TIME