trunc ↗

Use the trunc() function to truncate a numeric value to a specified number of decimal places by removing (not rounding) the extra digits.

Syntax

trunc(<number> [, <decimal_places>])

Parameters

Name Type Required Description
number integer, float Yes The numeric value to truncate.
decimal_places integer No The number of decimal places to keep. Defaults to 0 (truncate to integer). Negative values truncate digits to the left of the decimal point.

Returns

Type: float or integer

Description: The trunc() function returns the input value truncated to the specified number of decimal places. Unlike round(), which rounds to the nearest value, trunc() simply removes digits beyond the specified precision. If the input is null, the function returns null.

Usage notes

  • Input type: XQL doesn't support NaN or infinite values as input and these value types also can not be returned.
  • Truncation vs. Rounding: trunc() removes digits without rounding. For example, trunc(2.789, 1) returns 2.7, not 2.8.
  • Default Behavior: When decimal_places is omitted, the function truncates to 0 decimal places (i.e., returns the integer part).
  • Negative Decimal Places: A negative decimal_places value truncates digits to the left of the decimal point. For example, trunc(1234, -2) returns 1200.
  • Null Handling: If the input expression is null, the function returns null.
  • Direction: Truncation is always toward zero. For positive numbers, this is equivalent to floor(). For negative numbers, this is equivalent to ceil() (rounding toward zero, not toward negative infinity).
  • Common Use Cases: This function is typically used within the alter stage for precision control, data formatting, bucketing values, and removing unwanted decimal places.

Examples

Example 1: Truncate literal values to different precisions

Goal: Truncate a numeric literal to various decimal places to demonstrate the behavior.

XQL code:

dataset = xdr_data
| limit 1
| alter trunc0 = trunc(3.14159), trunc2 = trunc(3.14159, 2), trunc4 = trunc(3.14159, 4)
| fields trunc0, trunc2, trunc4

Explanation: trunc(3.14159) returns 3 (no decimal places), trunc(3.14159, 2) returns 3.14 (two decimal places), and trunc(3.14159, 4) returns 3.1415 (four decimal places). Note that the digits are removed, not rounded.

Output:

TRUNC0 TRUNC2 TRUNC4
3 3.14 3.1415

Example 2: Truncate field values to integer

Goal: Truncate floating-point field values to their integer part.

XQL code:

config timeframe = 1d
| dataset = sample_xql_raw
| alter truncated_duration = trunc(duration_seconds)
| fields event_id, duration_seconds, truncated_duration
| limit 3

Explanation: This query truncates each duration_seconds value to its integer part by removing the decimal portion. For example, 1.5 becomes 1 and 10.2 becomes 10. Unlike floor(), trunc() rounds toward zero, so -2.7 would become -2 (not -3).

Output:

EVENT_ID DURATION_SECONDS TRUNCATED_DURATION
101 1.5 1
102 0.8 0
103 10.2 10

Example 3: Truncate with negative decimal places

Goal: Truncate values to the nearest hundred using negative decimal places.

XQL code:

config timeframe = 1d
| dataset = sample_xql_raw
| alter truncated_id = trunc(event_id, -2)
| fields event_id, truncated_id
| limit 3

Explanation: Using trunc() with -2 decimal places truncates the last two digits of the integer, effectively rounding down to the nearest hundred. For example, 101 becomes 100 and 215 becomes 200.

Output:

EVENT_ID TRUNCATED_ID
101 100
102 100
103 100

Example 4: Compare trunc with round and floor for negative numbers

Goal: Demonstrate the difference between trunc(), round(), and floor() for negative numbers.

XQL code:

config timeframe = 1d
| dataset = sample_xql_raw
| alter neg_val = multiply(duration_seconds, -1)
| alter trunc_val = trunc(neg_val)
| alter round_val = round(neg_val)
| alter floor_val = floor(neg_val)
| fields event_id, neg_val, trunc_val, round_val, floor_val
| limit 3

Explanation: For negative numbers, trunc() rounds toward zero (for example, -1.5 becomes -1), round() rounds to the nearest integer (for example, -1.5 becomes -2), and floor() rounds toward negative infinity (for example, -1.5 becomes -2). This demonstrates the key behavioral difference between these functions.

Output:

EVENT_ID NEG_VAL TRUNC_VAL ROUND_VAL FLOOR_VAL
101 -1.5 -1 -2 -2
102 -0.8 0 -1 -1
103 -10.2 -10 -10 -11