Operators

The `part` parameter is case-insensitive.

The `date` parameter specifies the date or the time to process. The [Date](/docs/sql-reference/data-types/date), [Date32](/docs/sql-reference/data-types/date32), [DateTime](/docs/sql-reference/data-types/datetime), and [DateTime64](/docs/sql-reference/data-types/datetime64) types are supported.

Examples:

```
SELECT EXTRACT(DAY FROM toDate('2017-06-15'));
SELECT EXTRACT(MONTH FROM toDate('2017-06-15'));
SELECT EXTRACT(YEAR FROM toDate('2017-06-15'));
SELECT EXTRACT(EPOCH FROM toDateTime('2024-01-15 12:30:45', 'UTC'));
SELECT EXTRACT(DOW FROM toDate('2024-01-15'));
SELECT EXTRACT(CENTURY FROM toDate('2024-01-01'));
```

In the following example we create a table and insert into it a value with the `DateTime` type.

```
CREATE TABLE test.Orders
(
    OrderId UInt64,
    OrderName String,
    OrderDate DateTime
) ENGINE = MergeTree
ORDER BY ();
```

```
INSERT INTO test.Orders VALUES (1, 'Jarlsberg Cheese', toDateTime('2008-10-11 13:23:44'));
```

```
SELECT
    toYear(OrderDate) AS OrderYear,
    toMonth(OrderDate) AS OrderMonth,
    toDayOfMonth(OrderDate) AS OrderDay,
    toHour(OrderDate) AS OrderHour,
    toMinute(OrderDate) AS OrderMinute,
    toSecond(OrderDate) AS OrderSecond
FROM test.Orders;
```

```
┌─OrderYear─┬─OrderMonth─┬─OrderDay─┬─OrderHour─┬─OrderMinute─┬─OrderSecond─┐
│      2008 │         10 │       11 │        13 │          23 │          44 │
└───────────┴────────────┴──────────┴───────────┴─────────────┴─────────────┘
```

