Functions for working with dates and times

[Edit this page](https://github.com/ClickHouse/ClickHouse/tree/master/docs/en/sql-reference/functions/date-time-functions.md)

Most functions in this section accept an optional time zone argument, e.g. `Europe/Amsterdam`. In this case, the time zone is the specified one instead of the local (default) one.

**Example**

```
SELECT
    toDateTime('2016-06-15 23:00:00') AS time,
    toDate(time) AS date_local,
    toDate(time, 'Asia/Yekaterinburg') AS date_yekat,
    toString(time, 'US/Samoa') AS time_samoa
```

```
┌────────────────time─┬─date_local─┬─date_yekat─┬─time_samoa──────────┐
│ 2016-06-15 23:00:00 │ 2016-06-15 │ 2016-06-16 │ 2016-06-15 09:00:00 │
└─────────────────────┴────────────┴────────────┴─────────────────────┘
```

Note

For SQL standard compatibility the following functions, `NOW`, `CURRENT_TIMESTAMP`, `TODAY`, and `CURRENT_DATE` can be used without parentheses.

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

Introduced in: v22.11.0

Returns the current date and time at the moment of query analysis. The function is a constant expression.

This function gives the same result that `now('UTC')` would. It was added only for MySQL support. [`now`](#now) is the preferred usage.

**Syntax**

```
UTCTimestamp()
```

**Aliases**: `UTC_timestamp`

**Arguments**

- None.

**Returned value**

Returns the current date and time at the moment of query analysis. [`DateTime`](/docs/sql-reference/data-types/datetime)

**Examples**

**Get current UTC timestamp**

```
SELECT UTCTimestamp()
```

```
┌──────UTCTimestamp()─┐
│ 2024-05-28 08:32:09 │
└─────────────────────┘
```

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

Introduced in: v23.9.0

Converts a number containing the year, month and day number to a `Date`.
This function is the opposite of function [`toYYYYMMDD()`](/docs/sql-reference/functions/date-time-functions#toYYYYMMDD).
The output is undefined if the input does not encode a valid Date value.

**Syntax**

```
YYYYMMDDToDate(YYYYMMDD)
```

**Arguments**

- `YYYYMMDD` — Number containing the year, month and day. [`(U)Int*`](/docs/sql-reference/data-types/int-uint) or [`Float*`](/docs/sql-reference/data-types/float) or [`Decimal`](/docs/sql-reference/data-types/decimal)

**Returned value**

Returns a `Date` value from the provided arguments [`Date`](/docs/sql-reference/data-types/date)

**Examples**

**Example**

```
SELECT YYYYMMDDToDate(20230911);
```

```
┌─toYYYYMMDD(20230911)─┐
│           2023-09-11 │
└──────────────────────┘
```

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

Introduced in: v23.9.0

Converts a number containing the year, month and day number to a `Date32`.
This function is the opposite of function [`toYYYYMMDD()`](/docs/sql-reference/functions/date-time-functions#toYYYYMMDD).
The output is undefined if the input does not encode a valid `Date32` value.

**Syntax**

```
YYYYMMDDToDate32(YYYYMMDD)
```

**Arguments**

- `YYYYMMDD` — Number containing the year, month and day. [`(U)Int*`](/docs/sql-reference/data-types/int-uint) or [`Float*`](/docs/sql-reference/data-types/float) or [`Decimal`](/docs/sql-reference/data-types/decimal)

**Returned value**

Returns a `Date32` value from the provided arguments [`Date32`](/docs/sql-reference/data-types/date32)

**Examples**

**Example**

```
SELECT YYYYMMDDToDate32(20000507);
```

```
┌─YYYYMMDDToDate32(20000507)─┐
│                 2000-05-07 │
└────────────────────────────┘
```

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

Introduced in: v23.9.0

Converts a number containing the year, month, day, hour, minute, and second to a `DateTime`.
This function is the opposite of function [`toYYYYMMDDhhmmss()`](/docs/sql-reference/functions/date-time-functions#toYYYYMMDDhhmmss).
The output is undefined if the input does not encode a valid `DateTime` value.

**Syntax**

```
YYYYMMDDhhmmssToDateTime(YYYYMMDDhhmmss[, timezone])
```

**Arguments**

- `YYYYMMDDhhmmss` — Number containing the year, month, day, hour, minute, and second. [`(U)Int*`](/docs/sql-reference/data-types/int-uint) or [`Float*`](/docs/sql-reference/data-types/float) or [`Decimal`](/docs/sql-reference/data-types/decimal)
- `timezone` — Timezone name. [`String`](/docs/sql-reference/data-types/string)

**Returned value**

Returns a `DateTime` value from the provided arguments [`DateTime`](/docs/sql-reference/data-types/datetime)

**Examples**

**Example**

```
SELECT YYYYMMDDToDateTime(20230911131415);
```

```
┌──────YYYYMMDDhhmmssToDateTime(20230911131415)─┐
│                           2023-09-11 13:14:15 │
└───────────────────────────────────────────────┘
```

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

Introduced in: v23.9.0

Converts a number containing the year, month, day, hour, minute, and second to a `DateTime64`.
This function is the opposite of function [`toYYYYMMDDhhmmss()`](/docs/sql-reference/functions/date-time-functions#toYYYYMMDDhhmmss).
The output is undefined if the input does not encode a valid `DateTime64` value.

**Syntax**

```
YYYYMMDDhhmmssToDateTime64(YYYYMMDDhhmmss[, precision[, timezone]])
```

**Arguments**

- `YYYYMMDDhhmmss` — Number containing the year, month, day, hour, minute, and second. [`(U)Int*`](/docs/sql-reference/data-types/int-uint) or [`Float*`](/docs/sql-reference/data-types/float) or [`Decimal`](/docs/sql-reference/data-types/decimal)
- `precision` — Precision for the fractional part (0-9). [`UInt8`](/docs/sql-reference/data-types/int-uint)
- `timezone` — Timezone name. [`String`](/docs/sql-reference/data-types/string)

**Returned value**

Returns a `DateTime64` value from the provided arguments [`DateTime64`](/docs/sql-reference/data-types/datetime64)

**Examples**

**Example**

```
SELECT YYYYMMDDhhmmssToDateTime64(20230911131415, 3, 'Asia/Istanbul');
```

```
┌─YYYYMMDDhhmm⋯/Istanbul')─┐
│  2023-09-11 13:14:15.000 │
└──────────────────────────┘
```

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

Introduced in: v23.9.0

Adds the time interval to the provided date, date with time or string-encoded date or date with time.
If the addition results in a value outside the bounds of the data type, the result is undefined.

**Syntax**

```
addDate(datetime, interval)
```

**Arguments**

- `datetime` — The date or date with time to which `interval` is added. [`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) or [`String`](/docs/sql-reference/data-types/string)
- `interval` — Interval to add. [`Interval`](/docs/sql-reference/data-types/int-uint)

**Returned value**

Returns date or date with time obtained by adding `interval` to `datetime`. [`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**

**Add interval to date**

```
SELECT addDate(toDate('2018-01-01'), INTERVAL 3 YEAR)
```

```
┌─addDate(toDa⋯valYear(3))─┐
│               2021-01-01 │
└──────────────────────────┘
```

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

Introduced in: v1.1.0

Adds a specified number of days to a date, a date with time or a string-encoded date or date with time.

**Syntax**

```
addDays(datetime, num)
```

**Arguments**

- `datetime` — Date or date with time to add specified number of days to. [`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) or [`String`](/docs/sql-reference/data-types/string)
- `num` — Number of days to add. [`(U)Int*`](/docs/sql-reference/data-types/int-uint) or [`Float*`](/docs/sql-reference/data-types/float)

**Returned value**