> ## Documentation Index
> Fetch the complete documentation index at: https://private-7c7dfe99-mintlify-8a08bda2.mintlify.site/llms.txt
> Use this file to discover all available pages before exploring further.
> Documentation for Functions for Working with Dates and Times
# Functions for Working with Dates and Times
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**
```sql theme={null}
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
```
```text theme={null}
┌────────────────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 │
└─────────────────────┴────────────┴────────────┴─────────────────────┘
```
For SQL standard compatibility the following functions, `NOW`, `CURRENT_TIMESTAMP`, `TODAY`, and `CURRENT_DATE` can be used without parentheses.
{/*AUTOGENERATED_START*/}
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**
```sql theme={null}
UTCTimestamp()
```
**Aliases**: `UTC_timestamp`
**Arguments**
* None.
**Returned value**
Returns the current date and time at the moment of query analysis. [`DateTime`](/reference/data-types/datetime)
**Examples**
**Get current UTC timestamp**
```sql title=Query theme={null}
SELECT UTCTimestamp()
```
```response title=Response theme={null}
┌──────UTCTimestamp()─┐
│ 2024-05-28 08:32:09 │
└─────────────────────┘
```
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()`](/reference/functions/regular-functions/date-time-functions#toYYYYMMDD).
The output is undefined if the input does not encode a valid Date value.
**Syntax**
```sql theme={null}
YYYYMMDDToDate(YYYYMMDD)
```
**Arguments**
* `YYYYMMDD` — Number containing the year, month and day. [`(U)Int*`](/reference/data-types/int-uint) or [`Float*`](/reference/data-types/float) or [`Decimal`](/reference/data-types/decimal)
**Returned value**
Returns a `Date` value from the provided arguments [`Date`](/reference/data-types/date)
**Examples**
**Example**
```sql title=Query theme={null}
SELECT YYYYMMDDToDate(20230911);
```
```response title=Response theme={null}
┌─toYYYYMMDD(20230911)─┐
│ 2023-09-11 │
└──────────────────────┘
```
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()`](/reference/functions/regular-functions/date-time-functions#toYYYYMMDD).
The output is undefined if the input does not encode a valid `Date32` value.
**Syntax**
```sql theme={null}
YYYYMMDDToDate32(YYYYMMDD)
```
**Arguments**
* `YYYYMMDD` — Number containing the year, month and day. [`(U)Int*`](/reference/data-types/int-uint) or [`Float*`](/reference/data-types/float) or [`Decimal`](/reference/data-types/decimal)
**Returned value**
Returns a `Date32` value from the provided arguments [`Date32`](/reference/data-types/date32)
**Examples**
**Example**
```sql title=Query theme={null}
SELECT YYYYMMDDToDate32(20000507);
```
```response title=Response theme={null}
┌─YYYYMMDDToDate32(20000507)─┐
│ 2000-05-07 │
└────────────────────────────┘
```
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()`](/reference/functions/regular-functions/date-time-functions#toYYYYMMDDhhmmss).
The output is undefined if the input does not encode a valid `DateTime` value.
**Syntax**
```sql theme={null}
YYYYMMDDhhmmssToDateTime(YYYYMMDDhhmmss[, timezone])
```
**Arguments**
* `YYYYMMDDhhmmss` — Number containing the year, month, day, hour, minute, and second. [`(U)Int*`](/reference/data-types/int-uint) or [`Float*`](/reference/data-types/float) or [`Decimal`](/reference/data-types/decimal)
* `timezone` — Timezone name. [`String`](/reference/data-types/string)
**Returned value**
Returns a `DateTime` value from the provided arguments [`DateTime`](/reference/data-types/datetime)
**Examples**
**Example**
```sql title=Query theme={null}
SELECT YYYYMMDDToDateTime(20230911131415);
```
```response title=Response theme={null}
┌──────YYYYMMDDhhmmssToDateTime(20230911131415)─┐
│ 2023-09-11 13:14:15 │
└───────────────────────────────────────────────┘
```
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()`](/reference/functions/regular-functions/date-time-functions#toYYYYMMDDhhmmss).
The output is undefined if the input does not encode a valid `DateTime64` value.
**Syntax**
```sql theme={null}
YYYYMMDDhhmmssToDateTime64(YYYYMMDDhhmmss[, precision[, timezone]])
```
**Arguments**
* `YYYYMMDDhhmmss` — Number containing the year, month, day, hour, minute, and second. [`(U)Int*`](/reference/data-types/int-uint) or [`Float*`](/reference/data-types/float) or [`Decimal`](/reference/data-types/decimal)
* `precision` — Precision for the fractional part (0-9). [`UInt8`](/reference/data-types/int-uint)
* `timezone` — Timezone name. [`String`](/reference/data-types/string)
**Returned value**
Returns a `DateTime64` value from the provided arguments [`DateTime64`](/reference/data-types/datetime64)
**Examples**
**Example**
```sql title=Query theme={null}
SELECT YYYYMMDDhhmmssToDateTime64(20230911131415, 3, 'Asia/Istanbul');
```
```response title=Response theme={null}
┌─YYYYMMDDhhmm⋯/Istanbul')─┐
│ 2023-09-11 13:14:15.000 │
└──────────────────────────┘
```
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**
```sql theme={null}
addDate(datetime, interval)
```
**Arguments**
* `datetime` — The date or date with time to which `interval` is added. [`Date`](/reference/data-types/date) or [`Date32`](/reference/data-types/date32) or [`DateTime`](/reference/data-types/datetime) or [`DateTime64`](/reference/data-types/datetime64) or [`String`](/reference/data-types/string)
* `interval` — Interval to add. [`Interval`](/reference/data-types/int-uint)
**Returned value**
Returns date or date with time obtained by adding `interval` to `datetime`. [`Date`](/reference/data-types/date) or [`Date32`](/reference/data-types/date32) or [`DateTime`](/reference/data-types/datetime) or [`DateTime64`](/reference/data-types/datetime64)
**Examples**
**Add interval to date**
```sql title=Query theme={null}
SELECT addDate(toDate('2018-01-01'), INTERVAL 3 YEAR)
```
```response title=Response theme={null}
┌─addDate(toDa⋯valYear(3))─┐
│ 2021-01-01 │
└──────────────────────────┘
```
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**
```sql theme={null}
addDays(datetime, num)
```
**Arguments**
* `datetime` — Date or date with time to add specified number of days to. [`Date`](/reference/data-types/date) or [`Date32`](/reference/data-types/date32) or [`DateTime`](/reference/data-types/datetime) or [`DateTime64`](/reference/data-types/datetime64) or [`String`](/reference/data-types/string)
* `num` — Number of days to add. [`(U)Int*`](/reference/data-types/int-uint) or [`Float*`](/reference/data-types/float)
**Returned value**
Returns `datetime` plus `num` days. [`Date`](/reference/data-types/date) or [`Date32`](/reference/data-types/date32) or [`DateTime`](/reference/data-types/datetime) or [`DateTime64`](/reference/data-types/datetime64)
**Examples**
**Add days to different date types**
```sql title=Query theme={null}
WITH
toDate('2024-01-01') AS date,
toDateTime('2024-01-01 00:00:00') AS date_time,
'2024-01-01 00:00:00' AS date_time_string
SELECT
addDays(date, 5) AS add_days_with_date,
addDays(date_time, 5) AS add_days_with_date_time,
addDays(date_time_string, 5) AS add_days_with_date_time_string
```
```response title=Response theme={null}
┌─add_days_with_date─┬─add_days_with_date_time─┬─add_days_with_date_time_string─┐
│ 2024-01-06 │ 2024-01-06 00:00:00 │ 2024-01-06 00:00:00.000 │
└────────────────────┴─────────────────────────┴────────────────────────────────┘
```
**Using alternative INTERVAL syntax**
```sql title=Query theme={null}
SELECT dateAdd('1998-06-16'::Date, INTERVAL 10 day)
```
```response title=Response theme={null}
┌─plus(CAST('1⋯valDay(10))─┐
│ 1998-06-26 │
└──────────────────────────┘
```
addHours
Introduced in: v1.1.0
Adds a specified number of hours to a date, a date with time or a string-encoded date or date with time.
**Syntax**
```sql theme={null}
addHours(datetime, num)
```
**Arguments**
* `datetime` — Date or date with time to add specified number of hours to. [`Date`](/reference/data-types/date) or [`Date32`](/reference/data-types/date32) or [`DateTime`](/reference/data-types/datetime) or [`DateTime64`](/reference/data-types/datetime64) or [`String`](/reference/data-types/string)
* `num` — Number of hours to add. [`(U)Int*`](/reference/data-types/int-uint) or [`Float*`](/reference/data-types/float)
**Returned value**
Returns `datetime` plus `num` hours [`DateTime`](/reference/data-types/datetime) or [`DateTime64(3)`](/reference/data-types/datetime64)
**Examples**
**Add hours to different date types**
```sql title=Query theme={null}
WITH
toDate('2024-01-01') AS date,
toDateTime('2024-01-01 00:00:00') AS date_time,
'2024-01-01 00:00:00' AS date_time_string
SELECT
addHours(date, 12) AS add_hours_with_date,
addHours(date_time, 12) AS add_hours_with_date_time,
addHours(date_time_string, 12) AS add_hours_with_date_time_string
```
```response title=Response theme={null}
┌─add_hours_with_date─┬─add_hours_with_date_time─┬─add_hours_with_date_time_string─┐
│ 2024-01-01 12:00:00 │ 2024-01-01 12:00:00 │ 2024-01-01 12:00:00.000 │
└─────────────────────┴──────────────────────────┴─────────────────────────────────┘
```
**Using alternative INTERVAL syntax**
```sql title=Query theme={null}
SELECT dateAdd('1998-06-16'::Date, INTERVAL 10 hour)
```
```response title=Response theme={null}
┌─plus(CAST('1⋯alHour(10))─┐
│ 1998-06-16 10:00:00 │
└──────────────────────────┘
```
addInterval
Introduced in: v22.11.0
Adds an interval to another interval or tuple of intervals.
Intervals of the same type will be combined into a single interval. For instance if `toIntervalDay(1)` and `toIntervalDay(2)` are passed then the result will be `(3)` rather than `(1,1)`.
**Syntax**
```sql theme={null}
addInterval(interval_1, interval_2)
```
**Arguments**
* `interval_1` — First interval or tuple of intervals. [`Interval`](/reference/data-types/int-uint) or [`Tuple(Interval)`](/reference/data-types/tuple)
* `interval_2` — Second interval to be added. [`Interval`](/reference/data-types/int-uint)
**Returned value**
Returns a tuple of intervals [`Tuple(Interval)`](/reference/data-types/tuple)
**Examples**
**Add intervals**
```sql title=Query theme={null}
SELECT addInterval(INTERVAL 1 DAY, INTERVAL 1 MONTH);
SELECT addInterval((INTERVAL 1 DAY, INTERVAL 1 YEAR), INTERVAL 1 MONTH);
SELECT addInterval(INTERVAL 2 DAY, INTERVAL 1 DAY)
```
```response title=Response theme={null}
┌─addInterval(toIntervalDay(1), toIntervalMonth(1))─┐
│ (1,1) │
└───────────────────────────────────────────────────┘
┌─addInterval((toIntervalDay(1), toIntervalYear(1)), toIntervalMonth(1))─┐
│ (1,1,1) │
└────────────────────────────────────────────────────────────────────────┘
┌─addInterval(toIntervalDay(2), toIntervalDay(1))─┐
│ (3) │
└─────────────────────────────────────────────────┘
```
addMicroseconds
Introduced in: v22.6.0
Adds a specified number of microseconds to a date with time or a string-encoded date with time.
**Syntax**
```sql theme={null}
addMicroseconds(datetime, num)
```
**Arguments**
* `datetime` — Date with time to add specified number of microseconds to. [`DateTime`](/reference/data-types/datetime) or [`DateTime64`](/reference/data-types/datetime64) or [`String`](/reference/data-types/string)
* `num` — Number of microseconds to add. [`(U)Int*`](/reference/data-types/int-uint) or [`Float*`](/reference/data-types/float)
**Returned value**
Returns `date_time` plus `num` microseconds [`DateTime64`](/reference/data-types/datetime64)
**Examples**
**Add microseconds to different date time types**
```sql title=Query theme={null}
WITH
toDateTime('2024-01-01 00:00:00') AS date_time,
'2024-01-01 00:00:00' AS date_time_string
SELECT
addMicroseconds(date_time, 1000000) AS add_microseconds_with_date_time,
addMicroseconds(date_time_string, 1000000) AS add_microseconds_with_date_time_string
```
```response title=Response theme={null}
┌─add_microseconds_with_date_time─┬─add_microseconds_with_date_time_string─┐
│ 2024-01-01 00:00:01.000000 │ 2024-01-01 00:00:01.000000 │
└─────────────────────────────────┴────────────────────────────────────────┘
```
**Using alternative INTERVAL syntax**
```sql title=Query theme={null}
SELECT dateAdd('1998-06-16'::DateTime, INTERVAL 10 microsecond)
```
```response title=Response theme={null}
┌─plus(CAST('19⋯osecond(10))─┐
│ 1998-06-16 00:00:00.000010 │
└────────────────────────────┘
```
addMilliseconds
Introduced in: v22.6.0
Adds a specified number of milliseconds to a date with time or a string-encoded date with time.
**Syntax**
```sql theme={null}
addMilliseconds(datetime, num)
```
**Arguments**
* `datetime` — Date with time to add specified number of milliseconds to. [`DateTime`](/reference/data-types/datetime) or [`DateTime64`](/reference/data-types/datetime64) or [`String`](/reference/data-types/string)
* `num` — Number of milliseconds to add. [`(U)Int*`](/reference/data-types/int-uint) or [`Float*`](/reference/data-types/float)
**Returned value**
Returns `datetime` plus `num` milliseconds [`DateTime64`](/reference/data-types/datetime64)
**Examples**
**Add milliseconds to different date time types**
```sql title=Query theme={null}
WITH
toDateTime('2024-01-01 00:00:00') AS date_time,
'2024-01-01 00:00:00' AS date_time_string
SELECT
addMilliseconds(date_time, 1000) AS add_milliseconds_with_date_time,
addMilliseconds(date_time_string, 1000) AS add_milliseconds_with_date_time_string
```
```response title=Response theme={null}
┌─add_milliseconds_with_date_time─┬─add_milliseconds_with_date_time_string─┐
│ 2024-01-01 00:00:01.000 │ 2024-01-01 00:00:01.000 │
└─────────────────────────────────┴────────────────────────────────────────┘
```
**Using alternative INTERVAL syntax**
```sql title=Query theme={null}
SELECT dateAdd('1998-06-16'::DateTime, INTERVAL 10 millisecond)
```
```response title=Response theme={null}
┌─plus(CAST('1⋯second(10))─┐
│ 1998-06-16 00:00:00.010 │
└──────────────────────────┘
```
addMinutes
Introduced in: v1.1.0
Adds a specified number of minutes to a date, a date with time or a string-encoded date or date with time.
**Syntax**
```sql theme={null}
addMinutes(datetime, num)
```
**Arguments**
* `datetime` — Date or date with time to add specified number of minutes to. [`Date`](/reference/data-types/date) or [`Date32`](/reference/data-types/date32) or [`DateTime`](/reference/data-types/datetime) or [`DateTime64`](/reference/data-types/datetime64) or [`String`](/reference/data-types/string)
* `num` — Number of minutes to add. [`(U)Int*`](/reference/data-types/int-uint) or [`Float*`](/reference/data-types/float)
**Returned value**
Returns `datetime` plus `num` minutes [`DateTime`](/reference/data-types/datetime) or [`DateTime64(3)`](/reference/data-types/datetime64)
**Examples**
**Add minutes to different date types**
```sql title=Query theme={null}
WITH
toDate('2024-01-01') AS date,
toDateTime('2024-01-01 00:00:00') AS date_time,
'2024-01-01 00:00:00' AS date_time_string
SELECT
addMinutes(date, 20) AS add_minutes_with_date,
addMinutes(date_time, 20) AS add_minutes_with_date_time,
addMinutes(date_time_string, 20) AS add_minutes_with_date_time_string
```
```response title=Response theme={null}
┌─add_minutes_with_date─┬─add_minutes_with_date_time─┬─add_minutes_with_date_time_string─┐
│ 2024-01-01 00:20:00 │ 2024-01-01 00:20:00 │ 2024-01-01 00:20:00.000 │
└───────────────────────┴────────────────────────────┴───────────────────────────────────┘
```
**Using alternative INTERVAL syntax**
```sql title=Query theme={null}
SELECT dateAdd('1998-06-16'::Date, INTERVAL 10 minute)
```
```response title=Response theme={null}
┌─plus(CAST('1⋯Minute(10))─┐
│ 1998-06-16 00:10:00 │
└──────────────────────────┘
```
addMonths
Introduced in: v1.1.0
Adds a specified number of months to a date, a date with time or a string-encoded date or date with time.
**Syntax**
```sql theme={null}
addMonths(datetime, num)
```
**Arguments**
* `datetime` — Date or date with time to add specified number of months to. [`Date`](/reference/data-types/date) or [`Date32`](/reference/data-types/date32) or [`DateTime`](/reference/data-types/datetime) or [`DateTime64`](/reference/data-types/datetime64) or [`String`](/reference/data-types/string)
* `num` — Number of months to add. [`(U)Int*`](/reference/data-types/int-uint) or [`Float*`](/reference/data-types/float)
**Returned value**
Returns `datetime` plus `num` months [`Date`](/reference/data-types/date) or [`Date32`](/reference/data-types/date32) or [`DateTime`](/reference/data-types/datetime) or [`DateTime64`](/reference/data-types/datetime64)
**Examples**
**Add months to different date types**
```sql title=Query theme={null}
WITH
toDate('2024-01-01') AS date,
toDateTime('2024-01-01 00:00:00') AS date_time,
'2024-01-01 00:00:00' AS date_time_string
SELECT
addMonths(date, 6) AS add_months_with_date,
addMonths(date_time, 6) AS add_months_with_date_time,
addMonths(date_time_string, 6) AS add_months_with_date_time_string
```
```response title=Response theme={null}
┌─add_months_with_date─┬─add_months_with_date_time─┬─add_months_with_date_time_string─┐
│ 2024-07-01 │ 2024-07-01 00:00:00 │ 2024-07-01 00:00:00.000 │
└──────────────────────┴───────────────────────────┴──────────────────────────────────┘
```
**Using alternative INTERVAL syntax**
```sql title=Query theme={null}
SELECT dateAdd('1998-06-16'::Date, INTERVAL 10 month)
```
```response title=Response theme={null}
┌─plus(CAST('1⋯lMonth(10))─┐
│ 1999-04-16 │
└──────────────────────────┘
```
addNanoseconds
Introduced in: v22.6.0
Adds a specified number of nanoseconds to a date with time or a string-encoded date with time.
**Syntax**
```sql theme={null}
addNanoseconds(datetime, num)
```
**Arguments**
* `datetime` — Date with time to add specified number of nanoseconds to. [`DateTime`](/reference/data-types/datetime) or [`DateTime64`](/reference/data-types/datetime64) or [`String`](/reference/data-types/string)
* `num` — Number of nanoseconds to add. [`(U)Int*`](/reference/data-types/int-uint) or [`Float*`](/reference/data-types/float)
**Returned value**
Returns `datetime` plus `num` nanoseconds [`DateTime64`](/reference/data-types/datetime64)
**Examples**
**Add nanoseconds to different date time types**
```sql title=Query theme={null}
WITH
toDateTime('2024-01-01 00:00:00') AS date_time,
'2024-01-01 00:00:00' AS date_time_string
SELECT
addNanoseconds(date_time, 1000) AS add_nanoseconds_with_date_time,
addNanoseconds(date_time_string, 1000) AS add_nanoseconds_with_date_time_string
```
```response title=Response theme={null}
┌─add_nanoseconds_with_date_time─┬─add_nanoseconds_with_date_time_string─┐
│ 2024-01-01 00:00:00.000001000 │ 2024-01-01 00:00:00.000001000 │
└────────────────────────────────┴───────────────────────────────────────┘
```
**Using alternative INTERVAL syntax**
```sql title=Query theme={null}
SELECT dateAdd('1998-06-16'::DateTime, INTERVAL 1000 nanosecond)
```
```response title=Response theme={null}
┌─plus(CAST('199⋯osecond(1000))─┐
│ 1998-06-16 00:00:00.000001000 │
└───────────────────────────────┘
```
addQuarters
Introduced in: v20.1.0
Adds a specified number of quarters to a date, a date with time or a string-encoded date or date with time.
**Syntax**
```sql theme={null}
addQuarters(datetime, num)
```
**Arguments**
* `datetime` — Date or date with time to add specified number of quarters to. [`Date`](/reference/data-types/date) or [`Date32`](/reference/data-types/date32) or [`DateTime`](/reference/data-types/datetime) or [`DateTime64`](/reference/data-types/datetime64) or [`String`](/reference/data-types/string)
* `num` — Number of quarters to add. [`(U)Int*`](/reference/data-types/int-uint) or [`Float*`](/reference/data-types/float)
**Returned value**
Returns `datetime` plus `num` quarters [`Date`](/reference/data-types/date) or [`Date32`](/reference/data-types/date32) or [`DateTime`](/reference/data-types/datetime) or [`DateTime64`](/reference/data-types/datetime64)
**Examples**
**Add quarters to different date types**
```sql title=Query theme={null}
WITH
toDate('2024-01-01') AS date,
toDateTime('2024-01-01 00:00:00') AS date_time,
'2024-01-01 00:00:00' AS date_time_string
SELECT
addQuarters(date, 1) AS add_quarters_with_date,
addQuarters(date_time, 1) AS add_quarters_with_date_time,
addQuarters(date_time_string, 1) AS add_quarters_with_date_time_string
```
```response title=Response theme={null}
┌─add_quarters_with_date─┬─add_quarters_with_date_time─┬─add_quarters_with_date_time_string─┐
│ 2024-04-01 │ 2024-04-01 00:00:00 │ 2024-04-01 00:00:00.000 │
└────────────────────────┴─────────────────────────────┴────────────────────────────────────┘
```
**Using alternative INTERVAL syntax**
```sql title=Query theme={null}
SELECT dateAdd('1998-06-16'::Date, INTERVAL 10 quarter)
```
```response title=Response theme={null}
┌─plus(CAST('1⋯uarter(10))─┐
│ 2000-12-16 │
└──────────────────────────┘
```
addSeconds
Introduced in: v1.1.0
Adds a specified number of seconds to a date, a date with time or a string-encoded date or date with time.
**Syntax**
```sql theme={null}
addSeconds(datetime, num)
```
**Arguments**
* `datetime` — Date or date with time to add specified number of seconds to. [`Date`](/reference/data-types/date) or [`Date32`](/reference/data-types/date32) or [`DateTime`](/reference/data-types/datetime) or [`DateTime64`](/reference/data-types/datetime64) or [`String`](/reference/data-types/string)
* `num` — Number of seconds to add. [`(U)Int*`](/reference/data-types/int-uint) or [`Float*`](/reference/data-types/float)
**Returned value**
Returns `datetime` plus `num` seconds [`DateTime`](/reference/data-types/datetime) or [`DateTime64(3)`](/reference/data-types/datetime64)
**Examples**
**Add seconds to different date types**
```sql title=Query theme={null}
WITH
toDate('2024-01-01') AS date,
toDateTime('2024-01-01 00:00:00') AS date_time,
'2024-01-01 00:00:00' AS date_time_string
SELECT
addSeconds(date, 30) AS add_seconds_with_date,
addSeconds(date_time, 30) AS add_seconds_with_date_time,
addSeconds(date_time_string, 30) AS add_seconds_with_date_time_string
```
```response title=Response theme={null}
┌─add_seconds_with_date─┬─add_seconds_with_date_time─┬─add_seconds_with_date_time_string─┐
│ 2024-01-01 00:00:30 │ 2024-01-01 00:00:30 │ 2024-01-01 00:00:30.000 │
└───────────────────────┴────────────────────────────┴───────────────────────────────────┘
```
**Using alternative INTERVAL syntax**
```sql title=Query theme={null}
SELECT dateAdd('1998-06-16'::Date, INTERVAL 10 second)
```
```response title=Response theme={null}
┌─dateAdd('1998-06-16'::Date, INTERVAL 10 second)─┐
│ 1998-06-16 00:00:10 │
└─────────────────────────────────────────────────┘
```
addTupleOfIntervals
Introduced in: v22.11.0
Consecutively adds a tuple of intervals to a date or a date with time.
**Syntax**
```sql theme={null}
addTupleOfIntervals(datetime, intervals)
```
**Arguments**
* `datetime` — Date or date with time to add intervals to. [`Date`](/reference/data-types/date) or [`Date32`](/reference/data-types/date32) or [`DateTime`](/reference/data-types/datetime) or [`DateTime64`](/reference/data-types/datetime64)
* `intervals` — Tuple of intervals to add to `datetime`. [`Tuple(Interval)`](/reference/data-types/tuple)
**Returned value**
Returns `date` with added `intervals` [`Date`](/reference/data-types/date) or [`Date32`](/reference/data-types/date32) or [`DateTime`](/reference/data-types/datetime) or [`DateTime64`](/reference/data-types/datetime64)
**Examples**
**Add tuple of intervals to date**
```sql title=Query theme={null}
WITH toDate('2018-01-01') AS date
SELECT addTupleOfIntervals(date, (INTERVAL 1 DAY, INTERVAL 1 MONTH, INTERVAL 1 YEAR))
```
```response title=Response theme={null}
┌─addTupleOfIntervals(date, (toIntervalDay(1), toIntervalMonth(1), toIntervalYear(1)))─┐
│ 2019-02-02 │
└──────────────────────────────────────────────────────────────────────────────────────┘
```
addWeeks
Introduced in: v1.1.0
Adds a specified number of weeks to a date, a date with time or a string-encoded date or date with time.
**Syntax**
```sql theme={null}
addWeeks(datetime, num)
```
**Arguments**
* `datetime` — Date or date with time to add specified number of weeks to. [`Date`](/reference/data-types/date) or [`Date32`](/reference/data-types/date32) or [`DateTime`](/reference/data-types/datetime) or [`DateTime64`](/reference/data-types/datetime64) or [`String`](/reference/data-types/string)
* `num` — Number of weeks to add. [`(U)Int*`](/reference/data-types/int-uint) or [`Float*`](/reference/data-types/float)
**Returned value**
Returns `datetime` plus `num` weeks [`Date`](/reference/data-types/date) or [`Date32`](/reference/data-types/date32) or [`DateTime`](/reference/data-types/datetime) or [`DateTime64`](/reference/data-types/datetime64)
**Examples**
**Add weeks to different date types**
```sql title=Query theme={null}
WITH
toDate('2024-01-01') AS date,
toDateTime('2024-01-01 00:00:00') AS date_time,
'2024-01-01 00:00:00' AS date_time_string
SELECT
addWeeks(date, 5) AS add_weeks_with_date,
addWeeks(date_time, 5) AS add_weeks_with_date_time,
addWeeks(date_time_string, 5) AS add_weeks_with_date_time_string
```
```response title=Response theme={null}
┌─add_weeks_with_date─┬─add_weeks_with_date_time─┬─add_weeks_with_date_time_string─┐
│ 2024-02-05 │ 2024-02-05 00:00:00 │ 2024-02-05 00:00:00.000 │
└─────────────────────┴──────────────────────────┴─────────────────────────────────┘
```
**Using alternative INTERVAL syntax**
```sql title=Query theme={null}
SELECT dateAdd('1998-06-16'::Date, INTERVAL 10 week)
```
```response title=Response theme={null}
┌─plus(CAST('1⋯alWeek(10))─┐
│ 1998-08-25 │
└──────────────────────────┘
```
addYears
Introduced in: v1.1.0
Adds a specified number of years to a date, a date with time or a string-encoded date or date with time.
**Syntax**
```sql theme={null}
addYears(datetime, num)
```
**Arguments**
* `datetime` — Date or date with time to add specified number of years to. [`Date`](/reference/data-types/date) or [`Date32`](/reference/data-types/date32) or [`DateTime`](/reference/data-types/datetime) or [`DateTime64`](/reference/data-types/datetime64) or [`String`](/reference/data-types/string)
* `num` — Number of years to add. [`(U)Int*`](/reference/data-types/int-uint) or [`Float*`](/reference/data-types/float)
**Returned value**
Returns `datetime` plus `num` years [`Date`](/reference/data-types/date) or [`Date32`](/reference/data-types/date32) or [`DateTime`](/reference/data-types/datetime) or [`DateTime64`](/reference/data-types/datetime64)
**Examples**
**Add years to different date types**
```sql title=Query theme={null}
WITH
toDate('2024-01-01') AS date,
toDateTime('2024-01-01 00:00:00') AS date_time,
'2024-01-01 00:00:00' AS date_time_string
SELECT
addYears(date, 1) AS add_years_with_date,
addYears(date_time, 1) AS add_years_with_date_time,
addYears(date_time_string, 1) AS add_years_with_date_time_string
```
```response title=Response theme={null}
┌─add_years_with_date─┬─add_years_with_date_time─┬─add_years_with_date_time_string─┐
│ 2025-01-01 │ 2025-01-01 00:00:00 │ 2025-01-01 00:00:00.000 │
└─────────────────────┴──────────────────────────┴─────────────────────────────────┘
```
**Using alternative INTERVAL syntax**
```sql title=Query theme={null}
SELECT dateAdd('1998-06-16'::Date, INTERVAL 10 year)
```
```response title=Response theme={null}
┌─plus(CAST('1⋯alYear(10))─┐
│ 2008-06-16 │
└──────────────────────────┘
```
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**
```sql theme={null}
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`](/reference/data-types/date) or [`Date32`](/reference/data-types/date32) or [`DateTime`](/reference/data-types/datetime) or [`DateTime64`](/reference/data-types/datetime64)
* `enddate` — The second time value to subtract from (the minuend). [`Date`](/reference/data-types/date) or [`Date32`](/reference/data-types/date32) or [`DateTime`](/reference/data-types/datetime) or [`DateTime64`](/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`](/reference/data-types/string)
**Returned value**
Returns the difference between enddate and startdate expressed in unit. [`Int32`](/reference/data-types/int-uint)
**Examples**
**Calculate age in hours**
```sql title=Query theme={null}
SELECT age('hour', toDateTime('2018-01-01 22:30:00'), toDateTime('2018-01-02 23:00:00'))
```
```response title=Response theme={null}
┌─age('hour', toDateTime('2018-01-01 22:30:00'), toDateTime('2018-01-02 23:00:00'))─┐
│ 24 │
└───────────────────────────────────────────────────────────────────────────────────┘
```
**Calculate age in different units**
```sql title=Query theme={null}
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
```
```response title=Response theme={null}
┌──────────e─┬──────────s─┬─day_age─┬─month_age─┬─year_age─┐
│ 2022-01-01 │ 2021-12-29 │ 3 │ 0 │ 0 │
└────────────┴────────────┴─────────┴───────────┴──────────┘
```
changeDay
Introduced in: v24.7.0
Changes the day component of a date or date time.
**Syntax**
```sql theme={null}
changeDay(date_or_datetime, value)
```
**Arguments**
* `date_or_datetime` — The value to change. [`Date`](/reference/data-types/date) or [`Date32`](/reference/data-types/date32) or [`DateTime`](/reference/data-types/datetime) or [`DateTime64`](/reference/data-types/datetime64)
* `value` — The new value. [`(U)Int*`](/reference/data-types/int-uint)
**Returned value**
Returns a value of the same type as `date_or_datetime` with modified day component. [`Date`](/reference/data-types/date) or [`Date32`](/reference/data-types/date32) or [`DateTime`](/reference/data-types/datetime) or [`DateTime64`](/reference/data-types/datetime64)
**Examples**
**Usage example**
```sql title=Query theme={null}
SELECT changeDay('2024-01-31'::DateTime, 15)
```
```response title=Response theme={null}
2024-01-15 00:00:00
```
changeHour
Introduced in: v24.7.0
Changes the hour component of a date or date time.
**Syntax**
```sql theme={null}
changeHour(date_or_datetime, value)
```
**Arguments**
* `date_or_datetime` — The value to change. [`Date`](/reference/data-types/date) or [`Date32`](/reference/data-types/date32) or [`DateTime`](/reference/data-types/datetime) or [`DateTime64`](/reference/data-types/datetime64)
* `value` — The new value. [`(U)Int*`](/reference/data-types/int-uint)
**Returned value**
Returns a value of the same type as `date_or_datetime` with modified hour component. [`DateTime`](/reference/data-types/datetime) or [`DateTime64`](/reference/data-types/datetime64)
**Examples**
**Usage example**
```sql title=Query theme={null}
SELECT changeHour('2024-01-01 12:00:00'::DateTime, 5)
```
```response title=Response theme={null}
2024-01-01 05:00:00
```
changeMinute
Introduced in: v24.7.0
Changes the minute component of a `date or date time`.
**Syntax**
```sql theme={null}
changeMinute(date_or_datetime, value)
```
**Arguments**
* `date_or_datetime` — The value to change. [`Date`](/reference/data-types/date) or [`Date32`](/reference/data-types/date32) or [`DateTime`](/reference/data-types/datetime) or [`DateTime64`](/reference/data-types/datetime64)
* `value` — The new value. [`(U)Int*`](/reference/data-types/int-uint)
**Returned value**
Returns a value of the same type as `date_or_datetime` with modified minute component. [`DateTime`](/reference/data-types/datetime) or [`DateTime64`](/reference/data-types/datetime64)
**Examples**
**Usage example**
```sql title=Query theme={null}
SELECT changeMinute('2024-01-01 12:30:00'::DateTime, 45)
```
```response title=Response theme={null}
2024-01-01 12:45:00
```
changeMonth
Introduced in: v24.7.0
Changes the month component of a date or date time.
**Syntax**
```sql theme={null}
changeMonth(date_or_datetime, value)
```
**Arguments**
* `date_or_datetime` — The value to change. [`Date`](/reference/data-types/date) or [`Date32`](/reference/data-types/date32) or [`DateTime`](/reference/data-types/datetime) or [`DateTime64`](/reference/data-types/datetime64)
* `value` — The new value. [`(U)Int*`](/reference/data-types/int-uint)
**Returned value**
Returns a value of the same type as `date_or_datetime` with modified month component. [`Date`](/reference/data-types/date) or [`Date32`](/reference/data-types/date32) or [`DateTime`](/reference/data-types/datetime) or [`DateTime64`](/reference/data-types/datetime64)
**Examples**
**Usage example**
```sql title=Query theme={null}
SELECT changeMonth('2024-01-01'::DateTime, 12)
```
```response title=Response theme={null}
2024-12-01 00:00:00
```
changeSecond
Introduced in: v24.7.0
Changes the second component of a date or date time.
**Syntax**
```sql theme={null}
changeSecond(date_or_datetime, value)
```
**Arguments**
* `date_or_datetime` — The value to change. [`Date`](/reference/data-types/date) or [`Date32`](/reference/data-types/date32) or [`DateTime`](/reference/data-types/datetime) or [`DateTime64`](/reference/data-types/datetime64)
* `value` — The new value. [`(U)Int*`](/reference/data-types/int-uint)
**Returned value**
Returns a value of the same type as `date_or_datetime` with modified seconds component. [`DateTime`](/reference/data-types/datetime) or [`DateTime64`](/reference/data-types/datetime64)
**Examples**
**Usage example**
```sql title=Query theme={null}
SELECT changeSecond('2024-01-01 12:30:45'::DateTime, 15)
```
```response title=Response theme={null}
2024-01-01 12:30:15
```
changeYear
Introduced in: v24.7.0
Changes the year component of a date or date time.
**Syntax**
```sql theme={null}
changeYear(date_or_datetime, value)
```
**Arguments**
* `date_or_datetime` — The value to change. [`Date`](/reference/data-types/date) or [`Date32`](/reference/data-types/date32) or [`DateTime`](/reference/data-types/datetime) or [`DateTime64`](/reference/data-types/datetime64)
* `value` — The new value. [`(U)Int*`](/reference/data-types/int-uint)
**Returned value**
Returns a value of the same type as `date_or_datetime` with modified year component. [`Date`](/reference/data-types/date) or [`Date32`](/reference/data-types/date32) or [`DateTime`](/reference/data-types/datetime) or [`DateTime64`](/reference/data-types/datetime64)
**Examples**
**Usage example**
```sql title=Query theme={null}
SELECT changeYear('2024-01-01'::DateTime, 2023)
```
```response title=Response theme={null}
2023-01-01 00:00:00
```
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**
```sql theme={null}
dateDiff(unit, startdate, enddate[, timezone])
```
**Aliases**: `timestampDiff`, `TIMESTAMP_DIFF`, `DATE_DIFF`, `date_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`](/reference/data-types/date) or [`Date32`](/reference/data-types/date32) or [`DateTime`](/reference/data-types/datetime) or [`DateTime64`](/reference/data-types/datetime64)
* `enddate` — The second time value to subtract from (the minuend). [`Date`](/reference/data-types/date) or [`Date32`](/reference/data-types/date32) or [`DateTime`](/reference/data-types/datetime) or [`DateTime64`](/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`](/reference/data-types/string)
**Returned value**
Returns the difference between `enddate` and `startdate` expressed in `unit`. [`Int64`](/reference/data-types/int-uint)
**Examples**
**Calculate date difference in hours**
```sql title=Query theme={null}
SELECT dateDiff('hour', toDateTime('2018-01-01 22:00:00'), toDateTime('2018-01-02 23:00:00')) AS res
```
```response title=Response theme={null}
┌─res─┐
│ 25 │
└─────┘
```
**Calculate date difference in different units**
```sql title=Query theme={null}
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
```
```response title=Response theme={null}
┌──────────e─┬──────────s─┬─day_diff─┬─month_diff─┬─year_diff─┐
│ 2022-01-01 │ 2021-12-29 │ 3 │ 1 │ 1 │
└────────────┴────────────┴──────────┴────────────┴───────────┘
```
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**
```sql theme={null}
dateName(date_part, date[, timezone])
```
**Arguments**
* `date_part` — The part of the date that you want to extract. [`String`](/reference/data-types/string)
* `datetime` — A date or date with time value. [`Date`](/reference/data-types/date) or [`Date32`](/reference/data-types/date32) or [`DateTime`](/reference/data-types/datetime) or [`DateTime64`](/reference/data-types/datetime64)
* `timezone` — Optional. Timezone. [`String`](/reference/data-types/string)
**Returned value**
Returns the specified part of date. [`String`](/reference/data-types/string)
**Examples**
**Extract different date parts**
```sql title=Query theme={null}
WITH toDateTime('2021-04-14 11:22:33') AS date_value
SELECT
dateName('year', date_value),
dateName('month', date_value),
dateName('day', date_value)
```
```response title=Response theme={null}
┌─dateName('year', date_value)─┬─dateName('month', date_value)─┬─dateName('day', date_value)─┐
│ 2021 │ April │ 14 │
└──────────────────────────────┴───────────────────────────────┴─────────────────────────────┘
```
dateTrunc
Introduced in: v20.8.0
Truncates a date and time value to the specified part of the date.
**Syntax**
```sql theme={null}
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`](/reference/data-types/string)
* `datetime` — Date and time. [`Date`](/reference/data-types/date) or [`Date32`](/reference/data-types/date32) or [`DateTime`](/reference/data-types/datetime) or [`DateTime64`](/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`](/reference/data-types/string)
**Returned value**
Returns the truncated date and time value.
| Unit Argument | `datetime` Argument | Return Type |
| -------------------------- | ------------------------------------------------ | ------------------------------------------------------------------------------------------------ |
| Year, Quarter, Month, Week | `Date32` or `DateTime64` or `Date` or `DateTime` | [`Date32`](/reference/data-types/date32) or [`Date`](/reference/data-types/date) |
| Day, Hour, Minute, Second | `Date32`, `DateTime64`, `Date`, or `DateTime` | [`DateTime64`](/reference/data-types/datetime64) or [`DateTime`](/reference/data-types/datetime) |
| Millisecond, Microsecond, | Any | [`DateTime64`](/reference/data-types/datetime64) |
| Nanosecond | | with scale 3, 6, or 9 |
**Examples**
**Truncate without timezone**
```sql title=Query theme={null}
SELECT now(), dateTrunc('hour', now());
```
```response title=Response theme={null}
┌───────────────now()─┬─dateTrunc('hour', now())──┐
│ 2020-09-28 10:40:45 │ 2020-09-28 10:00:00 │
└─────────────────────┴───────────────────────────┘
```
**Truncate with specified timezone**
```sql title=Query theme={null}
SELECT now(), dateTrunc('hour', now(), 'Asia/Istanbul');
```
```response title=Response theme={null}
┌───────────────now()─┬─dateTrunc('hour', now(), 'Asia/Istanbul')──┐
│ 2020-09-28 10:46:26 │ 2020-09-28 13:00:00 │
└─────────────────────┴────────────────────────────────────────────┘
```
Introduced in: v1.1.0
Formats a date or date with time according to the given format string. `format` is a constant expression, so you cannot have multiple formats for a single result column.
`formatDateTime` uses MySQL datetime format style, refer to the [mysql docs](https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_date-format).
The opposite operation of this function is [`parseDateTime`](/reference/functions/regular-functions/type-conversion-functions#parseDateTime).
Using replacement fields, you can define a pattern for the resulting string.
The example column in the table below shows formatting result for `2018-01-02 22:33:44`.
**Replacement fields:**
| Placeholder | Description | Example |
| ----------- | ------------------------------------------------------- | ---------- |
| %a | abbreviated weekday name (Mon-Sun) | Mon |
| %b | abbreviated month name (Jan-Dec) | Jan |
| %c | month as an integer number (01-12) | 01 |
| %C | year divided by 100 and truncated to integer (00-99) | 20 |
| %d | day of the month, zero-padded (01-31) | 02 |
| %D | Short MM/DD/YY date, equivalent to %m/%d/%y | 01/02/18 |
| %e | day of the month, space-padded (1-31) | 2 |
| %f | fractional second | 123456 |
| %F | short YYYY-MM-DD date, equivalent to %Y-%m-%d | 2018-01-02 |
| %g | two-digit year format, aligned to ISO 8601 | 18 |
| %G | four-digit year format for ISO week number | 2018 |
| %h | hour in 12h format (01-12) | 09 |
| %H | hour in 24h format (00-23) | 22 |
| %i | minute (00-59) | 33 |
| %I | hour in 12h format (01-12) | 10 |
| %j | day of the year (001-366) | 002 |
| %k | hour in 24h format (00-23) | 14 |
| %l | hour in 12h format (01-12) | 09 |
| %m | month as an integer number (01-12) | 01 |
| %M | full month name (January-December) | January |
| %n | new-line character | |
| %p | AM or PM designation | PM |
| %Q | Quarter (1-4) | 1 |
| %r | 12-hour HH:MM AM/PM time, equivalent to %h:%i %p | 10:30 PM |
| %R | 24-hour HH:MM time, equivalent to %H:%i | 22:33 |
| %s | second (00-59) | 44 |
| %S | second (00-59) | 44 |
| %t | horizontal-tab character | |
| %T | ISO 8601 time format (HH:MM:SS), equivalent to %H:%i:%S | 22:33:44 |
| %u | ISO 8601 weekday as number with Monday as 1 (1-7) | 2 |
| %V | ISO 8601 week number (01-53) | 01 |
| %w | weekday as a integer number with Sunday as 0 (0-6) | 2 |
| %W | full weekday name (Monday-Sunday) | Monday |
| %y | Year, last two digits (00-99) | 18 |
| %Y | Year | 2018 |
| %z | Time offset from UTC as +HHMM or -HHMM | -0500 |
| %% | a % sign | % |
* In ClickHouse versions earlier than v23.4, `%f` prints a single zero (0) if the formatted value is a Date, Date32 or DateTime (which have no fractional seconds) or a DateTime64 with a precision of 0.
* In ClickHouse versions earlier than v25.1, `%f` prints as many digits as specified by the scale of the DateTime64 instead of fixed 6 digits.
* In ClickHouse versions earlier than v23.4, `%M` prints the minute (00-59) instead of the full month name (January-December).
**Syntax**
```sql theme={null}
formatDateTime(datetime, format[, timezone])
```
**Aliases**: `DATE_FORMAT`
**Arguments**
* `datetime` — A date or date time to format. [`Date`](/reference/data-types/date) or [`Date32`](/reference/data-types/date32) or [`DateTime`](/reference/data-types/datetime) or [`DateTime64`](/reference/data-types/datetime64)
* `format` — Format string with replacement fields. [`String`](/reference/data-types/string)
* `timezone` — Optional. Timezone name for the formatted time. [`String`](/reference/data-types/string)
**Returned value**
Returns time and date values according to the determined format. [`String`](/reference/data-types/string)
**Examples**
**Format date with year placeholder**
```sql title=Query theme={null}
SELECT formatDateTime(toDate('2010-01-04'), '%g')
```
```response title=Response theme={null}
┌─formatDateTime(toDate('2010-01-04'), '%g')─┐
│ 10 │
└────────────────────────────────────────────┘
```
**Format DateTime64 with fractional seconds**
```sql title=Query theme={null}
SELECT formatDateTime(toDateTime64('2010-01-04 12:34:56.123456', 7), '%f')
```
```response title=Response theme={null}
┌─formatDateTime(toDateTime64('2010-01-04 12:34:56.123456', 7), '%f')─┐
│ 1234560 │
└─────────────────────────────────────────────────────────────────────┘
```
**Format with timezone**
```sql title=Query theme={null}
SELECT
now() AS ts,
time_zone,
formatDateTime(ts, '%T', time_zone) AS str_tz_time
FROM system.time_zones
WHERE time_zone LIKE 'Europe%'
LIMIT 10
```
```response title=Response theme={null}
┌──────────────────ts─┬─time_zone─────────┬─str_tz_time─┐
│ 2023-09-08 19:13:40 │ Europe/Amsterdam │ 21:13:40 │
│ 2023-09-08 19:13:40 │ Europe/Andorra │ 21:13:40 │
│ 2023-09-08 19:13:40 │ Europe/Astrakhan │ 23:13:40 │
│ 2023-09-08 19:13:40 │ Europe/Athens │ 22:13:40 │
│ 2023-09-08 19:13:40 │ Europe/Belfast │ 20:13:40 │
│ 2023-09-08 19:13:40 │ Europe/Belgrade │ 21:13:40 │
│ 2023-09-08 19:13:40 │ Europe/Berlin │ 21:13:40 │
│ 2023-09-08 19:13:40 │ Europe/Bratislava │ 21:13:40 │
│ 2023-09-08 19:13:40 │ Europe/Brussels │ 21:13:40 │
│ 2023-09-08 19:13:40 │ Europe/Bucharest │ 22:13:40 │
└─────────────────────┴───────────────────┴─────────────┘
```
Introduced in: v20.1.0
Similar to `formatDateTime`, except that it formats datetime in Joda style instead of MySQL style. Refer to [Joda Time documentation](https://joda-time.sourceforge.net/apidocs/org/joda/time/format/DateTimeFormat.html).
The opposite operation of this function is [`parseDateTimeInJodaSyntax`](/reference/functions/regular-functions/type-conversion-functions#parseDateTimeInJodaSyntax).
Using replacement fields, you can define a pattern for the resulting string.
**Replacement fields:**
| Placeholder | Description | Presentation | Examples |
| ----------- | ------------------------------------ | ------------ | -------------------------- |
| G | era | text | AD |
| C | century of era (>=0) | number | 20 |
| Y | year of era (>=0) | year | 1996 |
| x | weekyear (not supported yet) | year | 1996 |
| w | week of weekyear (not supported yet) | number | 27 |
| e | day of week | number | 2 |
| E | day of week | text | Tuesday; Tue |
| y | year | year | 1996 |
| D | day of year | number | 189 |
| M | month of year | month | July; Jul; 07 |
| d | day of month | number | 10 |
| a | halfday of day | text | PM |
| K | hour of halfday (0\~11) | number | 0 |
| h | clockhour of halfday (1\~12) | number | 12 |
| H | hour of day (0\~23) | number | 0 |
| k | clockhour of day (1\~24) | number | 24 |
| m | minute of hour | number | 30 |
| s | second of minute | number | 55 |
| S | fraction of second | number | 978 |
| z | time zone | text | Eastern Standard Time; EST |
| Z | time zone offset | zone | -0800; -0812 |
| ' | escape for text | delimiter | |
| '' | single quote | literal | ' |
**Syntax**
```sql theme={null}
formatDateTimeInJodaSyntax(datetime, format[, timezone])
```
**Arguments**
* `datetime` — A date or date time to format. [`DateTime`](/reference/data-types/datetime) or [`Date`](/reference/data-types/date) or [`Date32`](/reference/data-types/date32) or [`DateTime64`](/reference/data-types/datetime64)
* `format` — Format string with Joda-style replacement fields. [`String`](/reference/data-types/string)
* `timezone` — Optional. Timezone name for the formatted time. [`String`](/reference/data-types/string)
**Returned value**
Returns time and date values according to the determined format. [`String`](/reference/data-types/string)
**Examples**
**Format datetime using Joda syntax**
```sql title=Query theme={null}
SELECT formatDateTimeInJodaSyntax(toDateTime('2010-01-04 12:34:56'), 'yyyy-MM-dd HH:mm:ss')
```
```response title=Response theme={null}
┌─formatDateTimeInJodaSyntax(toDateTime('2010-01-04 12:34:56'), 'yyyy-MM-dd HH:mm:ss')─┐
│ 2010-01-04 12:34:56 │
└─────────────────────────────────────────────────────────────────────────────────────────┘
```
fromDaysSinceYearZero
Introduced in: v23.11.0
For a given number of days elapsed since [1 January 0000](https://en.wikipedia.org/wiki/Year_zero), returns the corresponding date in the [proleptic Gregorian calendar defined by ISO 8601](https://en.wikipedia.org/wiki/Gregorian_calendar#Proleptic_Gregorian_calendar).
The calculation is the same as in MySQL's `FROM_DAYS()` function. The result is undefined if it cannot be represented within the bounds of the [Date](/reference/data-types/date) type.
**Syntax**
```sql theme={null}
fromDaysSinceYearZero(days)
```
**Aliases**: `FROM_DAYS`
**Arguments**
* `days` — The number of days passed since year zero. [`UInt32`](/reference/data-types/int-uint)
**Returned value**
Returns the date corresponding to the number of days passed since year zero. [`Date`](/reference/data-types/date)
**Examples**
**Convert days since year zero to dates**
```sql title=Query theme={null}
SELECT
fromDaysSinceYearZero(739136) AS date1,
fromDaysSinceYearZero(toDaysSinceYearZero(toDate('2023-09-08'))) AS date2
```
```response title=Response theme={null}
┌──────date1─┬──────date2─┐
│ 2023-09-08 │ 2023-09-08 │
└────────────┴────────────┘
```
fromDaysSinceYearZero32
Introduced in: v23.11.0
For a given number of days elapsed since [1 January 0000](https://en.wikipedia.org/wiki/Year_zero), returns the corresponding date in the [proleptic Gregorian calendar defined by ISO 8601](https://en.wikipedia.org/wiki/Gregorian_calendar#Proleptic_Gregorian_calendar).
The calculation is the same as in MySQL's `FROM_DAYS()` function. The result is undefined if it cannot be represented within the bounds of the [`Date32`](/reference/data-types/date32) type.
**Syntax**
```sql theme={null}
fromDaysSinceYearZero32(days)
```
**Arguments**
* `days` — The number of days passed since year zero. [`UInt32`](/reference/data-types/int-uint)
**Returned value**
Returns the date corresponding to the number of days passed since year zero. [`Date32`](/reference/data-types/date32)
**Examples**
**Convert days since year zero to dates**
```sql title=Query theme={null}
SELECT
fromDaysSinceYearZero32(739136) AS date1,
fromDaysSinceYearZero32(toDaysSinceYearZero(toDate('2023-09-08'))) AS date2
```
```response title=Response theme={null}
┌──────date1─┬──────date2─┐
│ 2023-09-08 │ 2023-09-08 │
└────────────┴────────────┘
```
fromModifiedJulianDay
Introduced in: v21.1.0
Converts a [Modified Julian Day](https://en.wikipedia.org/wiki/Julian_day#Variants) number to a [Proleptic Gregorian calendar](https://en.wikipedia.org/wiki/Proleptic_Gregorian_calendar) date in text form `YYYY-MM-DD`. This function supports day number from `-678941` to `2973483` (which represent 0000-01-01 and 9999-12-31 respectively). It raises an exception if the day number is outside of the supported range.
**Syntax**
```sql theme={null}
fromModifiedJulianDay(day)
```
**Arguments**
* `day` — Modified Julian Day number. [`(U)Int*`](/reference/data-types/int-uint)
**Returned value**
Returns date in text form. [`String`](/reference/data-types/string)
**Examples**
**Convert Modified Julian Day to date**
```sql title=Query theme={null}
SELECT fromModifiedJulianDay(58849)
```
```response title=Response theme={null}
┌─fromModifiedJulianDay(58849)─┐
│ 2020-01-01 │
└──────────────────────────────┘
```
fromModifiedJulianDayOrNull
Introduced in: v21.1.0
Similar to [`fromModifiedJulianDay()`](#fromModifiedJulianDay), but instead of raising exceptions it returns `NULL`.
**Syntax**
```sql theme={null}
fromModifiedJulianDayOrNull(day)
```
**Arguments**
* `day` — Modified Julian Day number. [`(U)Int*`](/reference/data-types/int-uint)
**Returned value**
Returns date in text form for valid `day` argument, otherwise `null`. [`Nullable(String)`](/reference/data-types/nullable)
**Examples**
**Convert Modified Julian Day to date with null handling**
```sql title=Query theme={null}
SELECT fromModifiedJulianDayOrNull(58849);
SELECT fromModifiedJulianDayOrNull(60000000); -- invalid argument, returns NULL
```
```response title=Response theme={null}
┌─fromModified⋯Null(58849)─┐
│ 2020-01-01 │
└──────────────────────────┘
┌─fromModified⋯l(60000000)─┐
│ ᴺᵁᴸᴸ │
└──────────────────────────┘
```
fromUTCTimestamp
Introduced in: v22.1.0
Converts a date or date with time value from UTC timezone to a date or date with time value with the specified time zone. This function is mainly included for compatibility with Apache Spark and similar frameworks.
**Syntax**
```sql theme={null}
fromUTCTimestamp(datetime, time_zone)
```
**Aliases**: `from_utc_timestamp`
**Arguments**
* `datetime` — A date or date with time const value or an expression. [`DateTime`](/reference/data-types/datetime) or [`DateTime64`](/reference/data-types/datetime64)
* `time_zone` — A String type const value or an expression representing the time zone. [`String`](/reference/data-types/string)
**Returned value**
Returns DateTime/DateTime64 in the specified timezone. [`DateTime`](/reference/data-types/datetime) or [`DateTime64`](/reference/data-types/datetime64)
**Examples**
**Convert UTC timezone to specified timezone**
```sql title=Query theme={null}
SELECT fromUTCTimestamp(toDateTime64('2023-03-16 10:00:00', 3), 'Asia/Shanghai')
```
```response title=Response theme={null}
┌─fromUTCTimestamp(toDateTime64('2023-03-16 10:00:00',3), 'Asia/Shanghai')─┐
│ 2023-03-16 18:00:00.000 │
└─────────────────────────────────────────────────────────────────────────┘
```
fromUnixTimestamp
Introduced in: v20.8.0
This function converts a Unix timestamp to a calendar date and a time of a day.
It can be called in two ways:
* When given a single argument of type [`Integer`](/reference/data-types/int-uint), it returns a value of type [`DateTime`](/reference/data-types/datetime), i.e. behaves like [`toDateTime`](/reference/functions/regular-functions/type-conversion-functions#toDateTime).
* When given two or three arguments where the first argument is a value of type [`Integer`](/reference/data-types/int-uint), [`Date`](/reference/data-types/date), [`Date32`](/reference/data-types/date32), [`DateTime`](/reference/data-types/datetime) or [`DateTime64`](/reference/data-types/datetime64), the second argument is a constant format string and the third argument is an optional constant time zone string, the function returns a value of type [`String`](/reference/data-types/string), i.e. it behaves like [`formatDateTime`](#formatDateTime).
In this case, [MySQL's datetime format style](https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_date-format) is used.
**Syntax**
```sql theme={null}
fromUnixTimestamp(timestamp)
fromUnixTimestamp(timestamp[, format[, timezone]])
```
**Aliases**: `FROM_UNIXTIME`
**Arguments**
* `timestamp` — Unix timestamp or date/date with time value. [`(U)Int*`](/reference/data-types/int-uint) or [`Date`](/reference/data-types/date) or [`Date32`](/reference/data-types/date32) or [`DateTime`](/reference/data-types/datetime) or [`DateTime64`](/reference/data-types/datetime64)
* `format` — Optional. Constant format string for output formatting. [`String`](/reference/data-types/string)
* `timezone` — Optional. Constant time zone string. [`String`](/reference/data-types/string)
**Returned value**
Returns `DateTime` of the timestamp when called with one argument, or a String when called with two or three arguments. [`DateTime`](/reference/data-types/datetime) or [`String`](/reference/data-types/string)
**Examples**
**Convert Unix timestamp to DateTime**
```sql title=Query theme={null}
SELECT fromUnixTimestamp(423543535)
```
```response title=Response theme={null}
┌─fromUnixTimestamp(423543535)─┐
│ 1983-06-04 10:58:55 │
└──────────────────────────────┘
```
**Convert Unix timestamp with format**
```sql title=Query theme={null}
SELECT fromUnixTimestamp(1234334543, '%Y-%m-%d %R:%S') AS DateTime
```
```response title=Response theme={null}
┌─DateTime────────────┐
│ 2009-02-11 14:42:23 │
└─────────────────────┘
```
fromUnixTimestampInJodaSyntax
Introduced in: v23.1.0
This function converts a Unix timestamp to a calendar date and a time of a day.
It can be called in two ways:
When given a single argument of type [`Integer`](/reference/data-types/int-uint), it returns a value of type [`DateTime`](/reference/data-types/datetime), i.e. behaves like [`toDateTime`](/reference/functions/regular-functions/type-conversion-functions#toDateTime).
When given two or three arguments where the first argument is a value of type [`Integer`](/reference/data-types/int-uint), [`Date`](/reference/data-types/date), [`Date32`](/reference/data-types/date32), [`DateTime`](/reference/data-types/datetime) or [`DateTime64`](/reference/data-types/datetime64), the second argument is a constant format string and the third argument is an optional constant time zone string, the function returns a value of type [`String`](/reference/data-types/string), i.e. it behaves like [`formatDateTimeInJodaSyntax`](#formatDateTimeInJodaSyntax). In this case, [Joda datetime format style](https://joda-time.sourceforge.net/apidocs/org/joda/time/format/DateTimeFormat.html) is used.
**Syntax**
```sql theme={null}
fromUnixTimestampInJodaSyntax(timestamp)
fromUnixTimestampInJodaSyntax(timestamp, format[, timezone])
```
**Arguments**
* `timestamp` — Unix timestamp or date/time value. [`(U)Int*`](/reference/data-types/int-uint) or [`Date`](/reference/data-types/date) or [`Date32`](/reference/data-types/date32) or [`DateTime`](/reference/data-types/datetime) or [`DateTime64`](/reference/data-types/datetime64)
* `format` — Optional. Constant format string using Joda syntax for output formatting. [`String`](/reference/data-types/string)
* `timezone` — Optional. Constant time zone string. [`String`](/reference/data-types/string)
**Returned value**
Returns a date with time when called with one argument, or a String when called with two or three arguments.} [`DateTime`](/reference/data-types/datetime) or [`String`](/reference/data-types/string)
**Examples**
**Convert Unix timestamp with Joda format**
```sql title=Query theme={null}
SELECT fromUnixTimestampInJodaSyntax(1234334543, 'yyyy-MM-dd HH:mm:ss', 'UTC') AS DateTime
```
```response title=Response theme={null}
┌─DateTime────────────┐
│ 2009-02-11 06:42:23 │
└─────────────────────┘
```
makeDate
Introduced in: v22.6.0
Creates a `Date` from either:
* a year, month and day
* a year and day of year
**Syntax**
```sql theme={null}
makeDate(year, month, day)
makeDate(year, day_of_year)
```
**Arguments**
* `year` — Year number. [`(U)Int*`](/reference/data-types/int-uint) or [`Float*`](/reference/data-types/float) or [`Decimal`](/reference/data-types/decimal)
* `month` — Month number (1-12). [`(U)Int*`](/reference/data-types/int-uint) or [`Float*`](/reference/data-types/float) or [`Decimal`](/reference/data-types/decimal)
* `day` — Day of the month (1-31). [`(U)Int*`](/reference/data-types/int-uint) or [`Float*`](/reference/data-types/float) or [`Decimal`](/reference/data-types/decimal)
* `day_of_year` — Day of the year (1-365). [`(U)Int*`](/reference/data-types/int-uint) or [`Float*`](/reference/data-types/float) or [`Decimal`](/reference/data-types/decimal)
**Returned value**
Returns a `Date` value constructed from the provided arguments [`Date`](/reference/data-types/date)
**Examples**
**Date from a year, month, day**
```sql title=Query theme={null}
SELECT makeDate(2023, 2, 28) AS date;
```
```response title=Response theme={null}
┌───────date─┐
│ 2023-02-28 │
└────────────┘
```
**Date from year and day of year**
```sql title=Query theme={null}
SELECT makeDate(2023, 42) AS date;
```
```response title=Response theme={null}
┌───────date─┐
│ 2023-02-11 │
└────────────┘
```
makeDate32
Introduced in: v22.6.0
Creates a `Date32` from either:
* a year, month and day
* a year and day of year
**Syntax**
```sql theme={null}
makeDate32(year, month, day)
makeDate32(year, day_of_year)
```
**Arguments**
* `year` — Year number. [`(U)Int*`](/reference/data-types/int-uint) or [`Float*`](/reference/data-types/float) or [`Decimal`](/reference/data-types/decimal)
* `month` — Month number (1-12). [`(U)Int*`](/reference/data-types/int-uint) or [`Float*`](/reference/data-types/float) or [`Decimal`](/reference/data-types/decimal)
* `day` — Day of the month (1-31). [`(U)Int*`](/reference/data-types/int-uint) or [`Float*`](/reference/data-types/float) or [`Decimal`](/reference/data-types/decimal)
* `day_of_year` — Day of the year (1-365). [`(U)Int*`](/reference/data-types/int-uint) or [`Float*`](/reference/data-types/float) or [`Decimal`](/reference/data-types/decimal)
**Returned value**
Returns a `Date32` value constructed from the provided arguments [`Date32`](/reference/data-types/date32)
**Examples**
**Date32 from a year, month, day**
```sql title=Query theme={null}
SELECT makeDate(2023, 2, 28) AS date;
```
```response title=Response theme={null}
┌───────date─┐
│ 2023-02-28 │
└────────────┘
```
**Date32 from year and day of year**
```sql title=Query theme={null}
SELECT makeDate(2023, 42) AS date;
```
```response title=Response theme={null}
┌───────date─┐
│ 2023-02-11 │
└────────────┘
```
makeDateTime
Introduced in: v22.6.0
Creates a `DateTime` from year, month, day, hour, minute, and second, with optional timezone.
**Syntax**
```sql theme={null}
makeDateTime(year, month, day, hour, minute, second[, timezone])
```
**Arguments**
* `year` — Year number. [`(U)Int*`](/reference/data-types/int-uint) or [`Float*`](/reference/data-types/float) or [`Decimal`](/reference/data-types/decimal)
* `month` — Month number (1-12). [`(U)Int*`](/reference/data-types/int-uint) or [`Float*`](/reference/data-types/float) or [`Decimal`](/reference/data-types/decimal)
* `day` — Day of the month (1-31). [`(U)Int*`](/reference/data-types/int-uint) or [`Float*`](/reference/data-types/float) or [`Decimal`](/reference/data-types/decimal)
* `hour` — Hour (0-23). [`(U)Int*`](/reference/data-types/int-uint) or [`Float*`](/reference/data-types/float) or [`Decimal`](/reference/data-types/decimal)
* `minute` — Minute (0-59). [`(U)Int*`](/reference/data-types/int-uint) or [`Float*`](/reference/data-types/float) or [`Decimal`](/reference/data-types/decimal)
* `second` — Second (0-59). [`(U)Int*`](/reference/data-types/int-uint) or [`Float*`](/reference/data-types/float) or [`Decimal`](/reference/data-types/decimal)
* `timezone` — Timezone name. [`String`](/reference/data-types/string)
**Returned value**
Returns a `DateTime` value constructed from the provided arguments [`DateTime`](/reference/data-types/datetime)
**Examples**
**DateTime from year, month, day, hour, minute, second**
```sql title=Query theme={null}
SELECT makeDateTime(2023, 2, 28, 17, 12, 33) AS DateTime;
```
```response title=Response theme={null}
┌────────────DateTime─┐
│ 2023-02-28 17:12:33 │
└─────────────────────┘
```
makeDateTime64
Introduced in: v22.6.0
Creates a `DateTime64` from year, month, day, hour, minute, second, with optional fraction, precision, and timezone.
**Syntax**
```sql theme={null}
makeDateTime64(year, month, day, hour, minute, second[, fraction[, precision[, timezone]]])
```
**Arguments**
* `year` — Year number. [`(U)Int*`](/reference/data-types/int-uint) or [`Float*`](/reference/data-types/float) or [`Decimal`](/reference/data-types/decimal)
* `month` — Month number (1-12). [`(U)Int*`](/reference/data-types/int-uint) or [`Float*`](/reference/data-types/float) or [`Decimal`](/reference/data-types/decimal)
* `day` — Day of the month (1-31). [`(U)Int*`](/reference/data-types/int-uint) or [`Float*`](/reference/data-types/float) or [`Decimal`](/reference/data-types/decimal)
* `hour` — Hour (0-23). [`(U)Int*`](/reference/data-types/int-uint) or [`Float*`](/reference/data-types/float) or [`Decimal`](/reference/data-types/decimal)
* `minute` — Minute (0-59). [`(U)Int*`](/reference/data-types/int-uint) or [`Float*`](/reference/data-types/float) or [`Decimal`](/reference/data-types/decimal)
* `second` — Second (0-59). [`(U)Int*`](/reference/data-types/int-uint) or [`Float*`](/reference/data-types/float) or [`Decimal`](/reference/data-types/decimal)
* `fraction` — Fractional part of the second. [`(U)Int*`](/reference/data-types/int-uint) or [`Float*`](/reference/data-types/float) or [`Decimal`](/reference/data-types/decimal)
* `precision` — Precision for the fractional part (0-9). [`UInt8`](/reference/data-types/int-uint)
* `timezone` — Timezone name. [`String`](/reference/data-types/string)
**Returned value**
Returns a `DateTime64` value constructed from the provided arguments [`DateTime64`](/reference/data-types/datetime64)
**Examples**
**DateTime64 from year, month, day, hour, minute, second**
```sql title=Query theme={null}
SELECT makeDateTime64(2023, 5, 15, 10, 30, 45, 779, 5);
```
```response title=Response theme={null}
┌─makeDateTime64(2023, 5, 15, 10, 30, 45, 779, 5)─┐
│ 2023-05-15 10:30:45.00779 │
└─────────────────────────────────────────────────┘
```
monthName
Introduced in: v22.1.0
Returns the name of the month as a string from a date or date with time value.
**Syntax**
```sql theme={null}
monthName(datetime)
```
**Arguments**
* `datetime` — Date or date with time. [`Date`](/reference/data-types/date) or [`Date32`](/reference/data-types/date32) or [`DateTime`](/reference/data-types/datetime) or [`DateTime64`](/reference/data-types/datetime64)
**Returned value**
Returns the name of the month. [`String`](/reference/data-types/string)
**Examples**
**Get month name from date**
```sql title=Query theme={null}
WITH toDateTime('2021-04-14 11:22:33') AS date_value
SELECT monthName(date_value)
```
```response title=Response theme={null}
┌─monthName(date_value)─┐
│ April │
└───────────────────────┘
```
now
Introduced in: v1.1.0
Returns the current date and time at the moment of query analysis. The function is a constant expression.
**Syntax**
```sql theme={null}
now([timezone])
```
**Aliases**: `current_timestamp`
**Arguments**
* `timezone` — Optional. Timezone name for the returned value. [`String`](/reference/data-types/string)
**Returned value**
Returns the current date and time. [`DateTime`](/reference/data-types/datetime)
**Examples**
**Query without timezone**
```sql title=Query theme={null}
SELECT now()
```
```response title=Response theme={null}
┌───────────────now()─┐
│ 2020-10-17 07:42:09 │
└─────────────────────┘
```
**Query with specified timezone**
```sql title=Query theme={null}
SELECT now('Asia/Istanbul')
```
```response title=Response theme={null}
┌─now('Asia/Istanbul')─┐
│ 2020-10-17 10:42:23 │
└──────────────────────┘
```
**SQL standard syntax without parentheses**
```sql title=Query theme={null}
SELECT NOW, CURRENT_TIMESTAMP
```
```response title=Response theme={null}
┌─────────────────NOW─┬───CURRENT_TIMESTAMP─┐
│ 2020-10-17 07:42:19 │ 2020-10-17 07:42:19 │
└─────────────────────┴─────────────────────┘
```
now64
Introduced in: v20.1.0
Returns the current date and time with sub-second precision at the moment of query analysis. The function is a constant expression.
**Syntax**
```sql theme={null}
now64([scale[, timezone]])
```
**Arguments**
* `scale` — Optional. Tick size (precision): 10^-precision seconds. Valid range: \[0 : 9]. Typically, are used - 3 (default) (milliseconds), 6 (microseconds), 9 (nanoseconds). [`UInt8`](/reference/data-types/int-uint)
* `timezone` — Optional. Timezone name for the returned value. [`String`](/reference/data-types/string)
**Returned value**
Returns current date and time with sub-second precision. [`DateTime64`](/reference/data-types/datetime64)
**Examples**
**Query with default and custom precision**
```sql title=Query theme={null}
SELECT now64(), now64(9, 'Asia/Istanbul')
```
```response title=Response theme={null}
┌─────────────────now64()─┬─────now64(9, 'Asia/Istanbul')─┐
│ 2022-08-21 19:34:26.196 │ 2022-08-21 22:34:26.196542766 │
└─────────────────────────┴───────────────────────────────┘
```
nowInBlock
Introduced in: v22.8.0
Returns the current date and time at the moment of processing of each block of data. In contrast to the function [`now`](#now), it is not a constant expression, and the returned value will be different in different blocks for long-running queries.
It makes sense to use this function to generate the current time in long-running `INSERT SELECT` queries.
**Syntax**
```sql theme={null}
nowInBlock([timezone])
```
**Arguments**
* `timezone` — Optional. Timezone name for the returned value. [`String`](/reference/data-types/string)
**Returned value**
Returns the current date and time at the moment of processing of each block of data. [`DateTime`](/reference/data-types/datetime)
**Examples**
**Difference with the now() function**
```sql title=Query theme={null}
SELECT
now(),
nowInBlock(),
sleep(1)
FROM numbers(3)
SETTINGS max_block_size = 1
FORMAT PrettyCompactMonoBlock
```
```response title=Response theme={null}
┌───────────────now()─┬────────nowInBlock()─┬─sleep(1)─┐
│ 2022-08-21 19:41:19 │ 2022-08-21 19:41:19 │ 0 │
│ 2022-08-21 19:41:19 │ 2022-08-21 19:41:20 │ 0 │
│ 2022-08-21 19:41:19 │ 2022-08-21 19:41:21 │ 0 │
└─────────────────────┴─────────────────────┴──────────┘
```
nowInBlock64
Introduced in: v25.8.0
Returns the current date and time at the moment of processing of each block of data in milliseconds. In contrast to the function [now64](#now64), it is not a constant expression, and the returned value will be different in different blocks for long-running queries.
It makes sense to use this function to generate the current time in long-running INSERT SELECT queries.
**Syntax**
```sql theme={null}
nowInBlock64([scale[, timezone]])
```
**Arguments**
* `scale` — Optional. Tick size (precision): 10^-precision seconds. Valid range: \[0 : 9]. Typically, are used - 3 (default) (milliseconds), 6 (microseconds), 9 (nanoseconds). [`UInt8`](/reference/data-types/int-uint)
* `timezone` — Optional. Timezone name for the returned value. [`String`](/reference/data-types/string)
**Returned value**
Returns the current date and time at the moment of processing of each block of data with sub-second precision. [`DateTime64`](/reference/data-types/datetime64)
**Examples**
**Difference with the now64() function**
```sql title=Query theme={null}
SELECT
now64(),
nowInBlock64(),
sleep(1)
FROM numbers(3)
SETTINGS max_block_size = 1
FORMAT PrettyCompactMonoBlock
```
```response title=Response theme={null}
┌─────────────────now64()─┬──────────nowInBlock64()─┬─sleep(1)─┐
│ 2025-07-29 17:07:29.526 │ 2025-07-29 17:07:29.534 │ 0 │
│ 2025-07-29 17:07:29.526 │ 2025-07-29 17:07:30.535 │ 0 │
│ 2025-07-29 17:07:29.526 │ 2025-07-29 17:07:31.535 │ 0 │
└─────────────────────────┴─────────────────────────┴──────────┘
```
serverTimezone
Introduced in: v23.6.0
Returns the timezone of the server, i.e. the value of the [`timezone`](/reference/settings/server-settings/settings#timezone) setting.
If the function is executed in the context of a distributed table, then it generates a normal column with values relevant to each shard. Otherwise, it produces a constant value.
**Syntax**
```sql theme={null}
serverTimezone()
```
**Aliases**: `serverTimeZone`
**Arguments**
* None.
**Returned value**
Returns the server timezone as a [`String`](/reference/data-types/string)
**Examples**
**Usage example**
```sql title=Query theme={null}
SELECT serverTimeZone()
```
```response title=Response theme={null}
┌─serverTimeZone()─┐
│ UTC │
└──────────────────┘
```
subDate
Introduced in: v23.9.0
Subtracts the time interval from the provided date, date with time or string-encoded date or date with time.
If the subtraction results in a value outside the bounds of the data type, the result is undefined.
**Syntax**
```sql theme={null}
subDate(datetime, interval)
```
**Arguments**
* `datetime` — The date or date with time from which `interval` is subtracted. [`Date`](/reference/data-types/date) or [`Date32`](/reference/data-types/date32) or [`DateTime`](/reference/data-types/datetime) or [`DateTime64`](/reference/data-types/datetime64)
* `interval` — Interval to subtract. [`Interval`](/reference/data-types/int-uint)
**Returned value**
Returns date or date with time obtained by subtracting `interval` from `datetime`. [`Date`](/reference/data-types/date) or [`Date32`](/reference/data-types/date32) or [`DateTime`](/reference/data-types/datetime) or [`DateTime64`](/reference/data-types/datetime64)
**Examples**
**Subtract interval from date**
```sql title=Query theme={null}
SELECT subDate(toDate('2018-01-01'), INTERVAL 3 YEAR)
```
```response title=Response theme={null}
┌─subDate(toDate('2018-01-01'), toIntervalYear(3))─┐
│ 2015-01-01 │
└──────────────────────────────────────────────────┘
```
subtractDays
Introduced in: v1.1.0
Subtracts a specified number of days from a date, a date with time or a string-encoded date or date with time.
**Syntax**
```sql theme={null}
subtractDays(datetime, num)
```
**Arguments**
* `datetime` — Date or date with time to subtract specified number of days from. [`Date`](/reference/data-types/date) or [`Date32`](/reference/data-types/date32) or [`DateTime`](/reference/data-types/datetime) or [`DateTime64`](/reference/data-types/datetime64) or [`String`](/reference/data-types/string)
* `num` — Number of days to subtract. [`(U)Int*`](/reference/data-types/int-uint) or [`Float*`](/reference/data-types/float)
**Returned value**
Returns `datetime` minus `num` days [`Date`](/reference/data-types/date) or [`Date32`](/reference/data-types/date32) or [`DateTime`](/reference/data-types/datetime) or [`DateTime64`](/reference/data-types/datetime64)
**Examples**
**Subtract days from different date types**
```sql title=Query theme={null}
WITH
toDate('2024-01-01') AS date,
toDateTime('2024-01-01 00:00:00') AS date_time,
'2024-01-01 00:00:00' AS date_time_string
SELECT
subtractDays(date, 31) AS subtract_days_with_date,
subtractDays(date_time, 31) AS subtract_days_with_date_time,
subtractDays(date_time_string, 31) AS subtract_days_with_date_time_string
```
```response title=Response theme={null}
┌─subtract_days_with_date─┬─subtract_days_with_date_time─┬─subtract_days_with_date_time_string─┐
│ 2023-12-01 │ 2023-12-01 00:00:00 │ 2023-12-01 00:00:00.000 │
└─────────────────────────┴──────────────────────────────┴─────────────────────────────────────┘
```
**Using alternative INTERVAL syntax**
```sql title=Query theme={null}
SELECT dateSub('1998-06-16'::Date, INTERVAL 10 day)
```
```response title=Response theme={null}
┌─minus(CAST('⋯valDay(10))─┐
│ 1998-06-06 │
└──────────────────────────┘
```
subtractHours
Introduced in: v1.1.0
Subtracts a specified number of hours from a date, a date with time or a string-encoded date or date with time.
**Syntax**
```sql theme={null}
subtractHours(datetime, num)
```
**Arguments**
* `datetime` — Date or date with time to subtract specified number of hours from. [`Date`](/reference/data-types/date) or [`Date32`](/reference/data-types/date32) or [`DateTime`](/reference/data-types/datetime) or [`DateTime64`](/reference/data-types/datetime64) or [`String`](/reference/data-types/string)
* `num` — Number of hours to subtract. [`(U)Int*`](/reference/data-types/int-uint) or [`Float*`](/reference/data-types/float)
**Returned value**
Returns `datetime` minus `num` hours [`DateTime`](/reference/data-types/datetime) or [`DateTime64(3)`](/reference/data-types/datetime64)
**Examples**
**Subtract hours from different date types**
```sql title=Query theme={null}
WITH
toDate('2024-01-01') AS date,
toDateTime('2024-01-01 00:00:00') AS date_time,
'2024-01-01 00:00:00' AS date_time_string
SELECT
subtractHours(date, 12) AS subtract_hours_with_date,
subtractHours(date_time, 12) AS subtract_hours_with_date_time,
subtractHours(date_time_string, 12) AS subtract_hours_with_date_time_string
```
```response title=Response theme={null}
┌─subtract_hours_with_date─┬─subtract_hours_with_date_time─┬─subtract_hours_with_date_time_string─┐
│ 2023-12-31 12:00:00 │ 2023-12-31 12:00:00 │ 2023-12-31 12:00:00.000 │
└──────────────────────────┴───────────────────────────────┴──────────────────────────────────────┘
```
**Using alternative INTERVAL syntax**
```sql title=Query theme={null}
SELECT dateSub('1998-06-16'::Date, INTERVAL 10 hour)
```
```response title=Response theme={null}
┌─minus(CAST('⋯alHour(10))─┐
│ 1998-06-15 14:00:00 │
└──────────────────────────┘
```
subtractInterval
Introduced in: v22.11.0
Adds a negated interval to another interval or tuple of intervals.
Note: Intervals of the same type will be combined into a single interval. For instance if `toIntervalDay(2)` and `toIntervalDay(1)` are
passed then the result will be `(1)` rather than `(2,1)`.
**Syntax**
```sql theme={null}
subtractInterval(interval_1, interval_2)
```
**Arguments**
* `interval_1` — First interval or interval of tuples. [`Interval`](/reference/data-types/int-uint) or [`Tuple(Interval)`](/reference/data-types/tuple)
* `interval_2` — Second interval to be negated. [`Interval`](/reference/data-types/int-uint)
**Returned value**
Returns a tuple of intervals [`Tuple(T)`](/reference/data-types/tuple)
**Examples**
**Subtract intervals**
```sql title=Query theme={null}
SELECT subtractInterval(INTERVAL 1 DAY, INTERVAL 1 MONTH);
SELECT subtractInterval((INTERVAL 1 DAY, INTERVAL 1 YEAR), INTERVAL 1 MONTH);
SELECT subtractInterval(INTERVAL 2 DAY, INTERVAL 1 DAY);
```
```response title=Response theme={null}
┌─subtractInterval(toIntervalDay(1), toIntervalMonth(1))─┐
│ (1,-1) │
└────────────────────────────────────────────────────────┘
┌─subtractInterval((toIntervalDay(1), toIntervalYear(1)), toIntervalMonth(1))─┐
│ (1,1,-1) │
└─────────────────────────────────────────────────────────────────────────────┘
┌─subtractInterval(toIntervalDay(2), toIntervalDay(1))─┐
│ (1) │
└──────────────────────────────────────────────────────┘
```
subtractMicroseconds
Introduced in: v22.6.0
Subtracts a specified number of microseconds from a date with time or a string-encoded date with time.
**Syntax**
```sql theme={null}
subtractMicroseconds(datetime, num)
```
**Arguments**
* `datetime` — Date with time to subtract specified number of microseconds from. [`DateTime`](/reference/data-types/datetime) or [`DateTime64`](/reference/data-types/datetime64) or [`String`](/reference/data-types/string)
* `num` — Number of microseconds to subtract. [`(U)Int*`](/reference/data-types/int-uint) or [`Float*`](/reference/data-types/float)
**Returned value**
Returns `datetime` minus `num` microseconds [`DateTime64`](/reference/data-types/datetime64)
**Examples**
**Subtract microseconds from different date time types**
```sql title=Query theme={null}
WITH
toDateTime('2024-01-01 00:00:00') AS date_time,
'2024-01-01 00:00:00' AS date_time_string
SELECT
subtractMicroseconds(date_time, 1000000) AS subtract_microseconds_with_date_time,
subtractMicroseconds(date_time_string, 1000000) AS subtract_microseconds_with_date_time_string
```
```response title=Response theme={null}
┌─subtract_microseconds_with_date_time─┬─subtract_microseconds_with_date_time_string─┐
│ 2023-12-31 23:59:59.000000 │ 2023-12-31 23:59:59.000000 │
└──────────────────────────────────────┴─────────────────────────────────────────────┘
```
**Using alternative INTERVAL syntax**
```sql title=Query theme={null}
SELECT dateSub('1998-06-16'::DateTime, INTERVAL 10 microsecond)
```
```response title=Response theme={null}
┌─minus(CAST('1⋯osecond(10))─┐
│ 1998-06-15 23:59:59.999990 │
└────────────────────────────┘
```
subtractMilliseconds
Introduced in: v22.6.0
Subtracts a specified number of milliseconds from a date with time or a string-encoded date with time.
**Syntax**
```sql theme={null}
subtractMilliseconds(datetime, num)
```
**Arguments**
* `datetime` — Date with time to subtract specified number of milliseconds from. [`DateTime`](/reference/data-types/datetime) or [`DateTime64`](/reference/data-types/datetime64) or [`String`](/reference/data-types/string)
* `num` — Number of milliseconds to subtract. [`(U)Int*`](/reference/data-types/int-uint) or [`Float*`](/reference/data-types/float)
**Returned value**
Returns `datetime` minus `num` milliseconds [`DateTime64`](/reference/data-types/datetime64)
**Examples**
**Subtract milliseconds from different date time types**
```sql title=Query theme={null}
WITH
toDateTime('2024-01-01 00:00:00') AS date_time,
'2024-01-01 00:00:00' AS date_time_string
SELECT
subtractMilliseconds(date_time, 1000) AS subtract_milliseconds_with_date_time,
subtractMilliseconds(date_time_string, 1000) AS subtract_milliseconds_with_date_time_string
```
```response title=Response theme={null}
┌─subtract_milliseconds_with_date_time─┬─subtract_milliseconds_with_date_time_string─┐
│ 2023-12-31 23:59:59.000 │ 2023-12-31 23:59:59.000 │
└──────────────────────────────────────┴─────────────────────────────────────────────┘
```
**Using alternative INTERVAL syntax**
```sql title=Query theme={null}
SELECT dateSub('1998-06-16'::DateTime, INTERVAL 10 millisecond)
```
```response title=Response theme={null}
┌─minus(CAST('⋯second(10))─┐
│ 1998-06-15 23:59:59.990 │
└──────────────────────────┘
```
subtractMinutes
Introduced in: v1.1.0
Subtracts a specified number of minutes from a date, a date with time or a string-encoded date or date with time.
**Syntax**
```sql theme={null}
subtractMinutes(datetime, num)
```
**Arguments**
* `datetime` — Date or date with time to subtract specified number of minutes from. [`Date`](/reference/data-types/date) or [`Date32`](/reference/data-types/date32) or [`DateTime`](/reference/data-types/datetime) or [`DateTime64`](/reference/data-types/datetime64) or [`String`](/reference/data-types/string)
* `num` — Number of minutes to subtract. [`(U)Int*`](/reference/data-types/int-uint) or [`Float*`](/reference/data-types/float)
**Returned value**
Returns `datetime` minus `num` minutes [`DateTime`](/reference/data-types/datetime) or [`DateTime64(3)`](/reference/data-types/datetime64)
**Examples**
**Subtract minutes from different date types**
```sql title=Query theme={null}
WITH
toDate('2024-01-01') AS date,
toDateTime('2024-01-01 00:00:00') AS date_time,
'2024-01-01 00:00:00' AS date_time_string
SELECT
subtractMinutes(date, 30) AS subtract_minutes_with_date,
subtractMinutes(date_time, 30) AS subtract_minutes_with_date_time,
subtractMinutes(date_time_string, 30) AS subtract_minutes_with_date_time_string
```
```response title=Response theme={null}
┌─subtract_minutes_with_date─┬─subtract_minutes_with_date_time─┬─subtract_minutes_with_date_time_string─┐
│ 2023-12-31 23:30:00 │ 2023-12-31 23:30:00 │ 2023-12-31 23:30:00.000 │
└────────────────────────────┴─────────────────────────────────┴────────────────────────────────────────┘
```
**Using alternative INTERVAL syntax**
```sql title=Query theme={null}
SELECT dateSub('1998-06-16'::Date, INTERVAL 10 minute)
```
```response title=Response theme={null}
┌─minus(CAST('⋯Minute(10))─┐
│ 1998-06-15 23:50:00 │
└──────────────────────────┘
```
subtractMonths
Introduced in: v1.1.0
Subtracts a specified number of months from a date, a date with time or a string-encoded date or date with time.
**Syntax**
```sql theme={null}
subtractMonths(datetime, num)
```
**Arguments**
* `datetime` — Date or date with time to subtract specified number of months from. [`Date`](/reference/data-types/date) or [`Date32`](/reference/data-types/date32) or [`DateTime`](/reference/data-types/datetime) or [`DateTime64`](/reference/data-types/datetime64) or [`String`](/reference/data-types/string)
* `num` — Number of months to subtract. [`(U)Int*`](/reference/data-types/int-uint) or [`Float*`](/reference/data-types/float)
**Returned value**
Returns `datetime` minus `num` months [`Date`](/reference/data-types/date) or [`Date32`](/reference/data-types/date32) or [`DateTime`](/reference/data-types/datetime) or [`DateTime64`](/reference/data-types/datetime64)
**Examples**
**Subtract months from different date types**
```sql title=Query theme={null}
WITH
toDate('2024-01-01') AS date,
toDateTime('2024-01-01 00:00:00') AS date_time,
'2024-01-01 00:00:00' AS date_time_string
SELECT
subtractMonths(date, 1) AS subtract_months_with_date,
subtractMonths(date_time, 1) AS subtract_months_with_date_time,
subtractMonths(date_time_string, 1) AS subtract_months_with_date_time_string
```
```response title=Response theme={null}
┌─subtract_months_with_date─┬─subtract_months_with_date_time─┬─subtract_months_with_date_time_string─┐
│ 2023-12-01 │ 2023-12-01 00:00:00 │ 2023-12-01 00:00:00.000 │
└───────────────────────────┴────────────────────────────────┴───────────────────────────────────────┘
```
**Using alternative INTERVAL syntax**
```sql title=Query theme={null}
SELECT dateSub('1998-06-16'::Date, INTERVAL 10 month)
```
```response title=Response theme={null}
┌─minus(CAST('⋯lMonth(10))─┐
│ 1997-08-16 │
└──────────────────────────┘
```
subtractNanoseconds
Introduced in: v20.1.0
Subtracts a specified number of nanoseconds from a date with time or a string-encoded date with time.
**Syntax**
```sql theme={null}
subtractNanoseconds(datetime, num)
```
**Arguments**
* `datetime` — Date with time to subtract specified number of nanoseconds from. [`DateTime`](/reference/data-types/datetime) or [`DateTime64`](/reference/data-types/datetime64) or [`String`](/reference/data-types/string)
* `num` — Number of nanoseconds to subtract. [`(U)Int*`](/reference/data-types/int-uint) or [`Float*`](/reference/data-types/float)
**Returned value**
Returns `datetime` minus `num` nanoseconds [`DateTime64`](/reference/data-types/datetime64)
**Examples**
**Subtract nanoseconds from different date time types**
```sql title=Query theme={null}
WITH
toDateTime('2024-01-01 00:00:00') AS date_time,
'2024-01-01 00:00:00' AS date_time_string
SELECT
subtractNanoseconds(date_time, 1000) AS subtract_nanoseconds_with_date_time,
subtractNanoseconds(date_time_string, 1000) AS subtract_nanoseconds_with_date_time_string
```
```response title=Response theme={null}
┌─subtract_nanoseconds_with_date_time─┬─subtract_nanoseconds_with_date_time_string─┐
│ 2023-12-31 23:59:59.999999000 │ 2023-12-31 23:59:59.999999000 │
└─────────────────────────────────────┴────────────────────────────────────────────┘
```
**Using alternative INTERVAL syntax**
```sql title=Query theme={null}
SELECT dateSub('1998-06-16'::DateTime, INTERVAL 10 nanosecond)
```
```response title=Response theme={null}
┌─minus(CAST('19⋯anosecond(10))─┐
│ 1998-06-15 23:59:59.999999990 │
└───────────────────────────────┘
```
subtractQuarters
Introduced in: v20.1.0
Subtracts a specified number of quarters from a date, a date with time or a string-encoded date or date with time.
**Syntax**
```sql theme={null}
subtractQuarters(datetime, num)
```
**Arguments**
* `datetime` — Date or date with time to subtract specified number of quarters from. [`Date`](/reference/data-types/date) or [`Date32`](/reference/data-types/date32) or [`DateTime`](/reference/data-types/datetime) or [`DateTime64`](/reference/data-types/datetime64) or [`String`](/reference/data-types/string)
* `num` — Number of quarters to subtract. [`(U)Int*`](/reference/data-types/int-uint) or [`Float*`](/reference/data-types/float)
**Returned value**
Returns `datetime` minus `num` quarters [`Date`](/reference/data-types/date) or [`Date32`](/reference/data-types/date32) or [`DateTime`](/reference/data-types/datetime) or [`DateTime64`](/reference/data-types/datetime64)
**Examples**
**Subtract quarters from different date types**
```sql title=Query theme={null}
WITH
toDate('2024-01-01') AS date,
toDateTime('2024-01-01 00:00:00') AS date_time,
'2024-01-01 00:00:00' AS date_time_string
SELECT
subtractQuarters(date, 1) AS subtract_quarters_with_date,
subtractQuarters(date_time, 1) AS subtract_quarters_with_date_time,
subtractQuarters(date_time_string, 1) AS subtract_quarters_with_date_time_string
```
```response title=Response theme={null}
┌─subtract_quarters_with_date─┬─subtract_quarters_with_date_time─┬─subtract_quarters_with_date_time_string─┐
│ 2023-10-01 │ 2023-10-01 00:00:00 │ 2023-10-01 00:00:00.000 │
└─────────────────────────────┴──────────────────────────────────┴─────────────────────────────────────────┘
```
**Using alternative INTERVAL syntax**
```sql title=Query theme={null}
SELECT dateSub('1998-06-16'::Date, INTERVAL 10 quarter)
```
```response title=Response theme={null}
┌─minus(CAST('1⋯Quarter(10))─┐
│ 1996-09-16 │
└───────────────────────────┘
```
subtractSeconds
Introduced in: v1.1.0
Subtracts a specified number of seconds from a date, a date with time or a string-encoded date or date with time.
**Syntax**
```sql theme={null}
subtractSeconds(datetime, num)
```
**Arguments**
* `datetime` — Date or date with time to subtract specified number of seconds from. [`Date`](/reference/data-types/date) or [`Date32`](/reference/data-types/date32) or [`DateTime`](/reference/data-types/datetime) or [`DateTime64`](/reference/data-types/datetime64) or [`String`](/reference/data-types/string)
* `num` — Number of seconds to subtract. [`(U)Int*`](/reference/data-types/int-uint) or [`Float*`](/reference/data-types/float)
**Returned value**
Returns `datetime` minus `num` seconds [`DateTime`](/reference/data-types/datetime) or [`DateTime64(3)`](/reference/data-types/datetime64)
**Examples**
**Subtract seconds from different date types**
```sql title=Query theme={null}
WITH
toDate('2024-01-01') AS date,
toDateTime('2024-01-01 00:00:00') AS date_time,
'2024-01-01 00:00:00' AS date_time_string
SELECT
subtractSeconds(date, 60) AS subtract_seconds_with_date,
subtractSeconds(date_time, 60) AS subtract_seconds_with_date_time,
subtractSeconds(date_time_string, 60) AS subtract_seconds_with_date_time_string
```
```response title=Response theme={null}
┌─subtract_seconds_with_date─┬─subtract_seconds_with_date_time─┬─subtract_seconds_with_date_time_string─┐
│ 2023-12-31 23:59:00 │ 2023-12-31 23:59:00 │ 2023-12-31 23:59:00.000 │
└────────────────────────────┴─────────────────────────────────┴────────────────────────────────────────┘
```
**Using alternative INTERVAL syntax**
```sql title=Query theme={null}
SELECT dateSub('1998-06-16'::Date, INTERVAL 10 second)
```
```response title=Response theme={null}
┌─minus(CAST('⋯Second(10))─┐
│ 1998-06-15 23:59:50 │
└──────────────────────────┘
```
subtractTupleOfIntervals
Introduced in: v22.11.0
Consecutively subtracts a tuple of intervals from a date or a date with time.
**Syntax**
```sql theme={null}
subtractTupleOfIntervals(datetime, intervals)
```
**Arguments**
* `datetime` — Date or date with time to subtract intervals from. [`Date`](/reference/data-types/date) or [`Date32`](/reference/data-types/date32) or [`DateTime`](/reference/data-types/datetime) or [`DateTime64`](/reference/data-types/datetime64)
* `intervals` — Tuple of intervals to subtract from `datetime`. [`Tuple(Interval)`](/reference/data-types/tuple)
**Returned value**
Returns `date` with subtracted `intervals` [`Date`](/reference/data-types/date) or [`Date32`](/reference/data-types/date32) or [`DateTime`](/reference/data-types/datetime) or [`DateTime64`](/reference/data-types/datetime64)
**Examples**
**Subtract tuple of intervals from date**
```sql title=Query theme={null}
WITH toDate('2018-01-01') AS date SELECT subtractTupleOfIntervals(date, (INTERVAL 1 DAY, INTERVAL 1 YEAR))
```
```response title=Response theme={null}
┌─subtractTupl⋯alYear(1)))─┐
│ 2016-12-31 │
└──────────────────────────┘
```
subtractWeeks
Introduced in: v1.1.0
Subtracts a specified number of weeks from a date, a date with time or a string-encoded date or date with time.
**Syntax**
```sql theme={null}
subtractWeeks(datetime, num)
```
**Arguments**
* `datetime` — Date or date with time to subtract specified number of weeks from. [`Date`](/reference/data-types/date) or [`Date32`](/reference/data-types/date32) or [`DateTime`](/reference/data-types/datetime) or [`DateTime64`](/reference/data-types/datetime64) or [`String`](/reference/data-types/string)
* `num` — Number of weeks to subtract. [`(U)Int*`](/reference/data-types/int-uint) or [`Float*`](/reference/data-types/float)
**Returned value**
Returns `datetime` minus `num` weeks [`Date`](/reference/data-types/date) or [`Date32`](/reference/data-types/date32) or [`DateTime`](/reference/data-types/datetime) or [`DateTime64`](/reference/data-types/datetime64)
**Examples**
**Subtract weeks from different date types**
```sql title=Query theme={null}
WITH
toDate('2024-01-01') AS date,
toDateTime('2024-01-01 00:00:00') AS date_time,
'2024-01-01 00:00:00' AS date_time_string
SELECT
subtractWeeks(date, 1) AS subtract_weeks_with_date,
subtractWeeks(date_time, 1) AS subtract_weeks_with_date_time,
subtractWeeks(date_time_string, 1) AS subtract_weeks_with_date_time_string
```
```response title=Response theme={null}
┌─subtract_weeks_with_date─┬─subtract_weeks_with_date_time─┬─subtract_weeks_with_date_time_string─┐
│ 2023-12-25 │ 2023-12-25 00:00:00 │ 2023-12-25 00:00:00.000 │
└──────────────────────────┴───────────────────────────────┴──────────────────────────────────────┘
```
**Using alternative INTERVAL syntax**
```sql title=Query theme={null}
SELECT dateSub('1998-06-16'::Date, INTERVAL 10 week)
```
```response title=Response theme={null}
┌─minus(CAST('⋯alWeek(10))─┐
│ 1998-04-07 │
└──────────────────────────┘
```
subtractYears
Introduced in: v1.1.0
Subtracts a specified number of years from a date, a date with time or a string-encoded date or date with time.
**Syntax**
```sql theme={null}
subtractYears(datetime, num)
```
**Arguments**
* `datetime` — Date or date with time to subtract specified number of years from. [`Date`](/reference/data-types/date) or [`Date32`](/reference/data-types/date32) or [`DateTime`](/reference/data-types/datetime) or [`DateTime64`](/reference/data-types/datetime64) or [`String`](/reference/data-types/string)
* `num` — Number of years to subtract. [`(U)Int*`](/reference/data-types/int-uint) or [`Float*`](/reference/data-types/float)
**Returned value**
Returns `datetime` minus `num` years [`Date`](/reference/data-types/date) or [`Date32`](/reference/data-types/date32) or [`DateTime`](/reference/data-types/datetime) or [`DateTime64`](/reference/data-types/datetime64)
**Examples**
**Subtract years from different date types**
```sql title=Query theme={null}
WITH
toDate('2024-01-01') AS date,
toDateTime('2024-01-01 00:00:00') AS date_time,
'2024-01-01 00:00:00' AS date_time_string
SELECT
subtractYears(date, 1) AS subtract_years_with_date,
subtractYears(date_time, 1) AS subtract_years_with_date_time,
subtractYears(date_time_string, 1) AS subtract_years_with_date_time_string
```
```response title=Response theme={null}
┌─subtract_years_with_date─┬─subtract_years_with_date_time─┬─subtract_years_with_date_time_string─┐
│ 2023-01-01 │ 2023-01-01 00:00:00 │ 2023-01-01 00:00:00.000 │
└──────────────────────────┴───────────────────────────────┴──────────────────────────────────────┘
```
**Using alternative INTERVAL syntax**
```sql title=Query theme={null}
SELECT dateSub('1998-06-16'::Date, INTERVAL 10 year)
```
```response title=Response theme={null}
┌─minus(CAST('⋯alYear(10))─┐
│ 1988-06-16 │
└──────────────────────────┘
```
timeDiff
Introduced in: v23.4.0
Returns the difference between two dates or dates with time values in seconds.
The difference is calculated as `enddate` - `startdate`.
This function is equivalent to `dateDiff('second', startdate, enddate)`.
For calculating time differences in other units (hours, days, months, etc.), use the [`dateDiff`](#dateDiff) function instead.
**Syntax**
```sql theme={null}
timeDiff(startdate, enddate)
```
**Arguments**
* `startdate` — The first time value to subtract (the subtrahend). [`Date`](/reference/data-types/date) or [`Date32`](/reference/data-types/date32) or [`DateTime`](/reference/data-types/datetime) or [`DateTime64`](/reference/data-types/datetime64)
* `enddate` — The second time value to subtract from (the minuend). [`Date`](/reference/data-types/date) or [`Date32`](/reference/data-types/date32) or [`DateTime`](/reference/data-types/datetime) or [`DateTime64`](/reference/data-types/datetime64)
**Returned value**
Returns the difference between `enddate` and `startdate` expressed in seconds. [`Int64`](/reference/data-types/int-uint)
**Examples**
**Calculate time difference in seconds**
```sql title=Query theme={null}
SELECT timeDiff(toDateTime('2018-01-01 22:00:00'), toDateTime('2018-01-02 23:00:00')) AS res
```
```response title=Response theme={null}
┌───res─┐
│ 90000 │
└───────┘
```
**Calculate time difference and convert to hours**
```sql title=Query theme={null}
SELECT timeDiff(toDateTime('2018-01-01 22:00:00'), toDateTime('2018-01-02 23:00:00')) / 3600 AS hours
```
```response title=Response theme={null}
┌─hours─┐
│ 25 │
└───────┘
```
**Equivalent to dateDiff with seconds**
```sql title=Query theme={null}
SELECT
timeDiff(toDateTime('2021-12-29'), toDateTime('2022-01-01')) AS time_diff_result,
dateDiff('second', toDateTime('2021-12-29'), toDateTime('2022-01-01')) AS date_diff_result
```
```response title=Response theme={null}
┌─time_diff_result─┬─date_diff_result─┐
│ 259200 │ 259200 │
└──────────────────┴──────────────────┘
```
timeSlot
Introduced in: v1.1.0
Round the time to the start of a half-an-hour length interval.
Although this function can take values of the extended types `Date32` and `DateTime64` as an argument,
passing it a time outside the normal range (year 1970 to 2149 for `Date` / 2106 for `DateTime`) will produce wrong results.
**Syntax**
```sql theme={null}
timeSlot(time[, time_zone])
```
**Arguments**
* `time` — Time to round to the start of a half-an-hour length interval. [`DateTime`](/reference/data-types/datetime) or [`Date32`](/reference/data-types/date32) or [`DateTime64`](/reference/data-types/datetime64)
* `time_zone` — Optional. A String type const value or an expression representing the time zone. [`String`](/reference/data-types/string)
**Returned value**
Returns the time rounded to the start of a half-an-hour length interval. [`DateTime`](/reference/data-types/datetime)
**Examples**
**Round time to half-hour interval**
```sql title=Query theme={null}
SELECT timeSlot(toDateTime('2000-01-02 03:04:05', 'UTC'))
```
```response title=Response theme={null}
┌─timeSlot(toDateTime('2000-01-02 03:04:05', 'UTC'))─┐
│ 2000-01-02 03:00:00 │
└────────────────────────────────────────────────────┘
```
timeSlots
Introduced in: v1.1.0
For a time interval starting at `StartTime` and continuing for `Duration` seconds, it returns an array of moments in time, consisting of points from this interval rounded down to the `Size` in seconds. `Size` is an optional parameter set to 1800 (30 minutes) by default.
This is necessary, for example, when searching for pageviews in the corresponding session.
For `DateTime64`, the return value's scale can differ from the scale of `StartTime`. The highest scale among all given arguments is taken.
**Syntax**
```sql theme={null}
timeSlots(StartTime, Duration[, Size])
```
**Arguments**
* `StartTime` — Starting time for the interval. [`DateTime`](/reference/data-types/datetime) or [`DateTime64`](/reference/data-types/datetime64)
* `Duration` — Duration of the interval in seconds. [`UInt32`](/reference/data-types/int-uint) or [`DateTime64`](/reference/data-types/datetime64)
* `Size` — Optional. Size of time slots in seconds. Default is 1800 (30 minutes). [`UInt32`](/reference/data-types/int-uint) or [`DateTime64`](/reference/data-types/datetime64)
**Returned value**
Returns an array of DateTime/DateTime64 (return type matches the type of `StartTime`). For DateTime64, the return value's scale can differ from the scale of `StartTime` - the highest scale among all given arguments is taken. [`Array(DateTime)`](/reference/data-types/array) or [`Array(DateTime64)`](/reference/data-types/array)
**Examples**
**Generate time slots for an interval**
```sql title=Query theme={null}
SELECT timeSlots(toDateTime('2012-01-01 12:20:00'), toUInt32(600));
SELECT timeSlots(toDateTime('1980-12-12 21:01:02', 'UTC'), toUInt32(600), 299);
SELECT timeSlots(toDateTime64('1980-12-12 21:01:02.1234', 4, 'UTC'), toDecimal64(600.1, 1), toDecimal64(299, 0))
```
```response title=Response theme={null}
┌─timeSlots(toDateTime('2012-01-01 12:20:00'), toUInt32(600))─┐
│ ['2012-01-01 12:00:00','2012-01-01 12:30:00'] │
└─────────────────────────────────────────────────────────────┘
┌─timeSlots(toDateTime('1980-12-12 21:01:02', 'UTC'), toUInt32(600), 299)─┐
│ ['1980-12-12 20:56:13','1980-12-12 21:01:12','1980-12-12 21:06:11'] │
└─────────────────────────────────────────────────────────────────────────┘
┌─timeSlots(toDateTime64('1980-12-12 21:01:02.1234', 4, 'UTC'), toDecimal64(600.1, 1), toDecimal64(299, 0))─┐
│ ['1980-12-12 20:56:13.0000','1980-12-12 21:01:12.0000','1980-12-12 21:06:11.0000'] │
└───────────────────────────────────────────────────────────────────────────────────────────────────────────┘
```
timestamp
Introduced in: v23.9.0
Converts the first argument `expr` to type [`DateTime64(6)`](/reference/data-types/datetime64).
If a second argument `expr_time` is provided, it adds the specified time to the converted value.
**Syntax**
```sql theme={null}
timestamp(expr[, expr_time])
```
**Arguments**
* `expr` — Date or date with time. [`String`](/reference/data-types/string)
* `expr_time` — Optional. Time to add to the converted value. [`String`](/reference/data-types/string)
**Returned value**
Returns the converted value of `expr`, or `expr` with added time [`DateTime64(6)`](/reference/data-types/datetime64)
**Examples**
**Convert date string to DateTime64(6)**
```sql title=Query theme={null}
SELECT timestamp('2023-12-31') AS ts;
```
```response title=Response theme={null}
┌─────────────────────────ts─┐
│ 2023-12-31 00:00:00.000000 │
└────────────────────────────┘
```
**Add time to date string**
```sql title=Query theme={null}
SELECT timestamp('2023-12-31 12:00:00', '12:00:00.11') AS ts;
```
```response title=Response theme={null}
┌─────────────────────────ts─┐
│ 2024-01-01 00:00:00.110000 │
└────────────────────────────┘
```
timezone
Introduced in: v21.4.0
Returns the time zone name of the current session or converts a time zone
offset or name to a canonical time zone name.
**Syntax**
```sql theme={null}
timezone()
```
**Aliases**: `timeZone`
**Arguments**
* None.
**Returned value**
Returns the canonical time zone name as a [`String`](/reference/data-types/string)
**Examples**
**Usage example**
```sql title=Query theme={null}
SELECT timezone()
```
```response title=Response theme={null}
┌─timezone()───────┐
│ Europe/Amsterdam │
└──────────────────┘
```
timezoneOf
Introduced in: v21.4.0
Returns the timezone name of a [`DateTime`](/reference/data-types/datetime) or [`DateTime64`](/reference/data-types/datetime64) value.
**Syntax**
```sql theme={null}
timezoneOf(datetime)
```
**Aliases**: `timeZoneOf`
**Arguments**
* `datetime` — A value of type. [`DateTime`](/reference/data-types/datetime) or [`DateTime64`](/reference/data-types/datetime64)
* `timezone` — Optional. Timezone name to convert the `datetime` value's timezone to. [`String`](/reference/data-types/string)
**Returned value**
Returns the timezone name for `datetime` [`String`](/reference/data-types/string)
**Examples**
**Usage example**
```sql title=Query theme={null}
SELECT timezoneOf(now());
```
```response title=Response theme={null}
┌─timezoneOf(now())─┐
│ Europe/Amsterdam │
└───────────────────┘
```
timezoneOffset
Introduced in: v21.6.0
Returns the timezone offset in seconds from [UTC](https://en.wikipedia.org/wiki/Coordinated_Universal_Time).
The function takes daylight saving time and historical timezone changes at the specified date and time into account.
**Syntax**
```sql theme={null}
timezoneOffset(datetime)
```
**Aliases**: `timeZoneOffset`
**Arguments**
* `datetime` — `DateTime` value to get the timezone offset for. [`DateTime`](/reference/data-types/datetime) or [`DateTime64`](/reference/data-types/datetime64)
**Returned value**
Returns the offset from UTC in seconds [`Int32`](/reference/data-types/int-uint)
**Examples**
**Usage example**
```sql title=Query theme={null}
SELECT toDateTime('2021-04-21 10:20:30', 'America/New_York') AS Time,
toTypeName(Time) AS Type,
timezoneOffset(Time) AS Offset_in_seconds,
(Offset_in_seconds / 3600) AS Offset_in_hours;
```
```response title=Response theme={null}
┌────────────────Time─┬─Type─────────────────────────┬─Offset_in_seconds─┬─Offset_in_hours─┐
│ 2021-04-21 10:20:30 │ DateTime('America/New_York') │ -14400 │ -4 │
└─────────────────────┴──────────────────────────────┴───────────────────┴─────────────────┘
```
toDayOfMonth
Introduced in: v1.1.0
Returns the day of the month (1-31) of a `Date` or `DateTime`.
**Syntax**
```sql theme={null}
toDayOfMonth(datetime)
```
**Aliases**: `DAY`, `DAYOFMONTH`
**Arguments**
* `datetime` — Date or date with time to get the day of month from. [`Date`](/reference/data-types/date) or [`Date32`](/reference/data-types/date32) or [`DateTime`](/reference/data-types/datetime) or [`DateTime64`](/reference/data-types/datetime64)
**Returned value**
Returns the day of the month of the given date/time [`UInt8`](/reference/data-types/int-uint)
**Examples**
**Usage example**
```sql title=Query theme={null}
SELECT toDayOfMonth(toDateTime('2023-04-21 10:20:30'))
```
```response title=Response theme={null}
┌─toDayOfMonth(toDateTime('2023-04-21 10:20:30'))─┐
│ 21 │
└─────────────────────────────────────────────────┘
```
toDayOfWeek
Introduced in: v1.1.0
Returns the number of the day within the week of a `Date` or `DateTime` value.
The two-argument form of `toDayOfWeek()` enables you to specify whether the week starts on Monday or Sunday,
and whether the return value should be in the range from 0 to 6 or 1 to 7.
| Mode | First day of week | Range |
| ---- | ----------------- | ---------------------------------------------- |
| 0 | Monday | 1-7: Monday = 1, Tuesday = 2, ..., Sunday = 7 |
| 1 | Monday | 0-6: Monday = 0, Tuesday = 1, ..., Sunday = 6 |
| 2 | Sunday | 0-6: Sunday = 0, Monday = 1, ..., Saturday = 6 |
| 3 | Sunday | 1-7: Sunday = 1, Monday = 2, ..., Saturday = 7 |
**Syntax**
```sql theme={null}
toDayOfWeek(datetime[, mode[, timezone]])
```
**Aliases**: `DAYOFWEEK`
**Arguments**
* `datetime` — Date or date with time to get the day of week from. [`Date`](/reference/data-types/date) or [`Date32`](/reference/data-types/date32) or [`DateTime`](/reference/data-types/datetime) or [`DateTime64`](/reference/data-types/datetime64)
* `mode` — Optional. Integer specifying the week mode (0-3). Defaults to 0 if omitted. [`UInt8`](/reference/data-types/int-uint)
* `timezone` — Optional. Timezone to use for the conversion. [`String`](/reference/data-types/string)
**Returned value**
Returns the day of the week for the given `Date` or `DateTime` [`UInt8`](/reference/data-types/int-uint)
**Examples**
**Usage example**
```sql title=Query theme={null}
-- The following date is April 21, 2023, which was a Friday:
SELECT
toDayOfWeek(toDateTime('2023-04-21')),
toDayOfWeek(toDateTime('2023-04-21'), 1)
```
```response title=Response theme={null}
┌─toDayOfWeek(toDateTime('2023-04-21'))─┬─toDayOfWeek(toDateTime('2023-04-21'), 1)─┐
│ 5 │ 4 │
└───────────────────────────────────────┴──────────────────────────────────────────┘
```
toDayOfYear
Introduced in: v18.4.0
Returns the number of the day within the year (1-366) of a `Date` or `DateTime` value.
**Syntax**
```sql theme={null}
toDayOfYear(datetime)
```
**Aliases**: `DAYOFYEAR`
**Arguments**
* `datetime` — Date or date with time to get the day of year from. [`Date`](/reference/data-types/date) or [`Date32`](/reference/data-types/date32) or [`DateTime`](/reference/data-types/datetime) or [`DateTime64`](/reference/data-types/datetime64)
**Returned value**
Returns the day of the year of the given Date or DateTime [`UInt16`](/reference/data-types/int-uint)
**Examples**
**Usage example**
```sql title=Query theme={null}
SELECT toDayOfYear(toDateTime('2023-04-21 10:20:30'))
```
```response title=Response theme={null}
┌─toDayOfYear(toDateTime('2023-04-21 10:20:30'))─┐
│ 111 │
└────────────────────────────────────────────────┘
```
toDaysInMonth
Introduced in: v26.3.0
Returns the number of days in the month of a `Date` or `DateTime`.
The returned value is in the range 28 to 31.
**Syntax**
```sql theme={null}
toDaysInMonth(datetime)
```
**Arguments**
* `datetime` — Date or date with time to get the number of days in the month from. [`Date`](/reference/data-types/date) or [`Date32`](/reference/data-types/date32) or [`DateTime`](/reference/data-types/datetime) or [`DateTime64`](/reference/data-types/datetime64)
**Returned value**
Returns the number of days in the month of the given date/time. [`UInt8`](/reference/data-types/int-uint)
**Examples**
**Usage example**
```sql title=Query theme={null}
SELECT toDaysInMonth(toDate('2023-02-01')), toDaysInMonth(toDate('2024-02-01')), toDaysInMonth(toDate('2023-01-01'))
```
```response title=Response theme={null}
┌─toDaysInMonth(toDate('2023-02-01'))─┬─toDaysInMonth(toDate('2024-02-01'))─┬─toDaysInMonth(toDate('2023-01-01'))─┐
│ 28 │ 29 │ 31 │
└─────────────────────────────────────┴─────────────────────────────────────┴─────────────────────────────────────┘
```
toDaysSinceYearZero
Introduced in: v23.9.0
For a given date, returns the number of days which have passed since [1 January 0000](https://en.wikipedia.org/wiki/Year_zero) in the
[proleptic Gregorian calendar defined by ISO 8601](https://en.wikipedia.org/wiki/Gregorian_calendar#Proleptic_Gregorian_calendar).
The calculation is the same as in MySQL's [`TO_DAYS`](https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_to-days) function.
**Syntax**
```sql theme={null}
toDaysSinceYearZero(date[, time_zone])
```
**Aliases**: `TO_DAYS`
**Arguments**
* `date` — The date or date with time for which to calculate the number of days since year zero from. [`Date`](/reference/data-types/date) or [`Date32`](/reference/data-types/date32) or [`DateTime`](/reference/data-types/datetime) or [`DateTime64`](/reference/data-types/datetime64)
* `time_zone` — Time zone. [`String`](/reference/data-types/string)
**Returned value**
Returns the number of days passed since date `0000-01-01`. [`UInt32`](/reference/data-types/int-uint)
**Examples**
**Calculate days since year zero**
```sql title=Query theme={null}
SELECT toDaysSinceYearZero(toDate('2023-09-08'))
```
```response title=Response theme={null}
┌─toDaysSinceYearZero(toDate('2023-09-08')))─┐
│ 713569 │
└────────────────────────────────────────────┘
```
toHour
Introduced in: v1.1.0
Returns the hour component (0-23) of a `DateTime` or `DateTime64` value.
**Syntax**
```sql theme={null}
toHour(datetime)
```
**Aliases**: `HOUR`
**Arguments**
* `datetime` — Date with time to get the hour from. [`DateTime`](/reference/data-types/datetime) or [`DateTime64`](/reference/data-types/datetime64)
**Returned value**
Returns the hour (0-23) of `datetime`. [`UInt8`](/reference/data-types/int-uint)
**Examples**
**Usage example**
```sql title=Query theme={null}
SELECT toHour(toDateTime('2023-04-21 10:20:30'))
```
```response title=Response theme={null}
┌─toHour(toDateTime('2023-04-21 10:20:30'))─┐
│ 10 │
└───────────────────────────────────────────┘
```
toISOWeek
Introduced in: v20.1.0
Returns the ISO week number of a date or date with time.
This is a compatibility function that is equivalent to `toWeek(date, 3)`.
ISO weeks start on Monday and the first week of the year contains January 4th.
According to ISO 8601, week numbers are in the range from 1 to 53.
Note that dates near the beginning or end of a year may return a week number from the previous or next year. For example,
December 29, 2025 returns week 1 because it falls in the first week that contains January 4, 2026.
**Syntax**
```sql theme={null}
toISOWeek(datetime[, timezone])
```
**Arguments**
* `datetime` — Date or date with time to get the ISO week number from. [`Date`](/reference/data-types/date) or [`DateTime`](/reference/data-types/datetime) or [`Date32`](/reference/data-types/date32) or [`DateTime64`](/reference/data-types/datetime64)
* `timezone` — Optional. Time zone. [`String`](/reference/data-types/string)
**Returned value**
Returns the ISO week number according to ISO 8601 standard. Returns a number between 1 and 53. [`UInt8`](/reference/data-types/int-uint)
**Examples**
**Get ISO week numbers**
```sql title=Query theme={null}
SELECT toDate('2016-12-27') AS date, toISOWeek(date) AS isoWeek
```
```response title=Response theme={null}
┌───────date─┬─isoWeek─┐
│ 2016-12-27 │ 52 │
└────────────┴─────────┘
```
**ISO week can belong to different year**
```sql title=Query theme={null}
SELECT toDate('2025-12-29') AS date, toISOWeek(date) AS isoWeek, toYear(date) AS year
```
```response title=Response theme={null}
┌───────date─┬─isoWeek─┬─year─┐
│ 2025-12-29 │ 1 │ 2025 │
└────────────┴─────────┴──────┘
```
toISOYear
Introduced in: v18.4.0
Converts a date or date with time to the ISO year number.
**Syntax**
```sql theme={null}
toISOYear(datetime)
```
**Arguments**
* `datetime` — The value with date or date with time. [`Date`](/reference/data-types/date) or [`Date32`](/reference/data-types/date32) or [`DateTime`](/reference/data-types/datetime) or [`DateTime64`](/reference/data-types/datetime64)
**Returned value**
Returns the input value converted to an ISO year number. [`UInt16`](/reference/data-types/int-uint)
**Examples**
**Get ISO year from date values**
```sql title=Query theme={null}
SELECT
toISOYear(toDate('2024/10/02')) as year1,
toISOYear(toDateTime('2024-10-02 01:30:00')) as year2
```
```response title=Response theme={null}
┌─week1─┬─week2─┐
│ 40 │ 40 │
└───────┴───────┘
```
toLastDayOfMonth
Introduced in: v1.1.0
Rounds up a date or date with time to the last day of the month.
The return type can be configured by setting [`enable_extended_results_for_datetime_functions`](/reference/settings/session-settings#enable_extended_results_for_datetime_functions).
**Syntax**
```sql theme={null}
toLastDayOfMonth(value)
```
**Aliases**: `LAST_DAY`
**Arguments**
* `value` — The date or date with time to round up to the last day of the month. [`Date`](/reference/data-types/date) or [`Date32`](/reference/data-types/date32) or [`DateTime`](/reference/data-types/datetime) or [`DateTime64`](/reference/data-types/datetime64)
**Returned value**
Returns the date of the last day of the month for the given date or date with time. [`Date`](/reference/data-types/date)
**Examples**
**Round up to the last day of the month**
```sql title=Query theme={null}
SELECT toLastDayOfMonth(toDateTime('2023-04-21 10:20:30'))
```
```response title=Response theme={null}
┌─toLastDayOfMonth(toDateTime('2023-04-21 10:20:30'))─┐
│ 2023-04-30 │
└─────────────────────────────────────────────────────┘
```
toLastDayOfWeek
Introduced in: v23.5.0
Rounds a date or date with time up to the nearest Saturday or Sunday.
The return type can be configured by setting [`enable_extended_results_for_datetime_functions`](/reference/settings/session-settings#enable_extended_results_for_datetime_functions).
**Syntax**
```sql theme={null}
toLastDayOfWeek(datetime[, mode[, timezone]])
```
**Arguments**
* `datetime` — A date or date with time to convert. [`Date`](/reference/data-types/date) or [`DateTime`](/reference/data-types/datetime) or [`Date32`](/reference/data-types/date32) or [`DateTime64`](/reference/data-types/datetime64)
* `mode` — Determines the first day of the week as described in the `toWeek()` function. Default `0`. [`UInt8`](/reference/data-types/int-uint)
* `timezone` — Optional. The timezone to use for the conversion. If not specified, the server's timezone is used. [`String`](/reference/data-types/string)
**Returned value**
Returns the date of the nearest Saturday or Sunday, on or after the given date, depending on the mode. [`Date`](/reference/data-types/date) or [`Date32`](/reference/data-types/date32)
**Examples**
**Round up to the nearest Saturday or Sunday**
```sql title=Query theme={null}
SELECT
toLastDayOfWeek(toDateTime('2023-04-21 10:20:30')), /* a Friday */
toLastDayOfWeek(toDateTime('2023-04-21 10:20:30'), 1), /* a Friday */
toLastDayOfWeek(toDate('2023-04-23')), /* a Sunday */
toLastDayOfWeek(toDate('2023-04-23'), 1) /* a Sunday */
FORMAT Vertical
```
```response title=Response theme={null}
Row 1:
──────
toLastDayOfWeek(toDateTime('2023-04-21 10:20:30')): 2023-04-23
toLastDayOfWeek(toDateTime('2023-04-21 10:20:30'), 1): 2023-04-22
toLastDayOfWeek(toDate('2023-04-23')): 2023-04-23
toLastDayOfWeek(toDate('2023-04-23'), 1): 2023-04-23
```
toMillisecond
Introduced in: v24.2.0
Returns the millisecond component (0-999) of a `DateTime` or `DateTime64` value.
**Syntax**
```sql theme={null}
toMillisecond(datetime)
```
**Aliases**: `MILLISECOND`
**Arguments**
* `datetime` — Date with time to get the millisecond from. [`DateTime`](/reference/data-types/datetime) or [`DateTime64`](/reference/data-types/datetime64)
**Returned value**
Returns the millisecond in the minute (0 - 59) of `datetime`. [`UInt16`](/reference/data-types/int-uint)
**Examples**
**Usage example**
```sql title=Query theme={null}
SELECT toMillisecond(toDateTime64('2023-04-21 10:20:30.456', 3));
```
```response title=Response theme={null}
┌──toMillisecond(toDateTime64('2023-04-21 10:20:30.456', 3))─┐
│ 456 │
└────────────────────────────────────────────────────────────┘
```
toMinute
Introduced in: v1.1.0
Returns the minute component (0-59) of a `Date` or `DateTime` value.
**Syntax**
```sql theme={null}
toMinute(datetime)
```
**Aliases**: `MINUTE`
**Arguments**
* `datetime` — Date with time to get the minute from. [`DateTime`](/reference/data-types/datetime) or [`DateTime64`](/reference/data-types/datetime64)
**Returned value**
Returns the minute of the hour (0 - 59) of `datetime`. [`UInt8`](/reference/data-types/int-uint)
**Examples**
**Usage example**
```sql title=Query theme={null}
SELECT toMinute(toDateTime('2023-04-21 10:20:30'))
```
```response title=Response theme={null}
┌─toMinute(toDateTime('2023-04-21 10:20:30'))─┐
│ 20 │
└─────────────────────────────────────────────┘
```
toModifiedJulianDay
Introduced in: v21.1.0
Converts a [Proleptic Gregorian calendar](https://en.wikipedia.org/wiki/Proleptic_Gregorian_calendar) date in text form `YYYY-MM-DD` to a [Modified Julian Day](https://en.wikipedia.org/wiki/Julian_day#Variants) number in `Int32`. This function supports date from `0000-01-01` to `9999-12-31`. It raises an exception if the argument cannot be parsed as a date, or the date is invalid.
**Syntax**
```sql theme={null}
toModifiedJulianDay(date)
```
**Arguments**
* `date` — The date in String form. [`String`](/reference/data-types/string) or [`FixedString`](/reference/data-types/fixedstring)
**Returned value**
Returns Modified Julian Day number. [`Int32`](/reference/data-types/int-uint)
**Examples**
**Convert date to Modified Julian Day**
```sql title=Query theme={null}
SELECT toModifiedJulianDay('2020-01-01')
```
```response title=Response theme={null}
┌─toModifiedJulianDay('2020-01-01')─┐
│ 58849 │
└───────────────────────────────────┘
```
toModifiedJulianDayOrNull
Introduced in: v21.1.0
Similar to [`toModifiedJulianDay()`](#toModifiedJulianDay), but instead of raising exceptions it returns `NULL`.
**Syntax**
```sql theme={null}
toModifiedJulianDayOrNull(date)
```
**Arguments**
* `date` — Date in text form. [`String`](/reference/data-types/string) or [`FixedString`](/reference/data-types/fixedstring)
**Returned value**
Returns the modified Julian day number for valid `date`, otherwise `null`. [`Nullable(Int32)`](/reference/data-types/nullable)
**Examples**
**Convert date to Modified Julian Day with null handling**
```sql title=Query theme={null}
SELECT toModifiedJulianDayOrNull('2020-01-01');
SELECT toModifiedJulianDayOrNull('0000-00-00'); -- invalid date, returns NULL
```
```response title=Response theme={null}
┌─toModifiedJu⋯020-01-01')─┐
│ 58849 │
└──────────────────────────┘
┌─toModifiedJu⋯000-00-00')─┐
│ ᴺᵁᴸᴸ │
└──────────────────────────┘
```
toMonday
Introduced in: v1.1.0
Rounds down a date or date with time to the Monday of the same week. Returns the date.
The return type can be configured by setting [`enable_extended_results_for_datetime_functions`](/reference/settings/session-settings#enable_extended_results_for_datetime_functions).
**Syntax**
```sql theme={null}
toMonday(value)
```
**Arguments**
* `value` — Date or date with time to round down to the Monday of the week. [`Date`](/reference/data-types/date) or [`Date32`](/reference/data-types/date32) or [`DateTime`](/reference/data-types/datetime) or [`DateTime64`](/reference/data-types/datetime64)
**Returned value**
Returns the date of the Monday of the same week for the given date or date with time. [`Date`](/reference/data-types/date)
**Examples**
**Round down to the Monday of the week**
```sql title=Query theme={null}
SELECT
toMonday(toDateTime('2023-04-21 10:20:30')), -- A Friday
toMonday(toDate('2023-04-24')); -- Already a Monday
```
```response title=Response theme={null}
┌─toMonday(toDateTime('2023-04-21 10:20:30'))─┬─toMonday(toDate('2023-04-24'))─┐
│ 2023-04-17 │ 2023-04-24 │
└─────────────────────────────────────────────┴────────────────────────────────┘
```
toMonth
Introduced in: v1.1.0
Returns the month component (1-12) of a `Date` or `DateTime` value.
**Syntax**
```sql theme={null}
toMonth(datetime)
```
**Aliases**: `MONTH`
**Arguments**
* `datetime` — Date or date with time to get the month from. [`Date`](/reference/data-types/date) or [`Date32`](/reference/data-types/date32) or [`DateTime`](/reference/data-types/datetime) or [`DateTime64`](/reference/data-types/datetime64)
**Returned value**
Returns the month of the given date/time [`UInt8`](/reference/data-types/int-uint)
**Examples**
**Usage example**
```sql title=Query theme={null}
SELECT toMonth(toDateTime('2023-04-21 10:20:30'))
```
```response title=Response theme={null}
┌─toMonth(toDateTime('2023-04-21 10:20:30'))─┐
│ 4 │
└────────────────────────────────────────────┘
```
toMonthNumSinceEpoch
Introduced in: v25.3.0
Returns amount of months passed from year 1970
**Syntax**
```sql theme={null}
toMonthNumSinceEpoch(date)
```
**Arguments**
* `date` — A date or date with time. [`Date`](/reference/data-types/date) or [`DateTime`](/reference/data-types/datetime) or [`DateTime64`](/reference/data-types/datetime64)
**Returned value**
Positive integer
**Examples**
**Example**
```sql title=Query theme={null}
SELECT toMonthNumSinceEpoch(toDate('2024-10-01'))
```
```response title=Response theme={null}
657
```
toQuarter
Introduced in: v1.1.0
Returns the quarter of the year (1-4) for a given `Date` or `DateTime` value.
**Syntax**
```sql theme={null}
toQuarter(datetime)
```
**Aliases**: `QUARTER`
**Arguments**
* `datetime` — Date or date with time to get the quarter of the year from. [`Date`](/reference/data-types/date) or [`Date32`](/reference/data-types/date32) or [`DateTime`](/reference/data-types/datetime) or [`DateTime64`](/reference/data-types/datetime64)
**Returned value**
Returns the quarter of the year for the given date/time [`UInt8`](/reference/data-types/int-uint)
**Examples**
**Usage example**
```sql title=Query theme={null}
SELECT toQuarter(toDateTime('2023-04-21 10:20:30'))
```
```response title=Response theme={null}
┌─toQuarter(toDateTime('2023-04-21 10:20:30'))─┐
│ 2 │
└──────────────────────────────────────────────┘
```
toRelativeDayNum
Introduced in: v1.1.0
Converts a date or date with time to the number of days elapsed since a certain fixed point in the past.
The exact point in time is an implementation detail, and therefore this function is not intended to be used standalone.
The main purpose of the function is to calculate the difference in days between two dates or dates with time, e.g., `toRelativeDayNum(dt1) - toRelativeDayNum(dt2)`.
**Syntax**
```sql theme={null}
toRelativeDayNum(date)
```
**Arguments**
* `date` — Date or date with time. [`Date`](/reference/data-types/date) or [`DateTime`](/reference/data-types/datetime) or [`DateTime64`](/reference/data-types/datetime64)
**Returned value**
Returns the number of days from a fixed reference point in the past. [`UInt32`](/reference/data-types/int-uint)
**Examples**
**Get relative day numbers**
```sql title=Query theme={null}
SELECT toRelativeDayNum(toDate('2023-04-01')) - toRelativeDayNum(toDate('2023-01-01'))
```
```response title=Response theme={null}
┌─minus(toRela⋯3-01-01')))─┐
│ 90 │
└──────────────────────────┘
```
toRelativeHourNum
Introduced in: v1.1.0
Converts a date or date with time to the number of hours elapsed since a certain fixed point in the past.
The exact point in time is an implementation detail, and therefore this function is not intended to be used standalone.
The main purpose of the function is to calculate the difference in hours between two dates or dates with time, e.g., `toRelativeHourNum(dt1) - toRelativeHourNum(dt2)`.
**Syntax**
```sql theme={null}
toRelativeHourNum(date)
```
**Arguments**
* `date` — Date or date with time. [`Date`](/reference/data-types/date) or [`DateTime`](/reference/data-types/datetime) or [`DateTime64`](/reference/data-types/datetime64)
**Returned value**
Returns the number of hours from a fixed reference point in the past. [`UInt32`](/reference/data-types/int-uint)
**Examples**
**Get relative hour numbers**
```sql title=Query theme={null}
SELECT toRelativeHourNum(toDateTime('2023-01-01 12:00:00')) - toRelativeHourNum(toDateTime('2023-01-01 00:00:00')) AS hours_difference
```
```response title=Response theme={null}
┌─hours_difference─┐
│ 12 │
└──────────────────┘
```
toRelativeMinuteNum
Introduced in: v1.1.0
Converts a date or date with time to the number of minutes elapsed since a certain fixed point in the past.
The exact point in time is an implementation detail, and therefore this function is not intended to be used standalone.
The main purpose of the function is to calculate the difference in minutes between two dates or dates with time, e.g., `toRelativeMinuteNum(dt1) - toRelativeMinuteNum(dt2)`.
**Syntax**
```sql theme={null}
toRelativeMinuteNum(date)
```
**Arguments**
* `date` — Date or date with time. [`Date`](/reference/data-types/date) or [`DateTime`](/reference/data-types/datetime) or [`DateTime64`](/reference/data-types/datetime64)
**Returned value**
Returns the number of minutes from a fixed reference point in the past. [`UInt32`](/reference/data-types/int-uint)
**Examples**
**Get relative minute numbers**
```sql title=Query theme={null}
SELECT toRelativeMinuteNum(toDateTime('2023-01-01 00:30:00')) - toRelativeMinuteNum(toDateTime('2023-01-01 00:00:00')) AS minutes_difference
```
```response title=Response theme={null}
┌─minutes_difference─┐
│ 30 │
└────────────────────┘
```
toRelativeMonthNum
Introduced in: v1.1.0
Converts a date or date with time to the number of months elapsed since a certain fixed point in the past.
The exact point in time is an implementation detail, and therefore this function is not intended to be used standalone.
The main purpose of the function is to calculate the difference in months between two dates or dates with time, e.g., `toRelativeMonthNum(dt1) - toRelativeMonthNum(dt2)`.
**Syntax**
```sql theme={null}
toRelativeMonthNum(date)
```
**Arguments**
* `date` — Date or date with time. [`Date`](/reference/data-types/date) or [`DateTime`](/reference/data-types/datetime) or [`DateTime64`](/reference/data-types/datetime64)
**Returned value**
Returns the number of months from a fixed reference point in the past. [`UInt32`](/reference/data-types/int-uint)
**Examples**
**Get relative month numbers**
```sql title=Query theme={null}
SELECT toRelativeMonthNum(toDate('2023-04-01')) - toRelativeMonthNum(toDate('2023-01-01')) AS months_difference
```
```response title=Response theme={null}
┌─months_difference─┐
│ 3 │
└───────────────────┘
```
toRelativeQuarterNum
Introduced in: v1.1.0
Converts a date or date with time to the number of quarters elapsed since a certain fixed point in the past.
The exact point in time is an implementation detail, and therefore this function is not intended to be used standalone.
The main purpose of the function is to calculate the difference in quarters between two dates or dates with time, e.g., `toRelativeQuarterNum(dt1) - toRelativeQuarterNum(dt2)`.
**Syntax**
```sql theme={null}
toRelativeQuarterNum(date)
```
**Arguments**
* `date` — Date or date with time. [`Date`](/reference/data-types/date) or [`DateTime`](/reference/data-types/datetime) or [`DateTime64`](/reference/data-types/datetime64)
**Returned value**
Returns the number of quarters from a fixed reference point in the past. [`UInt32`](/reference/data-types/int-uint)
**Examples**
**Get relative quarter numbers**
```sql title=Query theme={null}
SELECT toRelativeQuarterNum(toDate('2023-04-01')) - toRelativeQuarterNum(toDate('2023-01-01')) AS quarters_difference
```
```response title=Response theme={null}
┌─quarters_difference─┐
│ 1 │
└─────────────────────┘
```
toRelativeSecondNum
Introduced in: v1.1.0
Converts a date or date with time to the number of seconds elapsed since a certain fixed point in the past.
The exact point in time is an implementation detail, and therefore this function is not intended to be used standalone.
The main purpose of the function is to calculate the difference in seconds between two dates or dates with time, e.g., `toRelativeSecondNum(dt1) - toRelativeSecondNum(dt2)`.
**Syntax**
```sql theme={null}
toRelativeSecondNum(date)
```
**Arguments**
* `date` — Date or date with time. [`Date`](/reference/data-types/date) or [`DateTime`](/reference/data-types/datetime) or [`DateTime64`](/reference/data-types/datetime64)
**Returned value**
Returns the number of seconds from a fixed reference point in the past. [`UInt32`](/reference/data-types/int-uint)
**Examples**
**Get relative second numbers**
```sql title=Query theme={null}
SELECT toRelativeSecondNum(toDateTime('2023-01-01 00:01:00')) - toRelativeSecondNum(toDateTime('2023-01-01 00:00:00')) AS seconds_difference
```
```response title=Response theme={null}
┌─seconds_difference─┐
│ 60 │
└────────────────────┘
```
toRelativeWeekNum
Introduced in: v1.1.0
Converts a date or date with time to the number of weeks elapsed since a certain fixed point in the past.
The exact point in time is an implementation detail, and therefore this function is not intended to be used standalone.
The main purpose of the function is to calculate the difference in weeks between two dates or dates with time, e.g., `toRelativeWeekNum(dt1) - toRelativeWeekNum(dt2)`.
**Syntax**
```sql theme={null}
toRelativeWeekNum(date)
```
**Arguments**
* `date` — Date or date with time. [`Date`](/reference/data-types/date) or [`DateTime`](/reference/data-types/datetime) or [`DateTime64`](/reference/data-types/datetime64)
**Returned value**
Returns the number of weeks from a fixed reference point in the past. [`UInt32`](/reference/data-types/int-uint)
**Examples**
**Get relative week numbers**
```sql title=Query theme={null}
SELECT toRelativeWeekNum(toDate('2023-01-08')) - toRelativeWeekNum(toDate('2023-01-01')) AS weeks_difference
```
```response title=Response theme={null}
┌─weeks_difference─┐
│ 1 │
└──────────────────┘
```
toRelativeYearNum
Introduced in: v1.1.0
Converts a date or date with time to the number of years elapsed since a certain fixed point in the past.
The exact point in time is an implementation detail, and therefore this function is not intended to be used
standalone. The main purpose of the function is to calculate the difference in years between two dates or dates with time, e.g., `toRelativeYearNum(dt1) - toRelativeYearNum(dt2)`.
**Syntax**
```sql theme={null}
toRelativeYearNum(date)
```
**Arguments**
* `date` — Date or date with time. [`Date`](/reference/data-types/date) or [`DateTime`](/reference/data-types/datetime) or [`DateTime64`](/reference/data-types/datetime64)
**Returned value**
Returns the number of years from a fixed reference point in the past. [`UInt16`](/reference/data-types/int-uint)
**Examples**
**Get relative year numbers**
```sql title=Query theme={null}
SELECT toRelativeYearNum('2010-10-01'::DateTime) - toRelativeYearNum('2000-01-01'::DateTime)
```
```response title=Response theme={null}
┌─minus(toRela⋯ateTime')))─┐
│ 10 │
└──────────────────────────┘
```
toSecond
Introduced in: v1.1.0
Returns the second component (0-59) of a `DateTime` or `DateTime64` value.
**Syntax**
```sql theme={null}
toSecond(datetime)
```
**Aliases**: `SECOND`
**Arguments**
* `datetime` — Date with time to get the second from. [`DateTime`](/reference/data-types/datetime) or [`DateTime64`](/reference/data-types/datetime64)
**Returned value**
Returns the second in the minute (0 - 59) of `datetime`. [`UInt8`](/reference/data-types/int-uint)
**Examples**
**Usage example**
```sql title=Query theme={null}
SELECT toSecond(toDateTime('2023-04-21 10:20:30'))
```
```response title=Response theme={null}
┌─toSecond(toDateTime('2023-04-21 10:20:30'))─┐
│ 30 │
└─────────────────────────────────────────────┘
```
toStartOfDay
Introduced in: v1.1.0
Rounds down a date with time to the start of the day.
The return type can be configured by setting [`enable_extended_results_for_datetime_functions`](/reference/settings/session-settings#enable_extended_results_for_datetime_functions).
**Syntax**
```sql theme={null}
toStartOfDay(datetime)
```
**Arguments**
* `datetime` — A date or date with time to round. [`Date`](/reference/data-types/date) or [`DateTime`](/reference/data-types/datetime)
**Returned value**
Returns the date with time rounded down to the start of the day. [`DateTime`](/reference/data-types/datetime) or [`DateTime64`](/reference/data-types/datetime64)
**Examples**
**Round down to the start of the day**
```sql title=Query theme={null}
SELECT toStartOfDay(toDateTime('2023-04-21 10:20:30'))
```
```response title=Response theme={null}
┌─toStartOfDay(toDateTime('2023-04-21 10:20:30'))─┐
│ 2023-04-21 00:00:00 │
└─────────────────────────────────────────────────┘
```
toStartOfFifteenMinutes
Introduced in: v1.1.0
Rounds down the date with time to the start of the fifteen-minute interval.
The return type can be configured by setting [`enable_extended_results_for_datetime_functions`](/reference/settings/session-settings#enable_extended_results_for_datetime_functions).
**Syntax**
```sql theme={null}
toStartOfFifteenMinutes(datetime)
```
**Arguments**
* `datetime` — A date or date with time to round. [`DateTime`](/reference/data-types/datetime) or [`DateTime64`](/reference/data-types/datetime64)
**Returned value**
Returns the date with time rounded to the start of the nearest fifteen-minute interval. [`DateTime`](/reference/data-types/datetime) or [`DateTime64`](/reference/data-types/datetime64)
**Examples**
**Example**
```sql title=Query theme={null}
SELECT
toStartOfFifteenMinutes(toDateTime('2023-04-21 10:17:00')),
toStartOfFifteenMinutes(toDateTime('2023-04-21 10:20:00')),
toStartOfFifteenMinutes(toDateTime('2023-04-21 10:23:00'))
FORMAT Vertical
```
```response title=Response theme={null}
Row 1:
──────
toStartOfFifteenMinutes(toDateTime('2023-04-21 10:17:00')): 2023-04-21 10:15:00
toStartOfFifteenMinutes(toDateTime('2023-04-21 10:20:00')): 2023-04-21 10:15:00
toStartOfFifteenMinutes(toDateTime('2023-04-21 10:23:00')): 2023-04-21 10:15:00
```
toStartOfFiveMinutes
Introduced in: v22.6.0
Rounds down a date with time to the start of the nearest five-minute interval.
The return type can be configured by setting [`enable_extended_results_for_datetime_functions`](/reference/settings/session-settings#enable_extended_results_for_datetime_functions).
**Syntax**
```sql theme={null}
toStartOfFiveMinutes(datetime)
```
**Aliases**: `toStartOfFiveMinute`
**Arguments**
* `datetime` — A date with time to round. [`DateTime`](/reference/data-types/datetime) or [`DateTime64`](/reference/data-types/datetime64)
**Returned value**
Returns the date with time rounded to the start of the nearest five-minute interval. [`DateTime`](/reference/data-types/datetime) or [`DateTime64`](/reference/data-types/datetime64)
**Examples**
**Example**
```sql title=Query theme={null}
SELECT
toStartOfFiveMinutes(toDateTime('2023-04-21 10:17:00')),
toStartOfFiveMinutes(toDateTime('2023-04-21 10:20:00')),
toStartOfFiveMinutes(toDateTime('2023-04-21 10:23:00'))
FORMAT Vertical
```
```response title=Response theme={null}
Row 1:
──────
toStartOfFiveMinutes(toDateTime('2023-04-21 10:17:00')): 2023-04-21 10:15:00
toStartOfFiveMinutes(toDateTime('2023-04-21 10:20:00')): 2023-04-21 10:20:00
toStartOfFiveMinutes(toDateTime('2023-04-21 10:23:00')): 2023-04-21 10:20:00
```
toStartOfHour
Introduced in: v1.1.0
Rounds down a date with time to the start of the hour.
The return type can be configured by setting [`enable_extended_results_for_datetime_functions`](/reference/settings/session-settings#enable_extended_results_for_datetime_functions).
**Syntax**
```sql theme={null}
toStartOfHour(datetime)
```
**Arguments**
* `datetime` — A date with time to round. [`DateTime`](/reference/data-types/datetime) or [`DateTime64`](/reference/data-types/datetime64)
**Returned value**
Returns the date with time rounded down to the start of the hour. [`DateTime`](/reference/data-types/datetime) or [`DateTime64`](/reference/data-types/datetime64)
**Examples**
**Round down to the start of the hour**
```sql title=Query theme={null}
SELECT
toStartOfHour(toDateTime('2023-04-21 10:20:30'));
```
```response title=Response theme={null}
┌─────────────────res─┬─toTypeName(res)─┐
│ 2023-04-21 10:00:00 │ DateTime │
└─────────────────────┴─────────────────┘
```
toStartOfISOYear
Introduced in: v1.1.0
Rounds down a date or date with time to the first day of the ISO year, which can be different than a regular year. See [ISO week date](https://en.wikipedia.org/wiki/ISO_week_date).
The return type can be configured by setting [`enable_extended_results_for_datetime_functions`](/reference/settings/session-settings#enable_extended_results_for_datetime_functions).
**Syntax**
```sql theme={null}
toStartOfISOYear(value)
```
**Arguments**
* `value` — The date or date with time to round down to the first day of the ISO year. [`Date`](/reference/data-types/date) or [`Date32`](/reference/data-types/date32) or [`DateTime`](/reference/data-types/datetime) or [`DateTime64`](/reference/data-types/datetime64)
**Returned value**
Returns the first day of the ISO year for the given date or date with time. [`Date`](/reference/data-types/date)
**Examples**
**Round down to the first day of the ISO year**
```sql title=Query theme={null}
SELECT toStartOfISOYear(toDateTime('2023-04-21 10:20:30'))
```
```response title=Response theme={null}
┌─toStartOfISOYear(toDateTime('2023-04-21 10:20:30'))─┐
│ 2023-01-02 │
└─────────────────────────────────────────────────────┘
```
toStartOfInterval
Introduced in: v20.1.0
This function generalizes other `toStartOf*()` functions with `toStartOfInterval(date_or_date_with_time, INTERVAL x unit [, time_zone])` syntax.
For example,
* `toStartOfInterval(t, INTERVAL 1 YEAR)` returns the same as `toStartOfYear(t)`,
* `toStartOfInterval(t, INTERVAL 1 MONTH)` returns the same as `toStartOfMonth(t)`,
* `toStartOfInterval(t, INTERVAL 1 DAY)` returns the same as `toStartOfDay(t)`,
* `toStartOfInterval(t, INTERVAL 15 MINUTE)` returns the same as `toStartOfFifteenMinutes(t)`.
The calculation is performed relative to specific points in time:
| Interval | Start |
| ------------------------------------------------------------------------------------------------------------------------------------------ | ---------------------- |
| YEAR | year 0 |
| QUARTER | 1900 Q1 |
| MONTH | 1900 January |
| WEEK | 1970, 1st week (01-05) |
| DAY | 1970-01-01 |
| HOUR | (\*) |
| MINUTE | 1970-01-01 00:00:00 |
| SECOND | 1970-01-01 00:00:00 |
| MILLISECOND | 1970-01-01 00:00:00 |
| MICROSECOND | 1970-01-01 00:00:00 |
| NANOSECOND | 1970-01-01 00:00:00 |
| (\*) hour intervals are special: the calculation is always performed relative to 00:00:00 (midnight) of the current day. As a result, only | |
| hour values between 1 and 23 are useful. | |
If unit `WEEK` was specified, `toStartOfInterval` assumes that weeks start on Monday. Note that this behavior is different from that of function `toStartOfWeek` in which weeks start by default on Sunday.
The second overload emulates TimescaleDB's `time_bucket()` function, respectively PostgreSQL's `date_bin()` function.
**Syntax**
```sql theme={null}
toStartOfInterval(value, INTERVAL x unit[, time_zone])
toStartOfInterval(value, INTERVAL x unit[, origin[, time_zone]])
```
**Aliases**: `time_bucket`, `date_bin`
**Arguments**
* `value` — Date or date with time value to round down. [`Date`](/reference/data-types/date) or [`DateTime`](/reference/data-types/datetime) or [`DateTime64`](/reference/data-types/datetime64)
* `x` — Interval length number. - `unit` — Interval unit: YEAR, QUARTER, MONTH, WEEK, DAY, HOUR, MINUTE, SECOND, MILLISECOND, MICROSECOND, NANOSECOND. - `time_zone` — Optional. Time zone name as a string. - `origin` — Optional. Origin point for calculation (second overload only).
**Returned value**
Returns the start of the interval containing the input value. [`DateTime`](/reference/data-types/datetime)
**Examples**
**Basic interval rounding**
```sql title=Query theme={null}
SELECT toStartOfInterval(toDateTime('2023-01-15 14:30:00'), INTERVAL 1 MONTH)
```
```response title=Response theme={null}
┌─toStartOfInt⋯alMonth(1))─┐
│ 2023-01-01 │
└──────────────────────────┘
```
**Using origin point**
```sql title=Query theme={null}
SELECT toStartOfInterval(toDateTime('2023-01-01 14:45:00'), INTERVAL 1 MINUTE, toDateTime('2023-01-01 14:35:30'))
```
```response title=Response theme={null}
┌─toStartOfInt⋯14:35:30'))─┐
│ 2023-01-01 14:44:30 │
└──────────────────────────┘
```
toStartOfMicrosecond
Introduced in: v22.6.0
Rounds down a date with time to the start of the microseconds.
**Syntax**
```sql theme={null}
toStartOfMicrosecond(datetime[, timezone])
```
**Arguments**
* `datetime` — Date and time. [`DateTime64`](/reference/data-types/datetime64)
* `timezone` — Optional. Timezone for the returned value. If not specified, the function uses the timezone of the `value` parameter. [`String`](/reference/data-types/string)
**Returned value**
Input value with sub-microseconds [`DateTime64`](/reference/data-types/datetime64)
**Examples**
**Query without timezone**
```sql title=Query theme={null}
WITH toDateTime64('2020-01-01 10:20:30.999999999', 9) AS dt64
SELECT toStartOfMicrosecond(dt64);
```
```response title=Response theme={null}
┌────toStartOfMicrosecond(dt64)─┐
│ 2020-01-01 10:20:30.999999000 │
└───────────────────────────────┘
```
**Query with timezone**
```sql title=Query theme={null}
WITH toDateTime64('2020-01-01 10:20:30.999999999', 9) AS dt64
SELECT toStartOfMicrosecond(dt64, 'Asia/Istanbul');
```
```response title=Response theme={null}
┌─toStartOfMicrosecond(dt64, 'Asia/Istanbul')─┐
│ 2020-01-01 12:20:30.999999000 │
└─────────────────────────────────────────────┘
```
toStartOfMillisecond
Introduced in: v22.6.0
Rounds down a date with time to the start of the milliseconds.
**Syntax**
```sql theme={null}
toStartOfMillisecond(datetime[, timezone])
```
**Arguments**
* `datetime` — Date and time. [`DateTime64`](/reference/data-types/datetime64)
* `timezone` — Optional. Timezone for the returned value. If not specified, the function uses the timezone of the `value` parameter. [`String`](/reference/data-types/string)
**Returned value**
Input value with sub-milliseconds. [`DateTime64`](/reference/data-types/datetime64)
**Examples**
**Query without timezone**
```sql title=Query theme={null}
WITH toDateTime64('2020-01-01 10:20:30.999999999', 9) AS dt64
SELECT toStartOfMillisecond(dt64);
```
```response title=Response theme={null}
┌────toStartOfMillisecond(dt64)─┐
│ 2020-01-01 10:20:30.999000000 │
└───────────────────────────────┘
```
**Query with timezone**
```sql title=Query theme={null}
WITH toDateTime64('2020-01-01 10:20:30.999999999', 9) AS dt64
SELECT toStartOfMillisecond(dt64, 'Asia/Istanbul');
```
```response title=Response theme={null}
┌─toStartOfMillisecond(dt64, 'Asia/Istanbul')─┐
│ 2020-01-01 12:20:30.999000000 │
└─────────────────────────────────────────────┘
```
toStartOfMinute
Introduced in: v1.1.0
Rounds down a date with time to the start of the minute.
The return type can be configured by setting [`enable_extended_results_for_datetime_functions`](/reference/settings/session-settings#enable_extended_results_for_datetime_functions).
**Syntax**
```sql theme={null}
toStartOfMinute(datetime)
```
**Arguments**
* `datetime` — A date with time to round. [`DateTime`](/reference/data-types/datetime) or [`DateTime64`](/reference/data-types/datetime64)
**Returned value**
Returns the date with time rounded down to the start of the minute. [`DateTime`](/reference/data-types/datetime) or [`DateTime64`](/reference/data-types/datetime64)
**Examples**
**Round down to the start of the minute**
```sql title=Query theme={null}
SELECT
toStartOfMinute(toDateTime('2023-04-21 10:20:30')),
toStartOfMinute(toDateTime64('2023-04-21 10:20:30.5300', 8))
FORMAT Vertical
```
```response title=Response theme={null}
Row 1:
──────
toStartOfMinute(toDateTime('2023-04-21 10:20:30')): 2023-04-21 10:20:00
toStartOfMinute(toDateTime64('2023-04-21 10:20:30.5300', 8)): 2023-04-21 10:20:00
```
toStartOfMonth
Introduced in: v1.1.0
Rounds down a date or date with time to the first day of the month.
The return type can be configured by setting [`enable_extended_results_for_datetime_functions`](/reference/settings/session-settings#enable_extended_results_for_datetime_functions).
**Syntax**
```sql theme={null}
toStartOfMonth(value)
```
**Arguments**
* `value` — The date or date with time to round down to the first day of the month. [`Date`](/reference/data-types/date) or [`Date32`](/reference/data-types/date32) or [`DateTime`](/reference/data-types/datetime) or [`DateTime64`](/reference/data-types/datetime64)
**Returned value**
Returns the first day of the month for the given date or date with time. [`Date`](/reference/data-types/date)
**Examples**
**Round down to the first day of the month**
```sql title=Query theme={null}
SELECT toStartOfMonth(toDateTime('2023-04-21 10:20:30'))
```
```response title=Response theme={null}
┌─toStartOfMonth(toDateTime('2023-04-21 10:20:30'))─┐
│ 2023-04-01 │
└───────────────────────────────────────────────────┘
```
toStartOfNanosecond
Introduced in: v22.6.0
Rounds down a date with time to the start of the nanoseconds.
**Syntax**
```sql theme={null}
toStartOfNanosecond(datetime[, timezone])
```
**Arguments**
* `datetime` — Date and time. [`DateTime64`](/reference/data-types/datetime64)
* `timezone` — Optional. Timezone for the returned value. If not specified, the function uses the timezone of the `value` parameter. [`String`](/reference/data-types/string)
**Returned value**
Input value with nanoseconds. [`DateTime64`](/reference/data-types/datetime64)
**Examples**
**Query without timezone**
```sql title=Query theme={null}
WITH toDateTime64('2020-01-01 10:20:30.999999999', 9) AS dt64
SELECT toStartOfNanosecond(dt64);
```
```response title=Response theme={null}
┌─────toStartOfNanosecond(dt64)─┐
│ 2020-01-01 10:20:30.999999999 │
└───────────────────────────────┘
```
**Query with timezone**
```sql title=Query theme={null}
WITH toDateTime64('2020-01-01 10:20:30.999999999', 9) AS dt64
SELECT toStartOfNanosecond(dt64, 'Asia/Istanbul');
```
```response title=Response theme={null}
┌─toStartOfNanosecond(dt64, 'Asia/Istanbul')─┐
│ 2020-01-01 12:20:30.999999999 │
└────────────────────────────────────────────┘
```
toStartOfQuarter
Introduced in: v1.1.0
Rounds down a date or date with time to the first day of the quarter. The first day of the quarter is either 1 January, 1 April, 1 July, or 1 October.
The return type can be configured by setting [`enable_extended_results_for_datetime_functions`](/reference/settings/session-settings#enable_extended_results_for_datetime_functions).
**Syntax**
```sql theme={null}
toStartOfQuarter(value)
```
**Arguments**
* `value` — The date or date with time to round down to the first day of the quarter. [`Date`](/reference/data-types/date) or [`Date32`](/reference/data-types/date32) or [`DateTime`](/reference/data-types/datetime) or [`DateTime64`](/reference/data-types/datetime64)
**Returned value**
Returns the first day of the quarter for the given date or date with time. [`Date`](/reference/data-types/date)
**Examples**
**Round down to the first day of the quarter**
```sql title=Query theme={null}
SELECT toStartOfQuarter(toDateTime('2023-04-21 10:20:30'))
```
```response title=Response theme={null}
┌─toStartOfQuarter(toDateTime('2023-04-21 10:20:30'))─┐
│ 2023-04-01 │
└─────────────────────────────────────────────────────┘
```
toStartOfSecond
Introduced in: v20.5.0
Rounds down a date with time to the start of the seconds.
**Syntax**
```sql theme={null}
toStartOfSecond(datetime[, timezone])
```
**Arguments**
* `datetime` — Date and time to truncate sub-seconds from. [`DateTime64`](/reference/data-types/datetime64)
* `timezone` — Optional. Timezone for the returned value. If not specified, the function uses the timezone of the `value` parameter. [`String`](/reference/data-types/string)
**Returned value**
Returns the input value without sub-seconds. [`DateTime64`](/reference/data-types/datetime64)
**Examples**
**Query without timezone**
```sql title=Query theme={null}
WITH toDateTime64('2020-01-01 10:20:30.999', 3) AS dt64
SELECT toStartOfSecond(dt64);
```
```response title=Response theme={null}
┌───toStartOfSecond(dt64)─┐
│ 2020-01-01 10:20:30.000 │
└─────────────────────────┘
```
**Query with timezone**
```sql title=Query theme={null}
WITH toDateTime64('2020-01-01 10:20:30.999', 3) AS dt64
SELECT toStartOfSecond(dt64, 'Asia/Istanbul');
```
```response title=Response theme={null}
┌─toStartOfSecond(dt64, 'Asia/Istanbul')─┐
│ 2020-01-01 13:20:30.000 │
└────────────────────────────────────────┘
```
toStartOfTenMinutes
Introduced in: v20.1.0
Rounds down a date with time to the start of the nearest ten-minute interval.
The return type can be configured by setting [`enable_extended_results_for_datetime_functions`](/reference/settings/session-settings#enable_extended_results_for_datetime_functions).
**Syntax**
```sql theme={null}
toStartOfTenMinutes(datetime)
```
**Arguments**
* `datetime` — A date with time. [`DateTime`](/reference/data-types/datetime) or [`DateTime64`](/reference/data-types/datetime64)
**Returned value**
Returns the date with time rounded to the start of the nearest ten-minute interval. [`DateTime`](/reference/data-types/datetime) or [`DateTime64`](/reference/data-types/datetime64)
**Examples**
**Example**
```sql title=Query theme={null}
SELECT
toStartOfTenMinutes(toDateTime('2023-04-21 10:17:00')),
toStartOfTenMinutes(toDateTime('2023-04-21 10:20:00')),
toStartOfTenMinutes(toDateTime('2023-04-21 10:23:00'))
FORMAT Vertical
```
```response title=Response theme={null}
Row 1:
──────
toStartOfTenMinutes(toDateTime('2023-04-21 10:17:00')): 2023-04-21 10:10:00
toStartOfTenMinutes(toDateTime('2023-04-21 10:20:00')): 2023-04-21 10:20:00
toStartOfTenMinutes(toDateTime('2023-04-21 10:23:00')): 2023-04-21 10:20:00
```
toStartOfWeek
Introduced in: v20.1.0
Rounds a date or date with time down to the nearest Sunday or Monday.
The return type can be configured by setting [`enable_extended_results_for_datetime_functions`](/reference/settings/session-settings#enable_extended_results_for_datetime_functions).
**Syntax**
```sql theme={null}
toStartOfWeek(datetime[, mode[, timezone]])
```
**Arguments**
* `datetime` — A date or date with time to convert. [`Date`](/reference/data-types/date) or [`DateTime`](/reference/data-types/datetime) or [`Date32`](/reference/data-types/date32) or [`DateTime64`](/reference/data-types/datetime64)
* `mode` — Determines the first day of the week as described in the `toWeek()` function. Default `0`. [`UInt8`](/reference/data-types/int-uint)
* `timezone` — The timezone to use for the conversion. If not specified, the server's timezone is used. [`String`](/reference/data-types/string)
**Returned value**
Returns the date of the nearest Sunday or Monday on, or prior to, the given date, depending on the mode. [`Date`](/reference/data-types/date) or [`Date32`](/reference/data-types/date32)
**Examples**
**Round down to the nearest Sunday or Monday**
```sql title=Query theme={null}
SELECT
toStartOfWeek(toDateTime('2023-04-21 10:20:30')), /* a Friday */
toStartOfWeek(toDateTime('2023-04-21 10:20:30'), 1), /* a Friday */
toStartOfWeek(toDate('2023-04-24')), /* a Monday */
toStartOfWeek(toDate('2023-04-24'), 1) /* a Monday */
FORMAT Vertical
```
```response title=Response theme={null}
Row 1:
──────
toStartOfWeek(toDateTime('2023-04-21 10:20:30')): 2023-04-17
toStartOfWeek(toDateTime('2023-04-21 10:20:30'), 1): 2023-04-17
toStartOfWeek(toDate('2023-04-24')): 2023-04-24
toStartOfWeek(toDate('2023-04-24'), 1): 2023-04-24
```
toStartOfYear
Introduced in: v1.1.0
Rounds down a date or date with time to the first day of the year. Returns the date as a `Date` object.
The return type can be configured by setting [`enable_extended_results_for_datetime_functions`](/reference/settings/session-settings#enable_extended_results_for_datetime_functions).
**Syntax**
```sql theme={null}
toStartOfYear(value)
```
**Arguments**
* `value` — The date or date with time to round down. [`Date`](/reference/data-types/date) or [`Date32`](/reference/data-types/date32) or [`DateTime`](/reference/data-types/datetime) or [`DateTime64`](/reference/data-types/datetime64)
**Returned value**
Returns the first day of the year for the given date/time [`Date`](/reference/data-types/date)
**Examples**
**Round down to the first day of the year**
```sql title=Query theme={null}
SELECT toStartOfYear(toDateTime('2023-04-21 10:20:30'))
```
```response title=Response theme={null}
┌─toStartOfYear(toDateTime('2023-04-21 10:20:30'))─┐
│ 2023-01-01 │
└──────────────────────────────────────────────────┘
```
toTimeWithFixedDate
Introduced in: v1.1.0
Extracts the time component of a date or date with time.
The returned result is an offset to a fixed point in time, currently `1970-01-02`,
but the exact point in time is an implementation detail which may change in future.
`toTime` should therefore not be used standalone.
The main purpose of the function is to calculate the time difference between two dates or dates with time, e.g., `toTime(dt1) - toTime(dt2)`.
**Syntax**
```sql theme={null}
toTimeWithFixedDate(date[, timezone])
```
**Arguments**
* `date` — Date to convert to a time. [`Date`](/reference/data-types/date) or [`DateTime`](/reference/data-types/datetime) or [`DateTime64`](/reference/data-types/datetime64)
* `timezone` — Optional. Timezone for the returned value. [`String`](/reference/data-types/string)
**Returned value**
Returns the time component of a date or date with time in the form of an offset to a fixed point in time (selected as 1970-01-02, currently). [`DateTime`](/reference/data-types/datetime)
**Examples**
**Calculate the time difference between two dates**
```sql title=Query theme={null}
SELECT toTimeWithFixedDate('2025-06-15 12:00:00'::DateTime) - toTimeWithFixedDate('2024-05-10 11:00:00'::DateTime) AS result, toTypeName(result)
```
```response title=Response theme={null}
┌─result─┬─toTypeName(result)─┐
│ 3600 │ Int32 │
└────────┴────────────────────┘
```
toTimezone
Introduced in: v1.1.0
Converts a `DateTime` or `DateTime64` to the specified time zone.
The internal value (number of unix seconds) of the data doesn't change.
Only the value's time zone attribute and the value's string representation changes.
**Syntax**
```sql theme={null}
toTimezone(datetime, timezone)
```
**Aliases**: `toTimeZone`
**Arguments**
* `date` — The value to convert. [`DateTime`](/reference/data-types/datetime) or [`DateTime64`](/reference/data-types/datetime64)
* `timezone` — The target time zone name. [`String`](/reference/data-types/string)
**Returned value**
Returns the same timestamp as the input, but with the specified time zone [`DateTime`](/reference/data-types/datetime) or [`DateTime64`](/reference/data-types/datetime64)
**Examples**
**Usage example**
```sql title=Query theme={null}
SELECT toDateTime('2019-01-01 00:00:00', 'UTC') AS time_utc,
toTypeName(time_utc) AS type_utc,
toInt32(time_utc) AS int32utc,
toTimezone(time_utc, 'Asia/Yekaterinburg') AS time_yekat,
toTypeName(time_yekat) AS type_yekat,
toInt32(time_yekat) AS int32yekat,
toTimezone(time_utc, 'US/Samoa') AS time_samoa,
toTypeName(time_samoa) AS type_samoa,
toInt32(time_samoa) AS int32samoa
FORMAT Vertical;
```
```response title=Response theme={null}
Row 1:
──────
time_utc: 2019-01-01 00:00:00
type_utc: DateTime('UTC')
int32utc: 1546300800
time_yekat: 2019-01-01 05:00:00
type_yekat: DateTime('Asia/Yekaterinburg')
int32yekat: 1546300800
time_samoa: 2018-12-31 13:00:00
type_samoa: DateTime('US/Samoa')
int32samoa: 1546300800
```
toUTCTimestamp
Introduced in: v23.8.0
Converts a date or date with time value from one time zone to UTC timezone timestamp. This function is mainly included for compatibility with Apache Spark and similar frameworks.
**Syntax**
```sql theme={null}
toUTCTimestamp(datetime, time_zone)
```
**Aliases**: `to_utc_timestamp`
**Arguments**
* `datetime` — A date or date with time type const value or an expression. [`DateTime`](/reference/data-types/datetime) or [`DateTime64`](/reference/data-types/datetime64)
* `time_zone` — A String type const value or an expression representing the time zone. [`String`](/reference/data-types/string)
**Returned value**
Returns a date or date with time in UTC timezone. [`DateTime`](/reference/data-types/datetime) or [`DateTime64`](/reference/data-types/datetime64)
**Examples**
**Convert timezone to UTC**
```sql title=Query theme={null}
SELECT toUTCTimestamp(toDateTime('2023-03-16'), 'Asia/Shanghai')
```
```response title=Response theme={null}
┌─toUTCTimestamp(toDateTime('2023-03-16'), 'Asia/Shanghai')─┐
│ 2023-03-15 16:00:00 │
└─────────────────────────────────────────────────────────┘
```
toUnixTimestamp
Introduced in: v1.1.0
Converts a `String`, `Date`, or `DateTime` to a Unix timestamp (seconds since `1970-01-01 00:00:00 UTC`) as `UInt32`.
**Syntax**
```sql theme={null}
toUnixTimestamp(date[, timezone])
```
**Arguments**
* `date` — Value to convert. [`Date`](/reference/data-types/date) or [`Date32`](/reference/data-types/date32) or [`DateTime`](/reference/data-types/datetime) or [`DateTime64`](/reference/data-types/datetime64) or [`String`](/reference/data-types/string)
* `timezone` — Optional. Timezone to use for conversion. If not specified, the server's timezone is used. [`String`](/reference/data-types/string)
**Returned value**
Returns the Unix timestamp. [`UInt32`](/reference/data-types/int-uint)
**Examples**
**Usage example**
```sql title=Query theme={null}
SELECT
'2017-11-05 08:07:47' AS dt_str,
toUnixTimestamp(dt_str) AS from_str,
toUnixTimestamp(dt_str, 'Asia/Tokyo') AS from_str_tokyo,
toUnixTimestamp(toDateTime(dt_str)) AS from_datetime,
toUnixTimestamp(toDateTime64(dt_str, 0)) AS from_datetime64,
toUnixTimestamp(toDate(dt_str)) AS from_date,
toUnixTimestamp(toDate32(dt_str)) AS from_date32
FORMAT Vertical;
```
```response title=Response theme={null}
Row 1:
──────
dt_str: 2017-11-05 08:07:47
from_str: 1509869267
from_str_tokyo: 1509836867
from_datetime: 1509869267
from_datetime64: 1509869267
from_date: 1509840000
from_date32: 1509840000
```
toWeek
Introduced in: v20.1.0
This function returns the week number for date or datetime. The two-argument form of `toWeek()` enables you to specify whether the week starts
on Sunday or Monday and whether the return value should be in the range from `0` to `53` or from `1` to `53`.
[`toISOWeek()`](#toWeek) is a compatibility function that is equivalent to `toWeek(date,3)`.
The following table describes how the mode argument works.
| Mode | First day of week | Range | Week 1 is the first week ... |
| ---- | ----------------- | ----- | ----------------------------- |
| 0 | Sunday | 0-53 | with a Sunday in this year |
| 1 | Monday | 0-53 | with 4 or more days this year |
| 2 | Sunday | 1-53 | with a Sunday in this year |
| 3 | Monday | 1-53 | with 4 or more days this year |
| 4 | Sunday | 0-53 | with 4 or more days this year |
| 5 | Monday | 0-53 | with a Monday in this year |
| 6 | Sunday | 1-53 | with 4 or more days this year |
| 7 | Monday | 1-53 | with a Monday in this year |
| 8 | Sunday | 1-53 | contains January 1 |
| 9 | Monday | 1-53 | contains January 1 |
For mode values with a meaning of "with 4 or more days this year," weeks are numbered according to ISO 8601:1988:
* If the week containing January 1 has 4 or more days in the new year, it is week 1.
* Otherwise, it is the last week of the previous year, and the next week is week 1.
For mode values with a meaning of "contains January 1", the week contains January 1 is week 1.
It does not matter how many days in the new year the week contained, even if it contained only one day.
I.e. if the last week of December contains January 1 of the next year, it will be week 1 of the next year.
The first argument can also be specified as [`String`](/reference/data-types/string) in a format supported by [`parseDateTime64BestEffort()`](/reference/functions/regular-functions/type-conversion-functions#parseDateTime64BestEffort). Support for string arguments exists only for reasons of compatibility with MySQL which is expected by certain 3rd party tools. As string argument support may in future be made dependent on new MySQL-compatibility settings and because string parsing is generally slow, it is recommended to not use it.
**Syntax**
```sql theme={null}
toWeek(datetime[, mode[, time_zone]])
```
**Aliases**: `week`
**Arguments**
* `datetime` — Date or date with time to get the week number from. [`Date`](/reference/data-types/date) or [`DateTime`](/reference/data-types/datetime)
* `mode` — Optional. A mode `0` to `9` determines the first day of the week and the range of the week number. Default `0`. - `time_zone` — Optional. Time zone. [`String`](/reference/data-types/string)
**Returned value**
Returns the week number according to the specified mode. [`UInt32`](/reference/data-types/int-uint)
**Examples**
**Get week numbers with different modes**
```sql title=Query theme={null}
SELECT toDate('2016-12-27') AS date, toWeek(date) AS week0, toWeek(date,1) AS week1, toWeek(date,9) AS week9
```
```response title=Response theme={null}
┌───────date─┬─week0─┬─week1─┬─week9─┐
│ 2016-12-27 │ 52 │ 52 │ 1 │
└────────────┴───────┴───────┴───────┘
```
toYYYYMM
Introduced in: v1.1.0
Converts a date or date with time to a `UInt32` number containing the year and month number (YYYY \* 100 + MM).
Accepts a second optional timezone argument. If provided, the timezone must be a string constant.
This function is the opposite of function `YYYYMMDDToDate()`.
**Syntax**
```sql theme={null}
toYYYYMM(datetime[, timezone])
```
**Arguments**
* `datetime` — A date or date with time to convert. [`Date`](/reference/data-types/date) or [`Date32`](/reference/data-types/date32) or [`DateTime`](/reference/data-types/datetime) or [`DateTime64`](/reference/data-types/datetime64)
* `timezone` — Optional. Timezone for the conversion. If provided, the timezone must be a string constant. [`String`](/reference/data-types/string)
**Returned value**
Returns a UInt32 number containing the year and month number (YYYY \* 100 + MM). [`UInt32`](/reference/data-types/int-uint)
**Examples**
**Convert current date to YYYYMM format**
```sql title=Query theme={null}
SELECT toYYYYMM(now(), 'US/Eastern')
```
```response title=Response theme={null}
┌─toYYYYMM(now(), 'US/Eastern')─┐
│ 202303 │
└───────────────────────────────┘
```
toYYYYMMDD
Introduced in: v1.1.0
Converts a date or date with time to a `UInt32` number containing the year and month number (YYYY \* 10000 + MM \* 100 + DD). Accepts a second optional timezone argument. If provided, the timezone must be a string constant.
**Syntax**
```sql theme={null}
toYYYYMMDD(datetime[, timezone])
```
**Arguments**
* `datetime` — A date or date with time to convert. [`Date`](/reference/data-types/date) or [`Date32`](/reference/data-types/date32) or [`DateTime`](/reference/data-types/datetime) or [`DateTime64`](/reference/data-types/datetime64)
* `timezone` — Optional. Timezone for the conversion. If provided, the timezone must be a string constant. [`String`](/reference/data-types/string)
**Returned value**
Returns a `UInt32` number containing the year, month and day (YYYY \* 10000 + MM \* 100 + DD). [`UInt32`](/reference/data-types/int-uint)
**Examples**
**Convert current date to YYYYMMDD format**
```sql title=Query theme={null}
SELECT toYYYYMMDD(now(), 'US/Eastern')
```
```response title=Response theme={null}
┌─toYYYYMMDD(now(), 'US/Eastern')─┐
│ 20230302 │
└─────────────────────────────────┘
```
toYYYYMMDDhhmmss
Introduced in: v1.1.0
Converts a date or date with time to a `UInt64` number containing the year and month number (YYYY \* 10000000000 + MM \* 100000000 + DD \* 1000000 + hh \* 10000 + mm \* 100 + ss).
Accepts a second optional timezone argument. If provided, the timezone must be a string constant.
**Syntax**
```sql theme={null}
toYYYYMMDDhhmmss(datetime[, timezone])
```
**Arguments**
* `datetime` — Date or date with time to convert. [`Date`](/reference/data-types/date) or [`Date32`](/reference/data-types/date32) or [`DateTime`](/reference/data-types/datetime) or [`DateTime64`](/reference/data-types/datetime64)
* `timezone` — Optional. Timezone for the conversion. If provided, the timezone must be a string constant. [`String`](/reference/data-types/string)
**Returned value**
Returns a `UInt64` number containing the year, month, day, hour, minute and second (YYYY \* 10000000000 + MM \* 100000000 + DD \* 1000000 + hh \* 10000 + mm \* 100 + ss). [`UInt64`](/reference/data-types/int-uint)
**Examples**
**Convert current date and time to YYYYMMDDhhmmss format**
```sql title=Query theme={null}
SELECT toYYYYMMDDhhmmss(now(), 'US/Eastern')
```
```response title=Response theme={null}
┌─toYYYYMMDDhhmmss(now(), 'US/Eastern')─┐
│ 20230302112209 │
└───────────────────────────────────────┘
```
toYear
Introduced in: v1.1.0
Returns the year component (AD) of a `Date` or `DateTime` value.
**Syntax**
```sql theme={null}
toYear(datetime)
```
**Aliases**: `YEAR`
**Arguments**
* `datetime` — Date or date with time to get the year from. [`Date`](/reference/data-types/date) or [`Date32`](/reference/data-types/date32) or [`DateTime`](/reference/data-types/datetime) or [`DateTime64`](/reference/data-types/datetime64)
**Returned value**
Returns the year of the given Date or DateTime [`UInt16`](/reference/data-types/int-uint)
**Examples**
**Usage example**
```sql title=Query theme={null}
SELECT toYear(toDateTime('2023-04-21 10:20:30'))
```
```response title=Response theme={null}
┌─toYear(toDateTime('2023-04-21 10:20:30'))─┐
│ 2023 │
└───────────────────────────────────────────┘
```
toYearNumSinceEpoch
Introduced in: v25.3.0
Returns amount of years passed from year 1970
**Syntax**
```sql theme={null}
toYearNumSinceEpoch(date)
```
**Arguments**
* `date` — A date or date with time to convert. [`Date`](/reference/data-types/date) or [`DateTime`](/reference/data-types/datetime) or [`DateTime64`](/reference/data-types/datetime64)
**Returned value**
Positive integer
**Examples**
**Example**
```sql title=Query theme={null}
SELECT toYearNumSinceEpoch(toDate('2024-10-01'))
```
```response title=Response theme={null}
54
```
toYearWeek
Introduced in: v20.1.0
Returns the year and week for a date. The year in the result may be different from the year in the date argument for the first and the last week of the year.
The mode argument works like the mode argument of [`toWeek()`](/reference/functions/regular-functions/date-time-functions#toWeek).
Warning: The week number returned by `toYearWeek()` can be different from what the `toWeek()` returns. `toWeek()` always returns week number in the context of the given year, and in case `toWeek()` returns `0`, `toYearWeek()` returns the value corresponding to the last week of previous year. See `prev_yearWeek` in example below.
The first argument can also be specified as [`String`](/reference/data-types/string) in a format supported by [`parseDateTime64BestEffort()`](/reference/functions/regular-functions/type-conversion-functions#parseDateTime64BestEffort). Support for string arguments exists only for reasons of compatibility with MySQL which is expected by certain 3rd party tools. As string argument support may in future be made dependent on new MySQL-compatibility settings and because string parsing is generally slow, it is recommended to not use it.
**Syntax**
```sql theme={null}
toYearWeek(datetime[, mode[, timezone]])
```
**Aliases**: `yearweek`
**Arguments**
* `datetime` — Date or date with time to get the year and week of. [`Date`](/reference/data-types/date) or [`DateTime`](/reference/data-types/datetime)
* `mode` — Optional. A mode `0` to `9` determines the first day of the week and the range of the week number. Default `0`. - `timezone` — Optional. Time zone. [`String`](/reference/data-types/string)
**Returned value**
Returns year and week number as a combined integer value. [`UInt32`](/reference/data-types/int-uint)
**Examples**
**Get year-week combinations with different modes**
```sql title=Query theme={null}
SELECT toDate('2016-12-27') AS date, toYearWeek(date) AS yearWeek0, toYearWeek(date,1) AS yearWeek1, toYearWeek(date,9) AS yearWeek9, toYearWeek(toDate('2022-01-01')) AS prev_yearWeek
```
```response title=Response theme={null}
┌───────date─┬─yearWeek0─┬─yearWeek1─┬─yearWeek9─┬─prev_yearWeek─┐
│ 2016-12-27 │ 201652 │ 201652 │ 201701 │ 202152 │
└────────────┴───────────┴───────────┴───────────┴───────────────┘
```
today
Introduced in: v1.1.0
Returns the current date at moment of query analysis. Same as `toDate(now())`.
**Syntax**
```sql theme={null}
today()
```
**Aliases**: `curdate`, `current_date`
**Arguments**
* None.
**Returned value**
Returns the current date [`Date`](/reference/data-types/date)
**Examples**
**Usage example**
```sql title=Query theme={null}
SELECT today() AS today, curdate() AS curdate, current_date() AS current_date FORMAT Pretty
```
```response title=Response theme={null}
┏━━━━━━━━━━━━┳━━━━━━━━━━━━┳━━━━━━━━━━━━━━┓
┃ today ┃ curdate ┃ current_date ┃
┡━━━━━━━━━━━━╇━━━━━━━━━━━━╇━━━━━━━━━━━━━━┩
│ 2025-03-03 │ 2025-03-03 │ 2025-03-03 │
└────────────┴────────────┴──────────────┘
```
**SQL standard syntax without parentheses**
```sql title=Query theme={null}
SELECT TODAY, CURDATE,CURRENT_DATE
```
```response title=Response theme={null}
┏━━━━━━━━━━━━┳━━━━━━━━━━━━┳━━━━━━━━━━━━━━┓
┃ TODAY ┃ CURDATE ┃ CURRENT_DATE ┃
┡━━━━━━━━━━━━╇━━━━━━━━━━━━╇━━━━━━━━━━━━━━┩
│ 2025-03-04 │ 2025-03-04 │ 2025-03-04 │
└────────────┴────────────┴──────────────┘
```
yesterday
Introduced in: v1.1.0
Accepts zero arguments and returns yesterday's date at one of the moments of query analysis.
**Syntax**
```sql theme={null}
yesterday()
```
**Arguments**
* None.
**Returned value**
Returns yesterday's date. [`Date`](/reference/data-types/date)
**Examples**
**Get yesterday's date**
```sql title=Query theme={null}
SELECT yesterday();
SELECT today() - 1;
```
```response title=Response theme={null}
┌─yesterday()─┐
│ 2025-06-09 │
└─────────────┘
┌─minus(today(), 1)─┐
│ 2025-06-09 │
└───────────────────┘
```