Functions for working with dates and times

**Using alternative INTERVAL syntax**

```
SELECT dateAdd('1998-06-16'::Date, INTERVAL 10 year)
```

```
┌─plus(CAST('1⋯alYear(10))─┐
│               2008-06-16 │
└──────────────────────────┘
```

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

Introduced in: v23.1.0

Returns the unit component of the difference between `startdate` and `enddate`.
The difference is calculated using a precision of 1 nanosecond.

For example, the difference between 2021-12-29 and 2022-01-01 is 3 days for the day unit,
0 months for the month unit, and 0 years for the year unit.

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

**Syntax**

```
age('unit', startdate, enddate[, timezone])
```

**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. [`Int32`](/docs/sql-reference/data-types/int-uint)

**Examples**

**Calculate age in hours**

```
SELECT age('hour', toDateTime('2018-01-01 22:30:00'), toDateTime('2018-01-02 23:00:00'))
```

```
┌─age('hour', toDateTime('2018-01-01 22:30:00'), toDateTime('2018-01-02 23:00:00'))─┐
│                                                                                24 │
└───────────────────────────────────────────────────────────────────────────────────┘
```

**Calculate age in different units**

```
SELECT
    toDate('2022-01-01') AS e,
    toDate('2021-12-29') AS s,
    age('day', s, e) AS day_age,
    age('month', s, e) AS month_age,
    age('year', s, e) AS year_age
```

```
┌──────────e─┬──────────s─┬─day_age─┬─month_age─┬─year_age─┐
│ 2022-01-01 │ 2021-12-29 │       3 │         0 │        0 │
└────────────┴────────────┴─────────┴───────────┴──────────┘
```

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

Introduced in: v24.7.0

Changes the day component of a date or date time.

**Syntax**

```
changeDay(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 day 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 changeDay('2024-01-31'::DateTime, 15)
```

```
2024-01-15 00:00:00
```

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

Introduced in: v24.7.0

Changes the hour component of a date or date time.

**Syntax**

```
changeHour(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 hour component. [`DateTime`](/docs/sql-reference/data-types/datetime) or [`DateTime64`](/docs/sql-reference/data-types/datetime64)

**Examples**

**Usage example**

```
SELECT changeHour('2024-01-01 12:00:00'::DateTime, 5)
```

```
2024-01-01 05:00:00
```

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

Introduced in: v24.7.0

Changes the minute component of a `date or date time`.

**Syntax**

```
changeMinute(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 minute component. [`DateTime`](/docs/sql-reference/data-types/datetime) or [`DateTime64`](/docs/sql-reference/data-types/datetime64)

**Examples**

**Usage example**

```
SELECT changeMinute('2024-01-01 12:30:00'::DateTime, 45)
```

```
2024-01-01 12:45:00
```

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

Introduced in: v24.7.0

Changes the month component of a date or date time.

**Syntax**

```
changeMonth(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 month 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 changeMonth('2024-01-01'::DateTime, 12)
```

```
2024-12-01 00:00:00
```

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

Introduced in: v24.7.0

Changes the second component of a date or date time.

**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
```