Rounding functions

**Returned value**

Returns a value rounded by the banker's rounding method. [`(U)Int*`](/docs/sql-reference/data-types/int-uint) or [`Decimal*`](/docs/sql-reference/data-types/decimal) or [`Float*`](/docs/sql-reference/data-types/float)

**Examples**

**Basic usage**

```
SELECT number / 2 AS x, roundBankers(x, 0) AS b FROM system.numbers LIMIT 10
```

```
┌───x─┬─b─┐
│   0 │ 0 │
│ 0.5 │ 0 │
│   1 │ 1 │
│ 1.5 │ 2 │
│   2 │ 2 │
│ 2.5 │ 2 │
│   3 │ 3 │
│ 3.5 │ 4 │
│   4 │ 4 │
│ 4.5 │ 4 │
└─────┴───┘
```

## roundDown[​](#roundDown "Direct link to roundDown")

Introduced in: v20.1.0

Rounds a number down to an element in the specified array.
If the value is less than the lower bound, the lower bound is returned.

**Syntax**

```
roundDown(num, arr)
```

**Arguments**

- `num` — A number to round down. [`(U)Int*`](/docs/sql-reference/data-types/int-uint) or [`Decimal*`](/docs/sql-reference/data-types/decimal) or [`Float*`](/docs/sql-reference/data-types/float)
- `arr` — Array of elements to round `num` down to. [`Array((U)Int*)`](/docs/sql-reference/data-types/array) or [`Array(Float*)`](/docs/sql-reference/data-types/array)

**Returned value**

Returns a number rounded down to an element in `arr`. If the value is less than the lowest bound, the lowest bound is returned. [`(U)Int*`](/docs/sql-reference/data-types/int-uint) or [`Float*`](/docs/sql-reference/data-types/float)

**Examples**

**Usage example**

```
SELECT *, roundDown(*, [3, 4, 5]) FROM system.numbers WHERE number IN (0, 1, 2, 3, 4, 5)
```

```
┌─number─┬─roundDown(number, [3, 4, 5])─┐
│      0 │                            3 │
│      1 │                            3 │
│      2 │                            3 │
│      3 │                            3 │
│      4 │                            4 │
│      5 │                            5 │
└────────┴──────────────────────────────┘
```

## roundDuration[​](#roundDuration "Direct link to roundDuration")

Introduced in: v1.1.0

Rounds a number down to the closest from a set of commonly used durations: `1, 10, 30, 60, 120, 180, 240, 300, 600, 1200, 1800, 3600, 7200, 18000, 36000`.
If the number is less than one, it returns `0`.

**Syntax**

```
roundDuration(num)
```

**Arguments**

- `num` — A number to round to one of the numbers in the set of common durations. [`(U)Int*`](/docs/sql-reference/data-types/int-uint) or [`Float*`](/docs/sql-reference/data-types/float)

**Returned value**

Returns `0`, for `num` < 1. Otherwise, one of: `1, 10, 30, 60, 120, 180, 240, 300, 600, 1200, 1800, 3600, 7200, 18000, 36000`. [`UInt16`](/docs/sql-reference/data-types/int-uint)

**Examples**

**Usage example**

```
SELECT *, roundDuration(*) FROM system.numbers WHERE number IN (0, 9, 19, 47, 101, 149, 205, 271, 421, 789, 1423, 2345, 4567, 9876, 24680, 42573)
```

```
┌─number─┬─roundDuration(number)─┐
│      0 │                     0 │
│      9 │                     1 │
│     19 │                    10 │
│     47 │                    30 │
│    101 │                    60 │
│    149 │                   120 │
│    205 │                   180 │
│    271 │                   240 │
│    421 │                   300 │
│    789 │                   600 │
│   1423 │                  1200 │
│   2345 │                  1800 │
│   4567 │                  3600 │
│   9876 │                  7200 │
│  24680 │                 18000 │
│  42573 │                 36000 │
└────────┴───────────────────────┘
```

## roundToExp2[​](#roundToExp2 "Direct link to roundToExp2")

Introduced in: v1.1.0

Rounds a number down to the nearest (whole non-negative) power of two.
If the number is less than one, it returns `0`.

**Syntax**

```
roundToExp2(num)
```

**Arguments**

- `num` — A number to round. [`(U)Int*`](/docs/sql-reference/data-types/int-uint) or [`Float*`](/docs/sql-reference/data-types/float)

**Returned value**

Returns `num` rounded down to the nearest (whole non-negative) power of two, otherwise `0` for `num < 1`. [`(U)Int*`](/docs/sql-reference/data-types/int-uint) or [`Float*`](/docs/sql-reference/data-types/float)

**Examples**

**Usage example**

```
SELECT *, roundToExp2(*) FROM system.numbers WHERE number IN (0, 2, 5, 10, 19, 50)
```

```
┌─number─┬─roundToExp2(number)─┐
│      0 │                   0 │
│      2 │                   2 │
│      5 │                   4 │
│     10 │                   8 │
│     19 │                  16 │
│     50 │                  32 │
└────────┴─────────────────────┘
```

## trunc[​](#trunc "Direct link to trunc")

Introduced in: v1.1.0

Like [`floor`](#floor) but returns the rounded number with the largest absolute value less than or equal to that of `x`.

**Syntax**

```
truncate(x[, N])
```

**Aliases**: `truncate`

**Arguments**

- `x` — The value to round. [`Float*`](/docs/sql-reference/data-types/float) or [`Decimal*`](/docs/sql-reference/data-types/decimal) or [`(U)Int*`](/docs/sql-reference/data-types/int-uint)
- `N` — Optional. The number of decimal places to round to. Defaults to zero, which means rounding to an integer. [`(U)Int*`](/docs/sql-reference/data-types/int-uint)

**Returned value**

Returns a rounded number of the same type as `x`. [`Float*`](/docs/sql-reference/data-types/float) or [`Decimal*`](/docs/sql-reference/data-types/decimal) or [`(U)Int*`](/docs/sql-reference/data-types/int-uint)

**Examples**

**Basic usage**

```
SELECT truncate(123.499, 1) AS res;
```

```
┌───res─┐
│ 123.4 │
└───────┘
```

- [ceil](#ceil)- [floor](#floor)- [round](#round)- [roundAge](#roundAge)- [roundBankers](#roundBankers)- [roundDown](#roundDown)- [roundDuration](#roundDuration)- [roundToExp2](#roundToExp2)- [trunc](#trunc)

Was this page helpful?