You can see more examples in [tests](https://github.com/ClickHouse/ClickHouse/blob/master/tests/queries/0_stateless/00619_extract.sql).

### INTERVAL[​](#interval "Direct link to INTERVAL")

Creates an [Interval](/docs/sql-reference/data-types/special-data-types/interval)-type value that should be used in arithmetical operations with [Date](/docs/sql-reference/data-types/date) and [DateTime](/docs/sql-reference/data-types/datetime)-type values.

Types of intervals:

- `SECOND`
- `MINUTE`
- `HOUR`
- `DAY`
- `WEEK`
- `MONTH`
- `QUARTER`
- `YEAR`

You can also use a string literal when setting the `INTERVAL` value. For example, `INTERVAL 1 HOUR` is identical to the `INTERVAL '1 hour'` or `INTERVAL '1' hour`.

Tip

Intervals with different types can't be combined. You can't use expressions like `INTERVAL 4 DAY 1 HOUR`. Specify intervals in units that are smaller or equal to the smallest unit of the interval, for example, `INTERVAL 25 HOUR`. You can use consecutive operations, like in the example below.

Examples:

```
SELECT now() AS current_date_time, current_date_time + INTERVAL 4 DAY + INTERVAL 3 HOUR;
```

```
┌───current_date_time─┬─plus(plus(now(), toIntervalDay(4)), toIntervalHour(3))─┐
│ 2020-11-03 22:09:50 │                                    2020-11-08 01:09:50 │
└─────────────────────┴────────────────────────────────────────────────────────┘
```

```
SELECT now() AS current_date_time, current_date_time + INTERVAL '4 day' + INTERVAL '3 hour';
```

```
┌───current_date_time─┬─plus(plus(now(), toIntervalDay(4)), toIntervalHour(3))─┐
│ 2020-11-03 22:12:10 │                                    2020-11-08 01:12:10 │
└─────────────────────┴────────────────────────────────────────────────────────┘
```

```
SELECT now() AS current_date_time, current_date_time + INTERVAL '4' day + INTERVAL '3' hour;
```

```
┌───current_date_time─┬─plus(plus(now(), toIntervalDay('4')), toIntervalHour('3'))─┐
│ 2020-11-03 22:33:19 │                                        2020-11-08 01:33:19 │
└─────────────────────┴────────────────────────────────────────────────────────────┘
```

Note

The `INTERVAL` syntax or `addDays` function are always preferred. Simple addition or subtraction (syntax like `now() + ...`) doesn't consider time settings. For example, daylight saving time.

Examples:

```
SELECT toDateTime('2014-10-26 00:00:00', 'Asia/Istanbul') AS time, time + 60 * 60 * 24 AS time_plus_24_hours, time + toIntervalDay(1) AS time_plus_1_day;
```

```
┌────────────────time─┬──time_plus_24_hours─┬─────time_plus_1_day─┐
│ 2014-10-26 00:00:00 │ 2014-10-26 23:00:00 │ 2014-10-27 00:00:00 │
└─────────────────────┴─────────────────────┴─────────────────────┘
```

**See Also**

- [Interval](/docs/sql-reference/data-types/special-data-types/interval) data type
- [toInterval](/docs/sql-reference/functions/type-conversion-functions#toIntervalYear) type conversion functions

## Logical AND Operator[​](#logical-and-operator "Direct link to Logical AND Operator")

Syntax `SELECT a AND b` — calculates logical conjunction of `a` and `b` with the function [and](/docs/sql-reference/functions/logical-functions#and).

## Logical OR Operator[​](#logical-or-operator "Direct link to Logical OR Operator")

Syntax `SELECT a OR b` — calculates logical disjunction of `a` and `b` with the function [or](/docs/sql-reference/functions/logical-functions#or).

## Logical Negation Operator[​](#logical-negation-operator "Direct link to Logical Negation Operator")

Syntax `SELECT NOT a` — calculates logical negation of `a` with the function [not](/docs/sql-reference/functions/logical-functions#not).

## Conditional Operator[​](#conditional-operator "Direct link to Conditional Operator")

`a ? b : c` – The `if(a, b, c)` function.

Note:

The conditional operator calculates the values of b and c, then checks whether condition a is met, and then returns the corresponding value. If `b` or `C` is an [arrayJoin()](/docs/sql-reference/functions/array-join) function, each row will be replicated regardless of the "a" condition.

## Conditional Expression[​](#conditional-expression "Direct link to Conditional Expression")

```
CASE [x]
    WHEN a THEN b
    [WHEN ... THEN ...]
    [ELSE c]
END
```

If `x` is specified, then `transform(x, [a, ...], [b, ...], c)` function is used. Otherwise – `multiIf(a, b, ..., c)`.

If there is no `ELSE c` clause in the expression, the default value is `NULL`.

The `transform` function does not work with `NULL`.

## Concatenation Operator[​](#concatenation-operator "Direct link to Concatenation Operator")

`s1 || s2` – The `concat(s1, s2) function.`

## Lambda Creation Operator[​](#lambda-creation-operator "Direct link to Lambda Creation Operator")

`x -> expr` – The `lambda(x, expr) function.`

The following operators do not have a priority since they are brackets:

## Array Creation Operator[​](#array-creation-operator "Direct link to Array Creation Operator")

`[x1, ...]` – The `array(x1, ...) function.`

## Tuple Creation Operator[​](#tuple-creation-operator "Direct link to Tuple Creation Operator")

`(x1, x2, ...)` – The `tuple(x2, x2, ...) function.`

## Associativity[​](#associativity "Direct link to Associativity")

All binary operators have left associativity. For example, `1 + 2 + 3` is transformed to `plus(plus(1, 2), 3)`.
Sometimes this does not work the way you expect. For example, `SELECT 4 > 2 > 3` will result in 0.

For efficiency, the `and` and `or` functions accept any number of arguments. The corresponding chains of `AND` and `OR` operators are transformed into a single call of these functions.

## Checking for `NULL`[​](#checking-for-null "Direct link to checking-for-null")

ClickHouse supports the `IS NULL` and `IS NOT NULL` operators.

### IS NULL[​](#is_null "Direct link to IS NULL")

- For [Nullable](/docs/sql-reference/data-types/nullable) type values, the `IS NULL` operator returns:
  - `1`, if the value is `NULL`.
  - `0` otherwise.
- For other values, the `IS NULL` operator always returns `0`.

Can be optimized by enabling the [optimize\_functions\_to\_subcolumns](/docs/operations/settings/settings#optimize_functions_to_subcolumns) setting. With `optimize_functions_to_subcolumns = 1` the function reads only [null](/docs/sql-reference/data-types/nullable#finding-null) subcolumn instead of reading and processing the whole column data. The query `SELECT n IS NULL FROM table` transforms to `SELECT n.null FROM TABLE`.

```
SELECT x+100 FROM t_null WHERE y IS NULL
```

```
┌─plus(x, 100)─┐
│          101 │
└──────────────┘
```

### IS NOT NULL[​](#is_not_null "Direct link to IS NOT NULL")