Functions for working with dates and times

**Syntax**

```
changeSecond(date_or_datetime, value)
```

**Arguments**

- `date_or_datetime` — The value to change. [`Date`](/docs/sql-reference/data-types/date) or [`Date32`](/docs/sql-reference/data-types/date32) or [`DateTime`](/docs/sql-reference/data-types/datetime) or [`DateTime64`](/docs/sql-reference/data-types/datetime64)
- `value` — The new value. [`(U)Int*`](/docs/sql-reference/data-types/int-uint)

**Returned value**

Returns a value of the same type as `date_or_datetime` with modified seconds component. [`DateTime`](/docs/sql-reference/data-types/datetime) or [`DateTime64`](/docs/sql-reference/data-types/datetime64)

**Examples**

**Usage example**

```
SELECT changeSecond('2024-01-01 12:30:45'::DateTime, 15)
```

```
2024-01-01 12:30:15
```

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

Introduced in: v24.7.0

Changes the year component of a date or date time.

**Syntax**

```
changeYear(date_or_datetime, value)
```

**Arguments**

- `date_or_datetime` — The value to change. [`Date`](/docs/sql-reference/data-types/date) or [`Date32`](/docs/sql-reference/data-types/date32) or [`DateTime`](/docs/sql-reference/data-types/datetime) or [`DateTime64`](/docs/sql-reference/data-types/datetime64)
- `value` — The new value. [`(U)Int*`](/docs/sql-reference/data-types/int-uint)

**Returned value**

Returns a value of the same type as `date_or_datetime` with modified year component. [`Date`](/docs/sql-reference/data-types/date) or [`Date32`](/docs/sql-reference/data-types/date32) or [`DateTime`](/docs/sql-reference/data-types/datetime) or [`DateTime64`](/docs/sql-reference/data-types/datetime64)

**Examples**

**Usage example**

```
SELECT changeYear('2024-01-01'::DateTime, 2023)
```

```
2023-01-01 00:00:00
```

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

Introduced in: v23.4.0

Returns the count of the specified `unit` boundaries crossed between the `startdate` and the `enddate`.
The difference is calculated using relative units. For example, the difference between 2021-12-29 and 2022-01-01 is 3 days for unit day
(see [`toRelativeDayNum`](#toRelativeDayNum)), 1 month for unit month (see [`toRelativeMonthNum`](#toRelativeMonthNum)) and 1 year for unit year
(see [`toRelativeYearNum`](#toRelativeYearNum)).

If the unit `week` was specified, then `dateDiff` assumes that weeks start on Monday.
Note that this behavior is different from that of function `toWeek()` in which weeks start by default on Sunday.

For an alternative to `dateDiff`, see function [`age`](#age).

**Syntax**

```
dateDiff(unit, startdate, enddate[, timezone])
```

**Aliases**: `timestampDiff`, `DATE_DIFF`, `date_diff`, `TIMESTAMP_DIFF`, `timestamp_diff`

**Arguments**

- `unit` — The type of interval for result.

|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- |
| Unit Possible values|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | nanosecond `nanosecond`, `nanoseconds`, `ns`| microsecond `microsecond`, `microseconds`, `us`, `u`| millisecond `millisecond`, `milliseconds`, `ms`| second `second`, `seconds`, `ss`, `s`| minute `minute`, `minutes`, `mi`, `n`| hour `hour`, `hours`, `hh`, `h`| day `day`, `days`, `dd`, `d`| week `week`, `weeks`, `wk`, `ww`| month `month`, `months`, `mm`, `m`| quarter `quarter`, `quarters`, `qq`, `q`| year `year`, `years`, `yyyy`, `yy` | | | | | | | | | | | | | | | | | | | | | | | |

- `startdate` — The first time value to subtract (the subtrahend). [`Date`](/docs/sql-reference/data-types/date) or [`Date32`](/docs/sql-reference/data-types/date32) or [`DateTime`](/docs/sql-reference/data-types/datetime) or [`DateTime64`](/docs/sql-reference/data-types/datetime64)
- `enddate` — The second time value to subtract from (the minuend). [`Date`](/docs/sql-reference/data-types/date) or [`Date32`](/docs/sql-reference/data-types/date32) or [`DateTime`](/docs/sql-reference/data-types/datetime) or [`DateTime64`](/docs/sql-reference/data-types/datetime64)
- `timezone` — Optional. Timezone name. If specified, it is applied to both `startdate` and `enddate`. If not specified, timezones of `startdate` and `enddate` are used. If they are not the same, the result is unspecified. [`String`](/docs/sql-reference/data-types/string)

**Returned value**

Returns the difference between `enddate` and `startdate` expressed in `unit`. [`Int64`](/docs/sql-reference/data-types/int-uint)

**Examples**

**Calculate date difference in hours**

```
SELECT dateDiff('hour', toDateTime('2018-01-01 22:00:00'), toDateTime('2018-01-02 23:00:00')) AS res
```

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

**Calculate date difference in different units**

```
SELECT
    toDate('2022-01-01') AS e,
    toDate('2021-12-29') AS s,
    dateDiff('day', s, e) AS day_diff,
    dateDiff('month', s, e) AS month_diff,
    dateDiff('year', s, e) AS year_diff
```

```
┌──────────e─┬──────────s─┬─day_diff─┬─month_diff─┬─year_diff─┐
│ 2022-01-01 │ 2021-12-29 │        3 │          1 │         1 │
└────────────┴────────────┴──────────┴────────────┴───────────┘
```

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

Introduced in: v21.7.0

Returns the specified part of the date.

Possible values:

- 'year'
- 'quarter'
- 'month'
- 'week'
- 'dayofyear'
- 'day'
- 'weekday'
- 'hour'
- 'minute'
- 'second'

**Syntax**

```
dateName(date_part, date[, timezone])
```

**Arguments**

- `date_part` — The part of the date that you want to extract. [`String`](/docs/sql-reference/data-types/string)
- `datetime` — A date or date with time value. [`Date`](/docs/sql-reference/data-types/date) or [`Date32`](/docs/sql-reference/data-types/date32) or [`DateTime`](/docs/sql-reference/data-types/datetime) or [`DateTime64`](/docs/sql-reference/data-types/datetime64)
- `timezone` — Optional. Timezone. [`String`](/docs/sql-reference/data-types/string)

**Returned value**

Returns the specified part of date. [`String`](/docs/sql-reference/data-types/string)

**Examples**

**Extract different date parts**

```
WITH toDateTime('2021-04-14 11:22:33') AS date_value
SELECT
    dateName('year', date_value),
    dateName('month', date_value),
    dateName('day', date_value)
```

```
┌─dateName('year', date_value)─┬─dateName('month', date_value)─┬─dateName('day', date_value)─┐
│ 2021                         │ April                         │ 14                          │
└──────────────────────────────┴───────────────────────────────┴─────────────────────────────┘
```

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

Introduced in: v20.8.0

Truncates a date and time value to the specified part of the date.

**Syntax**

```
dateTrunc(unit, datetime[, timezone])
```

**Aliases**: `DATE_TRUNC`

**Arguments**

- `unit` —
  The type of interval to truncate the result. Possible values: `nanosecond` (only DateTime64), `microsecond` (only DateTime64), `millisecond` (only DateTime64), `second`, `minute`, `hour`, `day`, `week`, `month`, `quarter`, `year`.
  [`String`](/docs/sql-reference/data-types/string)
- `datetime` — Date and time. [`Date`](/docs/sql-reference/data-types/date) or [`Date32`](/docs/sql-reference/data-types/date32) or [`DateTime`](/docs/sql-reference/data-types/datetime) or [`DateTime64`](/docs/sql-reference/data-types/datetime64)
- `timezone` — Optional. Timezone name for the returned datetime. If not specified, the function uses the timezone of the `datetime` parameter. [`String`](/docs/sql-reference/data-types/string)

**Returned value**

Returns the truncated date and time value.