ReferenceSQL ReferenceFunctions

Dates and Times

Dates and Times functions reference.

UTCTimestamp

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 is the preferred usage.

Syntax

UTCTimestamp()

Returned value

Returns the current date and time at the moment of query analysis. DateTime

Examples

Get current UTC timestamp

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

Introduced in version 22.11.

YYYYMMDDToDate

Converts a number containing the year, month and day number to a Date. This function is the opposite of function toYYYYMMDD(). The output is undefined if the input does not encode a valid Date value.

Syntax

YYYYMMDDToDate(YYYYMMDD)

Arguments

Returned value

Returns a Date value from the provided arguments Date

Examples

Example

SELECT YYYYMMDDToDate(20230911);
┌─toYYYYMMDD(20230911)─┐
│           2023-09-11 │
└──────────────────────┘

Introduced in version 23.9.

YYYYMMDDToDate32

Converts a number containing the year, month and day number to a Date32. This function is the opposite of function toYYYYMMDD(). The output is undefined if the input does not encode a valid Date32 value.

Syntax

YYYYMMDDToDate32(YYYYMMDD)

Arguments

Returned value

Returns a Date32 value from the provided arguments Date32

Examples

Example

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

Introduced in version 23.9.

YYYYMMDDhhmmssToDateTime

Converts a number containing the year, month, day, hour, minute, and second to a DateTime. This function is the opposite of function toYYYYMMDDhhmmss(). The output is undefined if the input does not encode a valid DateTime value.

Syntax

YYYYMMDDhhmmssToDateTime(YYYYMMDDhhmmss[, timezone])

Arguments

  • YYYYMMDDhhmmss — Number containing the year, month, day, hour, minute, and second. (U)Int* or Float* or Decimal
  • timezone — Timezone name. String

Returned value

Returns a DateTime value from the provided arguments DateTime

Examples

Example

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

Introduced in version 23.9.

YYYYMMDDhhmmssToDateTime64

Converts a number containing the year, month, day, hour, minute, and second to a DateTime64. This function is the opposite of function toYYYYMMDDhhmmss(). The output is undefined if the input does not encode a valid DateTime64 value.

Syntax

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

Arguments

  • YYYYMMDDhhmmss — Number containing the year, month, day, hour, minute, and second. (U)Int* or Float* or Decimal
  • precision — Precision for the fractional part (0-9). UInt8
  • timezone — Timezone name. String

Returned value

Returns a DateTime64 value from the provided arguments DateTime64

Examples

Example

SELECT YYYYMMDDhhmmssToDateTime64(20230911131415, 3, 'Asia/Istanbul');
┌─YYYYMMDDhhmm⋯/Istanbul')─┐
│  2023-09-11 13:14:15.000 │
└──────────────────────────┘

Introduced in version 23.9.

addDate

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

Syntax

addDate(datetime, interval)

Arguments

Returned value

Returns date or date with time obtained by adding interval to datetime. Date or Date32 or DateTime or DateTime64

Examples

Add interval to date

SELECT addDate(toDate('2018-01-01'), INTERVAL 3 YEAR)
┌─addDate(toDa⋯valYear(3))─┐
│               2021-01-01 │
└──────────────────────────┘

Introduced in version 23.9.

addDays

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

Syntax

addDays(datetime, num)

Arguments

Returned value

Returns datetime plus num days. Date or Date32 or DateTime or DateTime64

Examples

Add days to different date types

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
┌─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

SELECT dateAdd('1998-06-16'::Date, INTERVAL 10 day)
┌─plus(CAST('1⋯valDay(10))─┐
│               1998-06-26 │
└──────────────────────────┘

Introduced in version 1.1.

addHours

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

Syntax

addHours(datetime, num)

Arguments

Returned value

Returns datetime plus num hours DateTime or DateTime64(3)

Examples

Add hours to different date types

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
┌─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

SELECT dateAdd('1998-06-16'::Date, INTERVAL 10 hour)
┌─plus(CAST('1⋯alHour(10))─┐
│      1998-06-16 10:00:00 │
└──────────────────────────┘

Introduced in version 1.1.

addInterval

Adds an 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(1) and toIntervalDay(2) are passed then the result will be (3) rather than (1,1). :::

Syntax

addInterval(interval_1, interval_2)

Arguments

Returned value

Returns a tuple of intervals Tuple(Interval)

Examples

Add intervals

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)
┌─addInterval(toIntervalDay(1), toIntervalMonth(1))─┐
│ (1,1)                                             │
└───────────────────────────────────────────────────┘
┌─addInterval((toIntervalDay(1), toIntervalYear(1)), toIntervalMonth(1))─┐
│ (1,1,1)                                                                │
└────────────────────────────────────────────────────────────────────────┘
┌─addInterval(toIntervalDay(2), toIntervalDay(1))─┐
│ (3)                                             │
└─────────────────────────────────────────────────┘

Introduced in version 22.11.

addMicroseconds

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

Syntax

addMicroseconds(datetime, num)

Arguments

Returned value

Returns date_time plus num microseconds DateTime64

Examples

Add microseconds to different date time types

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
┌─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

SELECT dateAdd('1998-06-16'::DateTime, INTERVAL 10 microsecond)
┌─plus(CAST('19⋯osecond(10))─┐
│ 1998-06-16 00:00:00.000010 │
└────────────────────────────┘

Introduced in version 22.6.

addMilliseconds

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

Syntax

addMilliseconds(datetime, num)

Arguments

Returned value

Returns datetime plus num milliseconds DateTime64

Examples

Add milliseconds to different date time types

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
┌─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

SELECT dateAdd('1998-06-16'::DateTime, INTERVAL 10 millisecond)
┌─plus(CAST('1⋯second(10))─┐
│  1998-06-16 00:00:00.010 │
└──────────────────────────┘

Introduced in version 22.6.

addMinutes

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

Syntax

addMinutes(datetime, num)

Arguments

Returned value

Returns datetime plus num minutes DateTime or DateTime64(3)

Examples

Add minutes to different date types

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
┌─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

SELECT dateAdd('1998-06-16'::Date, INTERVAL 10 minute)
┌─plus(CAST('1⋯Minute(10))─┐
│      1998-06-16 00:10:00 │
└──────────────────────────┘

Introduced in version 1.1.

addMonths

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

Syntax

addMonths(datetime, num)

Arguments

Returned value

Returns datetime plus num months Date or Date32 or DateTime or DateTime64

Examples

Add months to different date types

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
┌─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

SELECT dateAdd('1998-06-16'::Date, INTERVAL 10 month)
┌─plus(CAST('1⋯lMonth(10))─┐
│               1999-04-16 │
└──────────────────────────┘

Introduced in version 1.1.

addNanoseconds

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

Syntax

addNanoseconds(datetime, num)

Arguments

Returned value

Returns datetime plus num nanoseconds DateTime64

Examples

Add nanoseconds to different date time types

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
┌─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

SELECT dateAdd('1998-06-16'::DateTime, INTERVAL 1000 nanosecond)
┌─plus(CAST('199⋯osecond(1000))─┐
│ 1998-06-16 00:00:00.000001000 │
└───────────────────────────────┘

Introduced in version 22.6.

addQuarters

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

Syntax

addQuarters(datetime, num)

Arguments

Returned value

Returns datetime plus num quarters Date or Date32 or DateTime or DateTime64

Examples

Add quarters to different date types

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
┌─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

SELECT dateAdd('1998-06-16'::Date, INTERVAL 10 quarter)
┌─plus(CAST('1⋯uarter(10))─┐
│               2000-12-16 │
└──────────────────────────┘

Introduced in version 20.1.

addSeconds

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

Syntax

addSeconds(datetime, num)

Arguments

Returned value

Returns datetime plus num seconds DateTime or DateTime64(3)

Examples

Add seconds to different date types

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
┌─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

SELECT dateAdd('1998-06-16'::Date, INTERVAL 10 second)
┌─dateAdd('1998-06-16'::Date, INTERVAL 10 second)─┐
│                             1998-06-16 00:00:10 │
└─────────────────────────────────────────────────┘

Introduced in version 1.1.

addTupleOfIntervals

Consecutively adds a tuple of intervals to a date or a date with time.

Syntax

addTupleOfIntervals(datetime, intervals)

Arguments

Returned value

Returns date with added intervals Date or Date32 or DateTime or DateTime64

Examples

Add tuple of intervals to date

WITH toDate('2018-01-01') AS date
SELECT addTupleOfIntervals(date, (INTERVAL 1 DAY, INTERVAL 1 MONTH, INTERVAL 1 YEAR))
┌─addTupleOfIntervals(date, (toIntervalDay(1), toIntervalMonth(1), toIntervalYear(1)))─┐
│                                                                           2019-02-02 │
└──────────────────────────────────────────────────────────────────────────────────────┘

Introduced in version 22.11.

addWeeks

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

Syntax

addWeeks(datetime, num)

Arguments

Returned value

Returns datetime plus num weeks Date or Date32 or DateTime or DateTime64

Examples

Add weeks to different date types

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
┌─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

SELECT dateAdd('1998-06-16'::Date, INTERVAL 10 week)
┌─plus(CAST('1⋯alWeek(10))─┐
│               1998-08-25 │
└──────────────────────────┘

Introduced in version 1.1.

addYears

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

Syntax

addYears(datetime, num)

Arguments

Returned value

Returns datetime plus num years Date or Date32 or DateTime or DateTime64

Examples

Add years to different date types

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
┌─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

SELECT dateAdd('1998-06-16'::Date, INTERVAL 10 year)
┌─plus(CAST('1⋯alYear(10))─┐
│               2008-06-16 │
└──────────────────────────┘

Introduced in version 1.1.

age

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.

Syntax

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

Arguments

  • unit — The type of interval for result.
UnitPossible values
nanosecondnanosecond, nanoseconds, ns
microsecondmicrosecond, microseconds, us, u
millisecondmillisecond, milliseconds, ms
secondsecond, seconds, ss, s
minuteminute, minutes, mi, n
hourhour, hours, hh, h
dayday, days, dd, d
weekweek, weeks, wk, ww
monthmonth, months, mm, m
quarterquarter, quarters, qq, q
yearyear, years, yyyy, yy
  • startdate — The first time value to subtract (the subtrahend). Date or Date32 or DateTime or DateTime64
  • enddate — The second time value to subtract from (the minuend). Date or Date32 or DateTime or 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

Returned value

Returns the difference between enddate and startdate expressed in unit. Int32

Examples

Calculate age in hours

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

Calculate age in different units

SELECT
    toDate('2022-01-01') AS e,
    toDate('2021-12-29') AS s,
    age('day', s, e) AS day_age,
    age('month', s, e) AS month_age,
    age('year', s, e) AS year_age
┌──────────e─┬──────────s─┬─day_age─┬─month_age─┬─year_age─┐
│ 2022-01-01 │ 2021-12-29 │       3 │         0 │        0 │
└────────────┴────────────┴─────────┴───────────┴──────────┘

Introduced in version 23.1.

changeDay

Changes the day component of a date or date time.

Syntax

changeDay(date_or_datetime, value)

Arguments

Returned value

Returns a value of the same type as date_or_datetime with modified day component. Date or Date32 or DateTime or DateTime64

Examples

Usage example

SELECT changeDay('2024-01-31'::DateTime, 15)
2024-01-15 00:00:00

Introduced in version 24.7.

changeHour

Changes the hour component of a date or date time.

Syntax

changeHour(date_or_datetime, value)

Arguments

Returned value

Returns a value of the same type as date_or_datetime with modified hour component. DateTime or DateTime64

Examples

Usage example

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

Introduced in version 24.7.

changeMinute

Changes the minute component of a date or date time.

Syntax

changeMinute(date_or_datetime, value)

Arguments

Returned value

Returns a value of the same type as date_or_datetime with modified minute component. DateTime or DateTime64

Examples

Usage example

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

Introduced in version 24.7.

changeMonth

Changes the month component of a date or date time.

Syntax

changeMonth(date_or_datetime, value)

Arguments

Returned value

Returns a value of the same type as date_or_datetime with modified month component. Date or Date32 or DateTime or DateTime64

Examples

Usage example

SELECT changeMonth('2024-01-01'::DateTime, 12)
2024-12-01 00:00:00

Introduced in version 24.7.

changeSecond

Changes the second component of a date or date time.

Syntax

changeSecond(date_or_datetime, value)

Arguments

Returned value

Returns a value of the same type as date_or_datetime with modified seconds component. DateTime or DateTime64

Examples

Usage example

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

Introduced in version 24.7.

changeYear

Changes the year component of a date or date time.

Syntax

changeYear(date_or_datetime, value)

Arguments

Returned value

Returns a value of the same type as date_or_datetime with modified year component. Date or Date32 or DateTime or DateTime64

Examples

Usage example

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

Introduced in version 24.7.

dateDiff

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), 1 month for unit month (see toRelativeMonthNum) and 1 year for unit year (see 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.

Syntax

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

Arguments

  • unit — The type of interval for result.
UnitPossible values
nanosecondnanosecond, nanoseconds, ns
microsecondmicrosecond, microseconds, us, u
millisecondmillisecond, milliseconds, ms
secondsecond, seconds, ss, s
minuteminute, minutes, mi, n
hourhour, hours, hh, h
dayday, days, dd, d
weekweek, weeks, wk, ww
monthmonth, months, mm, m
quarterquarter, quarters, qq, q
yearyear, years, yyyy, yy
  • startdate — The first time value to subtract (the subtrahend). Date or Date32 or DateTime or DateTime64
  • enddate — The second time value to subtract from (the minuend). Date or Date32 or DateTime or 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

Returned value

Returns the difference between enddate and startdate expressed in unit. Int64

Examples

Calculate date difference in hours

SELECT dateDiff('hour', toDateTime('2018-01-01 22:00:00'), toDateTime('2018-01-02 23:00:00')) AS res
┌─res─┐
│  25 │
└─────┘

Calculate date difference in different units

SELECT
    toDate('2022-01-01') AS e,
    toDate('2021-12-29') AS s,
    dateDiff('day', s, e) AS day_diff,
    dateDiff('month', s, e) AS month_diff,
    dateDiff('year', s, e) AS year_diff
┌──────────e─┬──────────s─┬─day_diff─┬─month_diff─┬─year_diff─┐
│ 2022-01-01 │ 2021-12-29 │        3 │          1 │         1 │
└────────────┴────────────┴──────────┴────────────┴───────────┘

Introduced in version 23.4.

dateName

Returns the specified part of the date.

Possible values:

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

Syntax

dateName(date_part, date[, timezone])

Arguments

Returned value

Returns the specified part of date. String

Examples

Extract different date parts

WITH toDateTime('2021-04-14 11:22:33') AS date_value
SELECT
    dateName('year', date_value),
    dateName('month', date_value),
    dateName('day', date_value)
┌─dateName('year', date_value)─┬─dateName('month', date_value)─┬─dateName('day', date_value)─┐
│ 2021                         │ April                         │ 14                          │
└──────────────────────────────┴───────────────────────────────┴─────────────────────────────┘

Introduced in version 21.7.

dateTrunc

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

Syntax

dateTrunc(unit, datetime[, timezone])

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
  • datetime — Date and time. Date or Date32 or DateTime or DateTime64
  • timezone — Optional. Timezone name for the returned datetime. If not specified, the function uses the timezone of the datetime parameter. String

Returned value

Returns the truncated date and time value.

Unit Argumentdatetime ArgumentReturn Type
Year, Quarter, Month, WeekDate32 or DateTime64 or Date or DateTimeDate32 or Date
Day, Hour, Minute, SecondDate32, DateTime64, Date, or DateTimeDateTime64 or DateTime
Millisecond, Microsecond,AnyDateTime64
Nanosecondwith scale 3, 6, or 9

Examples

Truncate without timezone

SELECT now(), dateTrunc('hour', now());
┌───────────────now()─┬─dateTrunc('hour', now())──┐
│ 2020-09-28 10:40:45 │       2020-09-28 10:00:00 │
└─────────────────────┴───────────────────────────┘

Truncate with specified timezone

SELECT now(), dateTrunc('hour', now(), 'Asia/Istanbul');
┌───────────────now()─┬─dateTrunc('hour', now(), 'Asia/Istanbul')──┐
│ 2020-09-28 10:46:26 │                        2020-09-28 13:00:00 │
└─────────────────────┴────────────────────────────────────────────┘

Introduced in version 20.8.

formatDateTime

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.

The opposite operation of this function is 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:

PlaceholderDescriptionExample
%aabbreviated weekday name (Mon-Sun)Mon
%babbreviated month name (Jan-Dec)Jan
%cmonth as an integer number (01-12)01
%Cyear divided by 100 and truncated to integer (00-99)20
%dday of the month, zero-padded (01-31)02
%DShort MM/DD/YY date, equivalent to %m/%d/%y01/02/18
%eday of the month, space-padded (1-31)2
%ffractional second123456
%Fshort YYYY-MM-DD date, equivalent to %Y-%m-%d2018-01-02
%gtwo-digit year format, aligned to ISO 860118
%Gfour-digit year format for ISO week number2018
%hhour in 12h format (01-12)09
%Hhour in 24h format (00-23)22
%iminute (00-59)33
%Ihour in 12h format (01-12)10
%jday of the year (001-366)002
%khour in 24h format (00-23)14
%lhour in 12h format (01-12)09
%mmonth as an integer number (01-12)01
%Mfull month name (January-December)January
%nnew-line character
%pAM or PM designationPM
%QQuarter (1-4)1
%r12-hour HH:MM AM/PM time, equivalent to %h:%i %p10:30 PM
%R24-hour HH:MM time, equivalent to %H:%i22:33
%ssecond (00-59)44
%Ssecond (00-59)44
%thorizontal-tab character
%TISO 8601 time format (HH:MM:SS), equivalent to %H:%i:%S22:33:44
%uISO 8601 weekday as number with Monday as 1 (1-7)2
%VISO 8601 week number (01-53)01
%wweekday as a integer number with Sunday as 0 (0-6)2
%Wfull weekday name (Monday-Sunday)Monday
%yYear, last two digits (00-99)18
%YYear2018
%zTime offset from UTC as +HHMM or -HHMM-0500
%%a % sign%
  • In RawTree 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 RawTree versions earlier than v25.1, %f prints as many digits as specified by the scale of the DateTime64 instead of fixed 6 digits.
  • In RawTree versions earlier than v23.4, %M prints the minute (00-59) instead of the full month name (January-December).

Syntax

formatDateTime(datetime, format[, timezone])

Arguments

  • datetime — A date or date time to format. Date or Date32 or DateTime or DateTime64
  • format — Format string with replacement fields. String
  • timezone — Optional. Timezone name for the formatted time. String

Returned value

Returns time and date values according to the determined format. String

Examples

Format date with year placeholder

SELECT formatDateTime(toDate('2010-01-04'), '%g')
┌─formatDateTime(toDate('2010-01-04'), '%g')─┐
│ 10                                         │
└────────────────────────────────────────────┘

Format DateTime64 with fractional seconds

SELECT formatDateTime(toDateTime64('2010-01-04 12:34:56.123456', 7), '%f')
┌─formatDateTime(toDateTime64('2010-01-04 12:34:56.123456', 7), '%f')─┐
│ 1234560                                                             │
└─────────────────────────────────────────────────────────────────────┘

Format with timezone

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
┌──────────────────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 version 1.1.

formatDateTimeInJodaSyntax

Similar to formatDateTime, except that it formats datetime in Joda style instead of MySQL style. Refer to Joda Time documentation.

The opposite operation of this function is parseDateTimeInJodaSyntax.

Using replacement fields, you can define a pattern for the resulting string.

Replacement fields:

PlaceholderDescriptionPresentationExamples
GeratextAD
Ccentury of era (>=0)number20
Yyear of era (>=0)year1996
xweekyear (not supported yet)year1996
wweek of weekyear (not supported yet)number27
eday of weeknumber2
Eday of weektextTuesday; Tue
yyearyear1996
Dday of yearnumber189
Mmonth of yearmonthJuly; Jul; 07
dday of monthnumber10
ahalfday of daytextPM
Khour of halfday (0~11)number0
hclockhour of halfday (1~12)number12
Hhour of day (0~23)number0
kclockhour of day (1~24)number24
mminute of hournumber30
ssecond of minutenumber55
Sfraction of secondnumber978
ztime zonetextEastern Standard Time; EST
Ztime zone offsetzone-0800; -0812
'escape for textdelimiter
''single quoteliteral'

Syntax

formatDateTimeInJodaSyntax(datetime, format[, timezone])

Arguments

  • datetime — A date or date time to format. DateTime or Date or Date32 or DateTime64
  • format — Format string with Joda-style replacement fields. String
  • timezone — Optional. Timezone name for the formatted time. String

Returned value

Returns time and date values according to the determined format. String

Examples

Format datetime using Joda syntax

SELECT formatDateTimeInJodaSyntax(toDateTime('2010-01-04 12:34:56'), 'yyyy-MM-dd HH:mm:ss')
┌─formatDateTimeInJodaSyntax(toDateTime('2010-01-04 12:34:56'), 'yyyy-MM-dd HH:mm:ss')─┐
│ 2010-01-04 12:34:56                                                                     │
└─────────────────────────────────────────────────────────────────────────────────────────┘

Introduced in version 20.1.

fromDaysSinceYearZero

For a given number of days elapsed since 1 January 0000, returns the corresponding date in the proleptic Gregorian calendar defined by ISO 8601.

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 type.

Syntax

fromDaysSinceYearZero(days)

Arguments

  • days — The number of days passed since year zero. UInt32

Returned value

Returns the date corresponding to the number of days passed since year zero. Date

Examples

Convert days since year zero to dates

SELECT
fromDaysSinceYearZero(739136) AS date1,
fromDaysSinceYearZero(toDaysSinceYearZero(toDate('2023-09-08'))) AS date2
┌──────date1─┬──────date2─┐
│ 2023-09-08 │ 2023-09-08 │
└────────────┴────────────┘

Introduced in version 23.11.

fromDaysSinceYearZero32

For a given number of days elapsed since 1 January 0000, returns the corresponding date in the proleptic Gregorian calendar defined by ISO 8601. 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 type.

Syntax

fromDaysSinceYearZero32(days)

Arguments

  • days — The number of days passed since year zero. UInt32

Returned value

Returns the date corresponding to the number of days passed since year zero. Date32

Examples

Convert days since year zero to dates

SELECT
fromDaysSinceYearZero32(739136) AS date1,
fromDaysSinceYearZero32(toDaysSinceYearZero(toDate('2023-09-08'))) AS date2
┌──────date1─┬──────date2─┐
│ 2023-09-08 │ 2023-09-08 │
└────────────┴────────────┘

Introduced in version 23.11.

fromModifiedJulianDay

Converts a Modified Julian Day number to a 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

fromModifiedJulianDay(day)

Arguments

  • day — Modified Julian Day number. (U)Int*

Returned value

Returns date in text form. String

Examples

Convert Modified Julian Day to date

SELECT fromModifiedJulianDay(58849)
┌─fromModifiedJulianDay(58849)─┐
│ 2020-01-01                   │
└──────────────────────────────┘

Introduced in version 21.1.

fromModifiedJulianDayOrNull

Similar to fromModifiedJulianDay(), but instead of raising exceptions it returns NULL.

Syntax

fromModifiedJulianDayOrNull(day)

Arguments

  • day — Modified Julian Day number. (U)Int*

Returned value

Returns date in text form for valid day argument, otherwise null. Nullable(String)

Examples

Convert Modified Julian Day to date with null handling

SELECT fromModifiedJulianDayOrNull(58849);
SELECT fromModifiedJulianDayOrNull(60000000); -- invalid argument, returns NULL
┌─fromModified⋯Null(58849)─┐
│ 2020-01-01               │
└──────────────────────────┘
┌─fromModified⋯l(60000000)─┐
│ ᴺᵁᴸᴸ                     │
└──────────────────────────┘

Introduced in version 21.1.

fromUTCTimestamp

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

fromUTCTimestamp(datetime, time_zone)

Arguments

  • datetime — A date or date with time const value or an expression. DateTime or DateTime64
  • time_zone — A String type const value or an expression representing the time zone. String

Returned value

Returns DateTime/DateTime64 in the specified timezone. DateTime or DateTime64

Examples

Convert UTC timezone to specified timezone

SELECT fromUTCTimestamp(toDateTime64('2023-03-16 10:00:00', 3), 'Asia/Shanghai')
┌─fromUTCTimestamp(toDateTime64('2023-03-16 10:00:00',3), 'Asia/Shanghai')─┐
│                                                 2023-03-16 18:00:00.000 │
└─────────────────────────────────────────────────────────────────────────┘

Introduced in version 22.1.

fromUnixTimestamp

This function converts a Unix timestamp to a calendar date and a time of a day.

It can be called in two ways:

Syntax

fromUnixTimestamp(timestamp)
fromUnixTimestamp(timestamp[, format[, timezone]])

Arguments

  • timestamp — Unix timestamp or date/date with time value. (U)Int* or Date or Date32 or DateTime or DateTime64
  • format — Optional. Constant format string for output formatting. String
  • timezone — Optional. Constant time zone string. String

Returned value

Returns DateTime of the timestamp when called with one argument, or a String when called with two or three arguments. DateTime or String

Examples

Convert Unix timestamp to DateTime

SELECT fromUnixTimestamp(423543535)
┌─fromUnixTimestamp(423543535)─┐
│          1983-06-04 10:58:55 │
└──────────────────────────────┘

Convert Unix timestamp with format

SELECT fromUnixTimestamp(1234334543, '%Y-%m-%d %R:%S') AS DateTime
┌─DateTime────────────┐
│ 2009-02-11 14:42:23 │
└─────────────────────┘

Introduced in version 20.8.

fromUnixTimestampInJodaSyntax

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, it returns a value of type DateTime, i.e. behaves like toDateTime.

When given two or three arguments where the first argument is a value of type Integer, Date, Date32, DateTime or 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, i.e. it behaves like formatDateTimeInJodaSyntax. In this case, Joda datetime format style is used.

Syntax

fromUnixTimestampInJodaSyntax(timestamp)
fromUnixTimestampInJodaSyntax(timestamp, format[, timezone])

Arguments

  • timestamp — Unix timestamp or date/time value. (U)Int* or Date or Date32 or DateTime or DateTime64
  • format — Optional. Constant format string using Joda syntax for output formatting. String
  • timezone — Optional. Constant time zone string. String

Returned value

Returns a date with time when called with one argument, or a String when called with two or three arguments.} DateTime or String

Examples

Convert Unix timestamp with Joda format

SELECT fromUnixTimestampInJodaSyntax(1234334543, 'yyyy-MM-dd HH:mm:ss', 'UTC') AS DateTime
┌─DateTime────────────┐
│ 2009-02-11 06:42:23 │
└─────────────────────┘

Introduced in version 23.1.

makeDate

Creates a Date from either:

  • a year, month and day
  • a year and day of year

Syntax

makeDate(year, month, day)
makeDate(year, day_of_year)

Arguments

Returned value

Returns a Date value constructed from the provided arguments Date

Examples

Date from a year, month, day

SELECT makeDate(2023, 2, 28) AS date;
┌───────date─┐
│ 2023-02-28 │
└────────────┘

Date from year and day of year

SELECT makeDate(2023, 42) AS date;
┌───────date─┐
│ 2023-02-11 │
└────────────┘

Introduced in version 22.6.

makeDate32

Creates a Date32 from either:

  • a year, month and day
  • a year and day of year

Syntax

makeDate32(year, month, day)
makeDate32(year, day_of_year)

Arguments

Returned value

Returns a Date32 value constructed from the provided arguments Date32

Examples

Date32 from a year, month, day

SELECT makeDate(2023, 2, 28) AS date;
┌───────date─┐
│ 2023-02-28 │
└────────────┘

Date32 from year and day of year

SELECT makeDate(2023, 42) AS date;
┌───────date─┐
│ 2023-02-11 │
└────────────┘

Introduced in version 22.6.

makeDateTime

Creates a DateTime from year, month, day, hour, minute, and second, with optional timezone.

Syntax

makeDateTime(year, month, day, hour, minute, second[, timezone])

Arguments

Returned value

Returns a DateTime value constructed from the provided arguments DateTime

Examples

DateTime from year, month, day, hour, minute, second

SELECT makeDateTime(2023, 2, 28, 17, 12, 33) AS DateTime;
┌────────────DateTime─┐
│ 2023-02-28 17:12:33 │
└─────────────────────┘

Introduced in version 22.6.

makeDateTime64

Creates a DateTime64 from year, month, day, hour, minute, second, with optional fraction, precision, and timezone.

Syntax

makeDateTime64(year, month, day, hour, minute, second[, fraction[, precision[, timezone]]])

Arguments

Returned value

Returns a DateTime64 value constructed from the provided arguments DateTime64

Examples

DateTime64 from year, month, day, hour, minute, second

SELECT makeDateTime64(2023, 5, 15, 10, 30, 45, 779, 5);
┌─makeDateTime64(2023, 5, 15, 10, 30, 45, 779, 5)─┐
│                       2023-05-15 10:30:45.00779 │
└─────────────────────────────────────────────────┘

Introduced in version 22.6.

monthName

Returns the name of the month as a string from a date or date with time value.

Syntax

monthName(datetime)

Arguments

Returned value

Returns the name of the month. String

Examples

Get month name from date

WITH toDateTime('2021-04-14 11:22:33') AS date_value
SELECT monthName(date_value)
┌─monthName(date_value)─┐
│ April                 │
└───────────────────────┘

Introduced in version 22.1.

now

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

Syntax

now([timezone])

Arguments

  • timezone — Optional. Timezone name for the returned value. String

Returned value

Returns the current date and time. DateTime

Examples

Query without timezone

SELECT now()
┌───────────────now()─┐
│ 2020-10-17 07:42:09 │
└─────────────────────┘

Query with specified timezone

SELECT now('Asia/Istanbul')
┌─now('Asia/Istanbul')─┐
│  2020-10-17 10:42:23 │
└──────────────────────┘

Introduced in version 1.1.

now64

Returns the current date and time with sub-second precision at the moment of query analysis. The function is a constant expression.

Syntax

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
  • timezone — Optional. Timezone name for the returned value. String

Returned value

Returns current date and time with sub-second precision. DateTime64

Examples

Query with default and custom precision

SELECT now64(), now64(9, 'Asia/Istanbul')
┌─────────────────now64()─┬─────now64(9, 'Asia/Istanbul')─┐
│ 2022-08-21 19:34:26.196 │ 2022-08-21 22:34:26.196542766 │
└─────────────────────────┴───────────────────────────────┘

Introduced in version 20.1.

nowInBlock

Returns the current date and time at the moment of processing of each block of data. In contrast to the function 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

nowInBlock([timezone])

Arguments

  • timezone — Optional. Timezone name for the returned value. String

Returned value

Returns the current date and time at the moment of processing of each block of data. DateTime

Examples

Difference with the now() function

SELECT
    now(),
    nowInBlock(),
    sleep(1)
FROM numbers(3)
SETTINGS max_block_size = 1
FORMAT PrettyCompactMonoBlock
┌───────────────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 │
└─────────────────────┴─────────────────────┴──────────┘

Introduced in version 22.8.

nowInBlock64

Returns the current date and time at the moment of processing of each block of data in milliseconds. In contrast to the function 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

nowInBlock([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
  • timezone — Optional. Timezone name for the returned value. String

Returned value

Returns the current date and time at the moment of processing of each block of data with sub-second precision. DateTime64

Examples

Difference with the now64() function

SELECT
    now64(),
    nowInBlock64(),
    sleep(1)
FROM numbers(3)
SETTINGS max_block_size = 1
FORMAT PrettyCompactMonoBlock
┌─────────────────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 │
└─────────────────────────┴─────────────────────────┴──────────┘

Introduced in version 25.8.

serverTimezone

Returns the timezone of the server, i.e. the value of the 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

serverTimeZone()

Returned value

Returns the server timezone as a String

Examples

Usage example

SELECT serverTimeZone()
┌─serverTimeZone()─┐
│ UTC              │
└──────────────────┘

Introduced in version 23.6.

subDate

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

subDate(datetime, interval)

Arguments

Returned value

Returns date or date with time obtained by subtracting interval from datetime. Date or Date32 or DateTime or DateTime64

Examples

Subtract interval from date

SELECT subDate(toDate('2018-01-01'), INTERVAL 3 YEAR)
┌─subDate(toDate('2018-01-01'), toIntervalYear(3))─┐
│                                       2015-01-01 │
└──────────────────────────────────────────────────┘

Introduced in version 23.9.

subtractDays

Subtracts a specified number of days from a date, a date with time or a string-encoded date or date with time.

Syntax

subtractDays(datetime, num)

Arguments

Returned value

Returns datetime minus num days Date or Date32 or DateTime or DateTime64

Examples

Subtract days from different date types

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
┌─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

SELECT dateSub('1998-06-16'::Date, INTERVAL 10 day)
┌─minus(CAST('⋯valDay(10))─┐
│               1998-06-06 │
└──────────────────────────┘

Introduced in version 1.1.

subtractHours

Subtracts a specified number of hours from a date, a date with time or a string-encoded date or date with time.

Syntax

subtractHours(datetime, num)

Arguments

Returned value

Returns datetime minus num hours DateTime or DateTime64(3)

Examples

Subtract hours from different date types

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
┌─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

SELECT dateSub('1998-06-16'::Date, INTERVAL 10 hour)
┌─minus(CAST('⋯alHour(10))─┐
│      1998-06-15 14:00:00 │
└──────────────────────────┘

Introduced in version 1.1.

subtractInterval

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

subtractInterval(interval_1, interval_2)

Arguments

Returned value

Returns a tuple of intervals Tuple(T)

Examples

Subtract intervals

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);
┌─subtractInterval(toIntervalDay(1), toIntervalMonth(1))─┐
│ (1,-1)                                                 │
└────────────────────────────────────────────────────────┘
┌─subtractInterval((toIntervalDay(1), toIntervalYear(1)), toIntervalMonth(1))─┐
│ (1,1,-1)                                                                    │
└─────────────────────────────────────────────────────────────────────────────┘
┌─subtractInterval(toIntervalDay(2), toIntervalDay(1))─┐
│ (1)                                                  │
└──────────────────────────────────────────────────────┘

Introduced in version 22.11.

subtractMicroseconds

Subtracts a specified number of microseconds from a date with time or a string-encoded date with time.

Syntax

subtractMicroseconds(datetime, num)

Arguments

Returned value

Returns datetime minus num microseconds DateTime64

Examples

Subtract microseconds from different date time types

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
┌─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

SELECT dateSub('1998-06-16'::DateTime, INTERVAL 10 microsecond)
┌─minus(CAST('1⋯osecond(10))─┐
│ 1998-06-15 23:59:59.999990 │
└────────────────────────────┘

Introduced in version 22.6.

subtractMilliseconds

Subtracts a specified number of milliseconds from a date with time or a string-encoded date with time.

Syntax

subtractMilliseconds(datetime, num)

Arguments

Returned value

Returns datetime minus num milliseconds DateTime64

Examples

Subtract milliseconds from different date time types

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
┌─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

SELECT dateSub('1998-06-16'::DateTime, INTERVAL 10 millisecond)
┌─minus(CAST('⋯second(10))─┐
│  1998-06-15 23:59:59.990 │
└──────────────────────────┘

Introduced in version 22.6.

subtractMinutes

Subtracts a specified number of minutes from a date, a date with time or a string-encoded date or date with time.

Syntax

subtractMinutes(datetime, num)

Arguments

Returned value

Returns datetime minus num minutes DateTime or DateTime64(3)

Examples

Subtract minutes from different date types

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
┌─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

SELECT dateSub('1998-06-16'::Date, INTERVAL 10 minute)
┌─minus(CAST('⋯Minute(10))─┐
│      1998-06-15 23:50:00 │
└──────────────────────────┘

Introduced in version 1.1.

subtractMonths

Subtracts a specified number of months from a date, a date with time or a string-encoded date or date with time.

Syntax

subtractMonths(datetime, num)

Arguments

Returned value

Returns datetime minus num months Date or Date32 or DateTime or DateTime64

Examples

Subtract months from different date types

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
┌─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

SELECT dateSub('1998-06-16'::Date, INTERVAL 10 month)
┌─minus(CAST('⋯lMonth(10))─┐
│               1997-08-16 │
└──────────────────────────┘

Introduced in version 1.1.

subtractNanoseconds

Subtracts a specified number of nanoseconds from a date with time or a string-encoded date with time.

Syntax

subtractNanoseconds(datetime, num)

Arguments

Returned value

Returns datetime minus num nanoseconds DateTime64

Examples

Subtract nanoseconds from different date time types

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
┌─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

SELECT dateSub('1998-06-16'::DateTime, INTERVAL 10 nanosecond)
┌─minus(CAST('19⋯anosecond(10))─┐
│ 1998-06-15 23:59:59.999999990 │
└───────────────────────────────┘

Introduced in version 20.1.

subtractQuarters

Subtracts a specified number of quarters from a date, a date with time or a string-encoded date or date with time.

Syntax

subtractQuarters(datetime, num)

Arguments

Returned value

Returns datetime minus num quarters Date or Date32 or DateTime or DateTime64

Examples

Subtract quarters from different date types

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
┌─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

SELECT dateSub('1998-06-16'::Date, INTERVAL 10 quarter)
┌─minus(CAST('1⋯Quarter(10))─┐
│                1996-09-16 │
└───────────────────────────┘

Introduced in version 20.1.

subtractSeconds

Subtracts a specified number of seconds from a date, a date with time or a string-encoded date or date with time.

Syntax

subtractSeconds(datetime, num)

Arguments

Returned value

Returns datetime minus num seconds DateTime or DateTime64(3)

Examples

Subtract seconds from different date types

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
┌─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

SELECT dateSub('1998-06-16'::Date, INTERVAL 10 second)
┌─minus(CAST('⋯Second(10))─┐
│      1998-06-15 23:59:50 │
└──────────────────────────┘

Introduced in version 1.1.

subtractTupleOfIntervals

Consecutively subtracts a tuple of intervals from a date or a date with time.

Syntax

subtractTupleOfIntervals(datetime, intervals)

Arguments

Returned value

Returns date with subtracted intervals Date or Date32 or DateTime or DateTime64

Examples

Subtract tuple of intervals from date

WITH toDate('2018-01-01') AS date SELECT subtractTupleOfIntervals(date, (INTERVAL 1 DAY, INTERVAL 1 YEAR))
┌─subtractTupl⋯alYear(1)))─┐
│               2016-12-31 │
└──────────────────────────┘

Introduced in version 22.11.

subtractWeeks

Subtracts a specified number of weeks from a date, a date with time or a string-encoded date or date with time.

Syntax

subtractWeeks(datetime, num)

Arguments

Returned value

Returns datetime minus num weeks Date or Date32 or DateTime or DateTime64

Examples

Subtract weeks from different date types

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
┌─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

SELECT dateSub('1998-06-16'::Date, INTERVAL 10 week)
┌─minus(CAST('⋯alWeek(10))─┐
│               1998-04-07 │
└──────────────────────────┘

Introduced in version 1.1.

subtractYears

Subtracts a specified number of years from a date, a date with time or a string-encoded date or date with time.

Syntax

subtractYears(datetime, num)

Arguments

Returned value

Returns datetime minus num years Date or Date32 or DateTime or DateTime64

Examples

Subtract years from different date types

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
┌─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

SELECT dateSub('1998-06-16'::Date, INTERVAL 10 year)
┌─minus(CAST('⋯alYear(10))─┐
│               1988-06-16 │
└──────────────────────────┘

Introduced in version 1.1.

timeDiff

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 function instead.

Syntax

timeDiff(startdate, enddate)

Arguments

Returned value

Returns the difference between enddate and startdate expressed in seconds. Int64

Examples

Calculate time difference in seconds

SELECT timeDiff(toDateTime('2018-01-01 22:00:00'), toDateTime('2018-01-02 23:00:00')) AS res
┌───res─┐
│ 90000 │
└───────┘

Calculate time difference and convert to hours

SELECT timeDiff(toDateTime('2018-01-01 22:00:00'), toDateTime('2018-01-02 23:00:00')) / 3600 AS hours
┌─hours─┐
│    25 │
└───────┘

Equivalent to dateDiff with seconds

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
┌─time_diff_result─┬─date_diff_result─┐
│           259200 │           259200 │
└──────────────────┴──────────────────┘

Introduced in version 23.4.

timeSlot

Round the time to the start of a half-an-hour length interval.

:::note 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

timeSlot(time[, time_zone])

Arguments

  • time — Time to round to the start of a half-an-hour length interval. DateTime or Date32 or DateTime64
  • time_zone — Optional. A String type const value or an expression representing the time zone. String

Returned value

Returns the time rounded to the start of a half-an-hour length interval. DateTime

Examples

Round time to half-hour interval

SELECT timeSlot(toDateTime('2000-01-02 03:04:05', 'UTC'))
┌─timeSlot(toDateTime('2000-01-02 03:04:05', 'UTC'))─┐
│                                2000-01-02 03:00:00 │
└────────────────────────────────────────────────────┘

Introduced in version 1.1.

timeSlots

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

timeSlots(StartTime, Duration[, Size])

Arguments

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) or Array(DateTime64)

Examples

Generate time slots for an interval

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))
┌─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']                        │
└───────────────────────────────────────────────────────────────────────────────────────────────────────────┘

Introduced in version 1.1.

timestamp

Converts the first argument expr to type DateTime64(6). If a second argument expr_time is provided, it adds the specified time to the converted value.

Syntax

timestamp(expr[, expr_time])

Arguments

  • expr — Date or date with time. String
  • expr_time — Optional. Time to add to the converted value. String

Returned value

Returns the converted value of expr, or expr with added time DateTime64(6)

Examples

Convert date string to DateTime64(6)

SELECT timestamp('2023-12-31') AS ts;
┌─────────────────────────ts─┐
│ 2023-12-31 00:00:00.000000 │
└────────────────────────────┘

Add time to date string

SELECT timestamp('2023-12-31 12:00:00', '12:00:00.11') AS ts;
┌─────────────────────────ts─┐
│ 2024-01-01 00:00:00.110000 │
└────────────────────────────┘

Introduced in version 23.9.

timezone

Returns the time zone name of the current session or converts a time zone offset or name to a canonical time zone name.

Syntax

timezone()

Returned value

Returns the canonical time zone name as a String

Examples

Usage example

SELECT timezone()
┌─timezone()───────┐
│ Europe/Amsterdam │
└──────────────────┘

Introduced in version 21.4.

timezoneOf

Returns the timezone name of a DateTime or DateTime64 value.

Syntax

timeZoneOf(datetime)

Arguments

  • datetime — A value of type. DateTime or DateTime64
  • timezone — Optional. Timezone name to convert the datetime value's timezone to. String

Returned value

Returns the timezone name for datetime String

Examples

Usage example

SELECT timezoneOf(now());
┌─timezoneOf(now())─┐
│ Europe/Amsterdam  │
└───────────────────┘

Introduced in version 21.4.

timezoneOffset

Returns the timezone offset in seconds from UTC. The function takes daylight saving time and historical timezone changes at the specified date and time into account.

Syntax

timeZoneOffset(datetime)

Arguments

Returned value

Returns the offset from UTC in seconds Int32

Examples

Usage example

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;
┌────────────────Time─┬─Type─────────────────────────┬─Offset_in_seconds─┬─Offset_in_hours─┐
│ 2021-04-21 10:20:30 │ DateTime('America/New_York') │            -14400 │              -4 │
└─────────────────────┴──────────────────────────────┴───────────────────┴─────────────────┘

Introduced in version 21.6.

toDayOfMonth

Returns the day of the month (1-31) of a Date or DateTime.

Syntax

toDayOfMonth(datetime)

Arguments

Returned value

Returns the day of the month of the given date/time UInt8

Examples

Usage example

SELECT toDayOfMonth(toDateTime('2023-04-21 10:20:30'))
┌─toDayOfMonth(toDateTime('2023-04-21 10:20:30'))─┐
│                                              21 │
└─────────────────────────────────────────────────┘

Introduced in version 1.1.

toDayOfWeek

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.

ModeFirst day of weekRange
0Monday1-7: Monday = 1, Tuesday = 2, ..., Sunday = 7
1Monday0-6: Monday = 0, Tuesday = 1, ..., Sunday = 6
2Sunday0-6: Sunday = 0, Monday = 1, ..., Saturday = 6
3Sunday1-7: Sunday = 1, Monday = 2, ..., Saturday = 7

Syntax

toDayOfWeek(datetime[, mode[, timezone]])

Arguments

  • datetime — Date or date with time to get the day of week from. Date or Date32 or DateTime or DateTime64
  • mode — Optional. Integer specifying the week mode (0-3). Defaults to 0 if omitted. UInt8
  • timezone — Optional. Timezone to use for the conversion. String

Returned value

Returns the day of the week for the given Date or DateTime UInt8

Examples

Usage example

-- The following date is April 21, 2023, which was a Friday:
SELECT
    toDayOfWeek(toDateTime('2023-04-21')),
    toDayOfWeek(toDateTime('2023-04-21'), 1)
┌─toDayOfWeek(toDateTime('2023-04-21'))─┬─toDayOfWeek(toDateTime('2023-04-21'), 1)─┐
│                                     5 │                                        4 │
└───────────────────────────────────────┴──────────────────────────────────────────┘

Introduced in version 1.1.

toDayOfYear

Returns the number of the day within the year (1-366) of a Date or DateTime value.

Syntax

toDayOfYear(datetime)

Arguments

Returned value

Returns the day of the year of the given Date or DateTime UInt16

Examples

Usage example

SELECT toDayOfYear(toDateTime('2023-04-21 10:20:30'))
┌─toDayOfYear(toDateTime('2023-04-21 10:20:30'))─┐
│                                            111 │
└────────────────────────────────────────────────┘

Introduced in version 18.4.

toDaysSinceYearZero

For a given date, returns the number of days which have passed since 1 January 0000 in the proleptic Gregorian calendar defined by ISO 8601.

The calculation is the same as in MySQL's TO_DAYS function.

Syntax

toDaysSinceYearZero(date[, time_zone])

Arguments

  • date — The date or date with time for which to calculate the number of days since year zero from. Date or Date32 or DateTime or DateTime64
  • time_zone — Time zone. String

Returned value

Returns the number of days passed since date 0000-01-01. UInt32

Examples

Calculate days since year zero

SELECT toDaysSinceYearZero(toDate('2023-09-08'))
┌─toDaysSinceYearZero(toDate('2023-09-08')))─┐
│                                     713569 │
└────────────────────────────────────────────┘

Introduced in version 23.9.

toHour

Returns the hour component (0-23) of a DateTime or DateTime64 value.

Syntax

toHour(datetime)

Arguments

Returned value

Returns the hour (0-23) of datetime. UInt8

Examples

Usage example

SELECT toHour(toDateTime('2023-04-21 10:20:30'))
┌─toHour(toDateTime('2023-04-21 10:20:30'))─┐
│                                        10 │
└───────────────────────────────────────────┘

Introduced in version 1.1.

toISOWeek

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

toISOWeek(datetime[, timezone])

Arguments

Returned value

Returns the ISO week number according to ISO 8601 standard. Returns a number between 1 and 53. UInt8

Examples

Get ISO week numbers

SELECT toDate('2016-12-27') AS date, toISOWeek(date) AS isoWeek
┌───────date─┬─isoWeek─┐
│ 2016-12-27 │      52 │
└────────────┴─────────┘

ISO week can belong to different year

SELECT toDate('2025-12-29') AS date, toISOWeek(date) AS isoWeek, toYear(date) AS year
┌───────date─┬─isoWeek─┬─year─┐
│ 2025-12-29 │       1 │ 2025 │
└────────────┴─────────┴──────┘

Introduced in version 20.1.

toISOYear

Converts a date or date with time to the ISO year number.

Syntax

toISOYear(datetime)

Arguments

Returned value

Returns the input value converted to an ISO year number. UInt16

Examples

Get ISO year from date values

SELECT
toISOYear(toDate('2024/10/02')) as year1,
toISOYear(toDateTime('2024-10-02 01:30:00')) as year2
┌─week1─┬─week2─┐
│    40 │    40 │
└───────┴───────┘

Introduced in version 18.4.

toLastDayOfMonth

Rounds up a date or date with time to the last day of the month.

:::note The return type can be configured by setting enable_extended_results_for_datetime_functions. :::

Syntax

toLastDayOfMonth(value)

Arguments

Returned value

Returns the date of the last day of the month for the given date or date with time. Date

Examples

Round up to the last day of the month

SELECT toLastDayOfMonth(toDateTime('2023-04-21 10:20:30'))
┌─toLastDayOfMonth(toDateTime('2023-04-21 10:20:30'))─┐
│                                          2023-04-30 │
└─────────────────────────────────────────────────────┘

Introduced in version 1.1.

toLastDayOfWeek

Rounds a date or date with time up to the nearest Saturday or Sunday.

:::note The return type can be configured by setting enable_extended_results_for_datetime_functions. :::

Syntax

toLastDayOfWeek(datetime[, mode[, timezone]])

Arguments

  • datetime — A date or date with time to convert. Date or DateTime or Date32 or DateTime64
  • mode — Determines the first day of the week as described in the toWeek() function. Default 0. UInt8
  • timezone — Optional. The timezone to use for the conversion. If not specified, the server's timezone is used. String

Returned value

Returns the date of the nearest Saturday or Sunday, on or after the given date, depending on the mode Date or Date32 or DateTime or DateTime64

Examples

Round up to the nearest Saturday or Sunday

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

Introduced in version 23.5.

toMillisecond

Returns the millisecond component (0-999) of a DateTime or DateTime64 value.

Syntax

toMillisecond(datetime)

Arguments

Returned value

Returns the millisecond in the minute (0 - 59) of datetime. UInt16

Examples

Usage example

SELECT toMillisecond(toDateTime64('2023-04-21 10:20:30.456', 3));
┌──toMillisecond(toDateTime64('2023-04-21 10:20:30.456', 3))─┐
│                                                        456 │
└────────────────────────────────────────────────────────────┘

Introduced in version 24.2.

toMinute

Returns the minute component (0-59) of a Date or DateTime value.

Syntax

toMinute(datetime)

Arguments

Returned value

Returns the minute of the hour (0 - 59) of datetime. UInt8

Examples

Usage example

SELECT toMinute(toDateTime('2023-04-21 10:20:30'))
┌─toMinute(toDateTime('2023-04-21 10:20:30'))─┐
│                                          20 │
└─────────────────────────────────────────────┘

Introduced in version 1.1.

toModifiedJulianDay

Converts a Proleptic Gregorian calendar date in text form YYYY-MM-DD to a Modified Julian Day 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

toModifiedJulianDay(date)

Arguments

Returned value

Returns Modified Julian Day number. Int32

Examples

Convert date to Modified Julian Day

SELECT toModifiedJulianDay('2020-01-01')
┌─toModifiedJulianDay('2020-01-01')─┐
│                             58849 │
└───────────────────────────────────┘

Introduced in version 21.1.

toModifiedJulianDayOrNull

Similar to toModifiedJulianDay(), but instead of raising exceptions it returns NULL.

Syntax

toModifiedJulianDayOrNull(date)

Arguments

Returned value

Returns the modified Julian day number for valid date, otherwise null. Nullable(Int32)

Examples

Convert date to Modified Julian Day with null handling

SELECT toModifiedJulianDayOrNull('2020-01-01');
SELECT toModifiedJulianDayOrNull('0000-00-00'); -- invalid date, returns NULL
┌─toModifiedJu⋯020-01-01')─┐
│                    58849 │
└──────────────────────────┘
┌─toModifiedJu⋯000-00-00')─┐
│                     ᴺᵁᴸᴸ │
└──────────────────────────┘

Introduced in version 21.1.

toMonday

Rounds down a date or date with time to the Monday of the same week. Returns the date.

:::note The return type can be configured by setting enable_extended_results_for_datetime_functions. :::

Syntax

toMonday(value)

Arguments

Returned value

Returns the date of the Monday of the same week for the given date or date with time. Date

Examples

Round down to the Monday of the week

SELECT
toMonday(toDateTime('2023-04-21 10:20:30')), -- A Friday
toMonday(toDate('2023-04-24'));              -- Already a Monday
┌─toMonday(toDateTime('2023-04-21 10:20:30'))─┬─toMonday(toDate('2023-04-24'))─┐
│                                  2023-04-17 │                     2023-04-24 │
└─────────────────────────────────────────────┴────────────────────────────────┘

Introduced in version 1.1.

toMonth

Returns the month component (1-12) of a Date or DateTime value.

Syntax

toMonth(datetime)

Arguments

Returned value

Returns the month of the given date/time UInt8

Examples

Usage example

SELECT toMonth(toDateTime('2023-04-21 10:20:30'))
┌─toMonth(toDateTime('2023-04-21 10:20:30'))─┐
│                                          4 │
└────────────────────────────────────────────┘

Introduced in version 1.1.

toMonthNumSinceEpoch

Returns amount of months passed from year 1970

Syntax

toMonthNumSinceEpoch(date)

Arguments

Returned value

Positive integer

Examples

Example

SELECT toMonthNumSinceEpoch(toDate('2024-10-01'))
657

Introduced in version 25.3.

toQuarter

Returns the quarter of the year (1-4) for a given Date or DateTime value.

Syntax

toQuarter(datetime)

Arguments

Returned value

Returns the quarter of the year for the given date/time UInt8

Examples

Usage example

SELECT toQuarter(toDateTime('2023-04-21 10:20:30'))
┌─toQuarter(toDateTime('2023-04-21 10:20:30'))─┐
│                                            2 │
└──────────────────────────────────────────────┘

Introduced in version 1.1.

toRelativeDayNum

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

toRelativeDayNum(date)

Arguments

Returned value

Returns the number of days from a fixed reference point in the past. UInt32

Examples

Get relative day numbers

SELECT toRelativeDayNum(toDate('2023-04-01')) - toRelativeDayNum(toDate('2023-01-01'))
┌─minus(toRela⋯3-01-01')))─┐
│                       90 │
└──────────────────────────┘

Introduced in version 1.1.

toRelativeHourNum

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

toRelativeHourNum(date)

Arguments

Returned value

Returns the number of hours from a fixed reference point in the past. UInt32

Examples

Get relative hour numbers

SELECT toRelativeHourNum(toDateTime('2023-01-01 12:00:00')) - toRelativeHourNum(toDateTime('2023-01-01 00:00:00')) AS hours_difference
┌─hours_difference─┐
│               12 │
└──────────────────┘

Introduced in version 1.1.

toRelativeMinuteNum

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

toRelativeMinuteNum(date)

Arguments

Returned value

Returns the number of minutes from a fixed reference point in the past. UInt32

Examples

Get relative minute numbers

SELECT toRelativeMinuteNum(toDateTime('2023-01-01 00:30:00')) - toRelativeMinuteNum(toDateTime('2023-01-01 00:00:00')) AS minutes_difference
┌─minutes_difference─┐
│                 30 │
└────────────────────┘

Introduced in version 1.1.

toRelativeMonthNum

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

toRelativeMonthNum(date)

Arguments

Returned value

Returns the number of months from a fixed reference point in the past. UInt32

Examples

Get relative month numbers

SELECT toRelativeMonthNum(toDate('2023-04-01')) - toRelativeMonthNum(toDate('2023-01-01')) AS months_difference
┌─months_difference─┐
│                 3 │
└───────────────────┘

Introduced in version 1.1.

toRelativeQuarterNum

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

toRelativeQuarterNum(date)

Arguments

Returned value

Returns the number of quarters from a fixed reference point in the past. UInt32

Examples

Get relative quarter numbers

SELECT toRelativeQuarterNum(toDate('2023-04-01')) - toRelativeQuarterNum(toDate('2023-01-01')) AS quarters_difference
┌─quarters_difference─┐
│                   1 │
└─────────────────────┘

Introduced in version 1.1.

toRelativeSecondNum

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

toRelativeSecondNum(date)

Arguments

Returned value

Returns the number of seconds from a fixed reference point in the past. UInt32

Examples

Get relative second numbers

SELECT toRelativeSecondNum(toDateTime('2023-01-01 00:01:00')) - toRelativeSecondNum(toDateTime('2023-01-01 00:00:00')) AS seconds_difference
┌─seconds_difference─┐
│                 60 │
└────────────────────┘

Introduced in version 1.1.

toRelativeWeekNum

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

toRelativeWeekNum(date)

Arguments

Returned value

Returns the number of weeks from a fixed reference point in the past. UInt32

Examples

Get relative week numbers

SELECT toRelativeWeekNum(toDate('2023-01-08')) - toRelativeWeekNum(toDate('2023-01-01')) AS weeks_difference
┌─weeks_difference─┐
│                1 │
└──────────────────┘

Introduced in version 1.1.

toRelativeYearNum

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

toRelativeYearNum(date)

Arguments

Returned value

Returns the number of years from a fixed reference point in the past. UInt16

Examples

Get relative year numbers

SELECT toRelativeYearNum('2010-10-01'::DateTime) - toRelativeYearNum('2000-01-01'::DateTime)
┌─minus(toRela⋯ateTime')))─┐
│                       10 │
└──────────────────────────┘

Introduced in version 1.1.

toSecond

Returns the second component (0-59) of a DateTime or DateTime64 value.

Syntax

toSecond(datetime)

Arguments

Returned value

Returns the second in the minute (0 - 59) of datetime. UInt8

Examples

Usage example

SELECT toSecond(toDateTime('2023-04-21 10:20:30'))
┌─toSecond(toDateTime('2023-04-21 10:20:30'))─┐
│                                          30 │
└─────────────────────────────────────────────┘

Introduced in version 1.1.

toStartOfDay

Rounds down a date with time to the start of the day.

:::note The return type can be configured by setting enable_extended_results_for_datetime_functions. :::

Syntax

toStartOfDay(datetime)

Arguments

  • datetime — A date or date with time to round. Date or DateTime

Returned value

Returns the date with time rounded down to the start of the day. Date or DateTime or Date32 or DateTime64

Examples

Round down to the start of the day

SELECT toStartOfDay(toDateTime('2023-04-21 10:20:30'))
┌─toStartOfDay(toDateTime('2023-04-21 10:20:30'))─┐
│                             2023-04-21 00:00:00 │
└─────────────────────────────────────────────────┘

Introduced in version 1.1.

toStartOfFifteenMinutes

Rounds down the date with time to the start of the fifteen-minute interval.

:::note The return type can be configured by setting enable_extended_results_for_datetime_functions. :::

Syntax

toStartOfFifteenMinutes(datetime)

Arguments

Returned value

Returns the date with time rounded to the start of the nearest fifteen-minute interval DateTime or DateTime64

Examples

Example

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

Introduced in version 1.1.

toStartOfFiveMinutes

Rounds down a date with time to the start of the nearest five-minute interval.

:::note The return type can be configured by setting enable_extended_results_for_datetime_functions. :::

Syntax

toStartOfFiveMinutes(datetime)

Arguments

Returned value

Returns the date with time rounded to the start of the nearest five-minute interval DateTime or DateTime64

Examples

Example

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

Introduced in version 22.6.

toStartOfHour

Rounds down a date with time to the start of the hour.

:::note The return type can be configured by setting enable_extended_results_for_datetime_functions. :::

Syntax

toStartOfHour(datetime)

Arguments

Returned value

Returns the date with time rounded down to the start of the hour. DateTime or DateTime64

Examples

Round down to the start of the hour

SELECT
    toStartOfHour(toDateTime('2023-04-21 10:20:30'));
┌─────────────────res─┬─toTypeName(res)─┐
│ 2023-04-21 10:00:00 │ DateTime        │
└─────────────────────┴─────────────────┘

Introduced in version 1.1.

toStartOfISOYear

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.

:::note The return type can be configured by setting enable_extended_results_for_datetime_functions. :::

Syntax

toStartOfISOYear(value)

Arguments

Returned value

Returns the first day of the ISO year for the given date or date with time. Date

Examples

Round down to the first day of the ISO year

SELECT toStartOfISOYear(toDateTime('2023-04-21 10:20:30'))
┌─toStartOfISOYear(toDateTime('2023-04-21 10:20:30'))─┐
│                                          2023-01-02 │
└─────────────────────────────────────────────────────┘

Introduced in version 1.1.

toStartOfInterval

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:

IntervalStart
YEARyear 0
QUARTER1900 Q1
MONTH1900 January
WEEK1970, 1st week (01-05)
DAY1970-01-01
HOUR(*)
MINUTE1970-01-01 00:00:00
SECOND1970-01-01 00:00:00
MILLISECOND1970-01-01 00:00:00
MICROSECOND1970-01-01 00:00:00
NANOSECOND1970-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

toStartOfInterval(value, INTERVAL x unit[, time_zone])
toStartOfInterval(value, INTERVAL x unit[, origin[, time_zone]])

Arguments

  • value — Date or date with time value to round down. Date or DateTime or 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

Examples

Basic interval rounding

SELECT toStartOfInterval(toDateTime('2023-01-15 14:30:00'), INTERVAL 1 MONTH)
┌─toStartOfInt⋯alMonth(1))─┐
│               2023-01-01 │
└──────────────────────────┘

Using origin point

SELECT toStartOfInterval(toDateTime('2023-01-01 14:45:00'), INTERVAL 1 MINUTE, toDateTime('2023-01-01 14:35:30'))
┌─toStartOfInt⋯14:35:30'))─┐
│      2023-01-01 14:44:30 │
└──────────────────────────┘

Introduced in version 20.1.

toStartOfMicrosecond

Rounds down a date with time to the start of the microseconds.

Syntax

toStartOfMicrosecond(datetime[, timezone])

Arguments

  • datetime — Date and time. DateTime64
  • timezone — Optional. Timezone for the returned value. If not specified, the function uses the timezone of the value parameter. String

Returned value

Input value with sub-microseconds DateTime64

Examples

Query without timezone

WITH toDateTime64('2020-01-01 10:20:30.999999999', 9) AS dt64
SELECT toStartOfMicrosecond(dt64);
┌────toStartOfMicrosecond(dt64)─┐
│ 2020-01-01 10:20:30.999999000 │
└───────────────────────────────┘

Query with timezone

WITH toDateTime64('2020-01-01 10:20:30.999999999', 9) AS dt64
SELECT toStartOfMicrosecond(dt64, 'Asia/Istanbul');
┌─toStartOfMicrosecond(dt64, 'Asia/Istanbul')─┐
│               2020-01-01 12:20:30.999999000 │
└─────────────────────────────────────────────┘

Introduced in version 22.6.

toStartOfMillisecond

Rounds down a date with time to the start of the milliseconds.

Syntax

toStartOfMillisecond(datetime[, timezone])

Arguments

  • datetime — Date and time. DateTime64
  • timezone — Optional. Timezone for the returned value. If not specified, the function uses the timezone of the value parameter. String

Returned value

Input value with sub-milliseconds. DateTime64

Examples

Query without timezone

WITH toDateTime64('2020-01-01 10:20:30.999999999', 9) AS dt64
SELECT toStartOfMillisecond(dt64);
┌────toStartOfMillisecond(dt64)─┐
│ 2020-01-01 10:20:30.999000000 │
└───────────────────────────────┘

Query with timezone

WITH toDateTime64('2020-01-01 10:20:30.999999999', 9) AS dt64
SELECT toStartOfMillisecond(dt64, 'Asia/Istanbul');
┌─toStartOfMillisecond(dt64, 'Asia/Istanbul')─┐
│               2020-01-01 12:20:30.999000000 │
└─────────────────────────────────────────────┘

Introduced in version 22.6.

toStartOfMinute

Rounds down a date with time to the start of the minute.

:::note The return type can be configured by setting enable_extended_results_for_datetime_functions. :::

Syntax

toStartOfMinute(datetime)

Arguments

Returned value

Returns the date with time rounded down to the start of the minute. DateTime or DateTime64

Examples

Round down to the start of the minute

SELECT
    toStartOfMinute(toDateTime('2023-04-21 10:20:30')),
    toStartOfMinute(toDateTime64('2023-04-21 10:20:30.5300', 8))
FORMAT Vertical
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

Introduced in version 1.1.

toStartOfMonth

Rounds down a date or date with time to the first day of the month.

:::note The return type can be configured by setting enable_extended_results_for_datetime_functions. :::

Syntax

toStartOfMonth(value)

Arguments

Returned value

Returns the first day of the month for the given date or date with time. Date

Examples

Round down to the first day of the month

SELECT toStartOfMonth(toDateTime('2023-04-21 10:20:30'))
┌─toStartOfMonth(toDateTime('2023-04-21 10:20:30'))─┐
│                                        2023-04-01 │
└───────────────────────────────────────────────────┘

Introduced in version 1.1.

toStartOfNanosecond

Rounds down a date with time to the start of the nanoseconds.

Syntax

toStartOfNanosecond(datetime[, timezone])

Arguments

  • datetime — Date and time. DateTime64
  • timezone — Optional. Timezone for the returned value. If not specified, the function uses the timezone of the value parameter. String

Returned value

Input value with nanoseconds. DateTime64

Examples

Query without timezone

WITH toDateTime64('2020-01-01 10:20:30.999999999', 9) AS dt64
SELECT toStartOfNanosecond(dt64);
┌─────toStartOfNanosecond(dt64)─┐
│ 2020-01-01 10:20:30.999999999 │
└───────────────────────────────┘

Query with timezone

WITH toDateTime64('2020-01-01 10:20:30.999999999', 9) AS dt64
SELECT toStartOfNanosecond(dt64, 'Asia/Istanbul');
┌─toStartOfNanosecond(dt64, 'Asia/Istanbul')─┐
│              2020-01-01 12:20:30.999999999 │
└────────────────────────────────────────────┘

Introduced in version 22.6.

toStartOfQuarter

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.

:::note The return type can be configured by setting enable_extended_results_for_datetime_functions. :::

Syntax

toStartOfQuarter(value)

Arguments

Returned value

Returns the first day of the quarter for the given date or date with time. Date

Examples

Round down to the first day of the quarter

SELECT toStartOfQuarter(toDateTime('2023-04-21 10:20:30'))
┌─toStartOfQuarter(toDateTime('2023-04-21 10:20:30'))─┐
│                                          2023-04-01 │
└─────────────────────────────────────────────────────┘

Introduced in version 1.1.

toStartOfSecond

Rounds down a date with time to the start of the seconds.

Syntax

toStartOfSecond(datetime[, timezone])

Arguments

  • datetime — Date and time to truncate sub-seconds from. DateTime64
  • timezone — Optional. Timezone for the returned value. If not specified, the function uses the timezone of the value parameter. String

Returned value

Returns the input value without sub-seconds. DateTime64

Examples

Query without timezone

WITH toDateTime64('2020-01-01 10:20:30.999', 3) AS dt64
SELECT toStartOfSecond(dt64);
┌───toStartOfSecond(dt64)─┐
│ 2020-01-01 10:20:30.000 │
└─────────────────────────┘

Query with timezone

WITH toDateTime64('2020-01-01 10:20:30.999', 3) AS dt64
SELECT toStartOfSecond(dt64, 'Asia/Istanbul');
┌─toStartOfSecond(dt64, 'Asia/Istanbul')─┐
│                2020-01-01 13:20:30.000 │
└────────────────────────────────────────┘

Introduced in version 20.5.

toStartOfTenMinutes

Rounds down a date with time to the start of the nearest ten-minute interval.

:::note The return type can be configured by setting enable_extended_results_for_datetime_functions. :::

Syntax

toStartOfTenMinutes(datetime)

Arguments

Returned value

Returns the date with time rounded to the start of the nearest ten-minute interval DateTime or DateTime64

Examples

Example

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

Introduced in version 20.1.

toStartOfWeek

Rounds a date or date with time down to the nearest Sunday or Monday.

:::note The return type can be configured by setting enable_extended_results_for_datetime_functions. :::

Syntax

toStartOfWeek(datetime[, mode[, timezone]])

Arguments

  • datetime — A date or date with time to convert. Date or DateTime or Date32 or DateTime64
  • mode — Determines the first day of the week as described in the toWeek() function. Default 0. UInt8
  • timezone — The timezone to use for the conversion. If not specified, the server's timezone is used. String

Returned value

Returns the date of the nearest Sunday or Monday on, or prior to, the given date, depending on the mode Date or Date32 or DateTime or DateTime64

Examples

Round down to the nearest Sunday or Monday

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

Introduced in version 20.1.

toStartOfYear

Rounds down a date or date with time to the first day of the year. Returns the date as a Date object.

:::note The return type can be configured by setting enable_extended_results_for_datetime_functions. :::

Syntax

toStartOfYear(value)

Arguments

Returned value

Returns the first day of the year for the given date/time Date

Examples

Round down to the first day of the year

SELECT toStartOfYear(toDateTime('2023-04-21 10:20:30'))
┌─toStartOfYear(toDateTime('2023-04-21 10:20:30'))─┐
│                                       2023-01-01 │
└──────────────────────────────────────────────────┘

Introduced in version 1.1.

toTimeWithFixedDate

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

toTime(date[, timezone])

Arguments

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

Examples

Calculate the time difference between two dates

SELECT toTime('2025-06-15 12:00:00'::DateTime) - toTime('2024-05-10 11:00:00'::DateTime) AS result, toTypeName(result)
┌─result─┬─toTypeName(result)─┐
│   3600 │ Int32              │
└────────┴────────────────────┘

Introduced in version 1.1.

toTimezone

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

toTimeZone(datetime, timezone)

Arguments

Returned value

Returns the same timestamp as the input, but with the specified time zone DateTime or DateTime64

Examples

Usage example

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;
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

Introduced in version 1.1.

toUTCTimestamp

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

toUTCTimestamp(datetime, time_zone)

Arguments

  • datetime — A date or date with time type const value or an expression. DateTime or DateTime64
  • time_zone — A String type const value or an expression representing the time zone. String

Returned value

Returns a date or date with time in UTC timezone. DateTime or DateTime64

Examples

Convert timezone to UTC

SELECT toUTCTimestamp(toDateTime('2023-03-16'), 'Asia/Shanghai')
┌─toUTCTimestamp(toDateTime('2023-03-16'), 'Asia/Shanghai')─┐
│                                     2023-03-15 16:00:00 │
└─────────────────────────────────────────────────────────┘

Introduced in version 23.8.

toUnixTimestamp

Converts a String, Date, or DateTime to a Unix timestamp (seconds since 1970-01-01 00:00:00 UTC) as UInt32.

Syntax

toUnixTimestamp(date[, timezone])

Arguments

Returned value

Returns the Unix timestamp. UInt32

Examples

Usage example

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;
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

Introduced in version 1.1.

toWeek

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() is a compatibility function that is equivalent to toWeek(date,3).

The following table describes how the mode argument works.

ModeFirst day of weekRangeWeek 1 is the first week ...
0Sunday0-53with a Sunday in this year
1Monday0-53with 4 or more days this year
2Sunday1-53with a Sunday in this year
3Monday1-53with 4 or more days this year
4Sunday0-53with 4 or more days this year
5Monday0-53with a Monday in this year
6Sunday1-53with 4 or more days this year
7Monday1-53with a Monday in this year
8Sunday1-53contains January 1
9Monday1-53contains 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 in a format supported by 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

toWeek(datetime[, mode[, time_zone]])

Arguments

  • datetime — Date or date with time to get the week number from. Date or 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

Returned value

Returns the week number according to the specified mode. UInt32

Examples

Get week numbers with different modes

SELECT toDate('2016-12-27') AS date, toWeek(date) AS week0, toWeek(date,1) AS week1, toWeek(date,9) AS week9
┌───────date─┬─week0─┬─week1─┬─week9─┐
│ 2016-12-27 │    52 │    52 │     1 │
└────────────┴───────┴───────┴───────┘

Introduced in version 20.1.

toYYYYMM

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

toYYYYMM(datetime[, timezone])

Arguments

  • datetime — A date or date with time to convert. Date or Date32 or DateTime or DateTime64
  • timezone — Optional. Timezone for the conversion. If provided, the timezone must be a string constant. String

Returned value

Returns a UInt32 number containing the year and month number (YYYY * 100 + MM). UInt32

Examples

Convert current date to YYYYMM format

SELECT toYYYYMM(now(), 'US/Eastern')
┌─toYYYYMM(now(), 'US/Eastern')─┐
│                        202303 │
└───────────────────────────────┘

Introduced in version 1.1.

toYYYYMMDD

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

toYYYYMMDD(datetime[, timezone])

Arguments

  • datetime — A date or date with time to convert. Date or Date32 or DateTime or DateTime64
  • timezone — Optional. Timezone for the conversion. If provided, the timezone must be a string constant. String

Returned value

Returns a UInt32 number containing the year, month and day (YYYY * 10000 + MM * 100 + DD). UInt32

Examples

Convert current date to YYYYMMDD format

SELECT toYYYYMMDD(now(), 'US/Eastern')
┌─toYYYYMMDD(now(), 'US/Eastern')─┐
│                        20230302 │
└─────────────────────────────────┘

Introduced in version 1.1.

toYYYYMMDDhhmmss

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

toYYYYMMDDhhmmss(datetime[, timezone])

Arguments

  • datetime — Date or date with time to convert. Date or Date32 or DateTime or DateTime64
  • timezone — Optional. Timezone for the conversion. If provided, the timezone must be a string constant. 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

Examples

Convert current date and time to YYYYMMDDhhmmss format

SELECT toYYYYMMDDhhmmss(now(), 'US/Eastern')
┌─toYYYYMMDDhhmmss(now(), 'US/Eastern')─┐
│                        20230302112209 │
└───────────────────────────────────────┘

Introduced in version 1.1.

toYear

Returns the year component (AD) of a Date or DateTime value.

Syntax

toYear(datetime)

Arguments

Returned value

Returns the year of the given Date or DateTime UInt16

Examples

Usage example

SELECT toYear(toDateTime('2023-04-21 10:20:30'))
┌─toYear(toDateTime('2023-04-21 10:20:30'))─┐
│                                     2023  │
└───────────────────────────────────────────┘

Introduced in version 1.1.

toYearNumSinceEpoch

Returns amount of years passed from year 1970

Syntax

toYearNumSinceEpoch(date)

Arguments

Returned value

Positive integer

Examples

Example

SELECT toYearNumSinceEpoch(toDate('2024-10-01'))
54

Introduced in version 25.3.

toYearWeek

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().

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 in a format supported by 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

toYearWeek(datetime[, mode[, timezone]])

Arguments

  • datetime — Date or date with time to get the year and week of. Date or 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

Returned value

Returns year and week number as a combined integer value. UInt32

Examples

Get year-week combinations with different modes

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
┌───────date─┬─yearWeek0─┬─yearWeek1─┬─yearWeek9─┬─prev_yearWeek─┐
│ 2016-12-27 │    201652 │    201652 │    201701 │        202152 │
└────────────┴───────────┴───────────┴───────────┴───────────────┘

Introduced in version 20.1.

today

Returns the current date at moment of query analysis. Same as toDate(now()).

Syntax

today()

Returned value

Returns the current date Date

Examples

Usage example

SELECT today() AS today, curdate() AS curdate, current_date() AS current_date FORMAT Pretty
┏━━━━━━━━━━━━┳━━━━━━━━━━━━┳━━━━━━━━━━━━━━┓
┃      today ┃    curdate ┃ current_date ┃
┡━━━━━━━━━━━━╇━━━━━━━━━━━━╇━━━━━━━━━━━━━━┩
│ 2025-03-03 │ 2025-03-03 │   2025-03-03 │
└────────────┴────────────┴──────────────┘

Introduced in version 1.1.

yesterday

Accepts zero arguments and returns yesterday's date at one of the moments of query analysis.

Syntax

yesterday()

Returned value

Returns yesterday's date. Date

Examples

Get yesterday's date

SELECT yesterday();
SELECT today() - 1;
┌─yesterday()─┐
│  2025-06-09 │
└─────────────┘
┌─minus(today(), 1)─┐
│        2025-06-09 │
└───────────────────┘

Introduced in version 1.1.

Conditional

Conditional functions reference.

Dictionary

Dictionary functions reference.

On this page

UTCTimestampYYYYMMDDToDateYYYYMMDDToDate32YYYYMMDDhhmmssToDateTimeYYYYMMDDhhmmssToDateTime64addDateaddDaysaddHoursaddIntervaladdMicrosecondsaddMillisecondsaddMinutesaddMonthsaddNanosecondsaddQuartersaddSecondsaddTupleOfIntervalsaddWeeksaddYearsagechangeDaychangeHourchangeMinutechangeMonthchangeSecondchangeYeardateDiffdateNamedateTruncformatDateTimeformatDateTimeInJodaSyntaxfromDaysSinceYearZerofromDaysSinceYearZero32fromModifiedJulianDayfromModifiedJulianDayOrNullfromUTCTimestampfromUnixTimestampfromUnixTimestampInJodaSyntaxmakeDatemakeDate32makeDateTimemakeDateTime64monthNamenownow64nowInBlocknowInBlock64serverTimezonesubDatesubtractDayssubtractHourssubtractIntervalsubtractMicrosecondssubtractMillisecondssubtractMinutessubtractMonthssubtractNanosecondssubtractQuarterssubtractSecondssubtractTupleOfIntervalssubtractWeekssubtractYearstimeDifftimeSlottimeSlotstimestamptimezonetimezoneOftimezoneOffsettoDayOfMonthtoDayOfWeektoDayOfYeartoDaysSinceYearZerotoHourtoISOWeektoISOYeartoLastDayOfMonthtoLastDayOfWeektoMillisecondtoMinutetoModifiedJulianDaytoModifiedJulianDayOrNulltoMondaytoMonthtoMonthNumSinceEpochtoQuartertoRelativeDayNumtoRelativeHourNumtoRelativeMinuteNumtoRelativeMonthNumtoRelativeQuarterNumtoRelativeSecondNumtoRelativeWeekNumtoRelativeYearNumtoSecondtoStartOfDaytoStartOfFifteenMinutestoStartOfFiveMinutestoStartOfHourtoStartOfISOYeartoStartOfIntervaltoStartOfMicrosecondtoStartOfMillisecondtoStartOfMinutetoStartOfMonthtoStartOfNanosecondtoStartOfQuartertoStartOfSecondtoStartOfTenMinutestoStartOfWeektoStartOfYeartoTimeWithFixedDatetoTimezonetoUTCTimestamptoUnixTimestamptoWeektoYYYYMMtoYYYYMMDDtoYYYYMMDDhhmmsstoYeartoYearNumSinceEpochtoYearWeektodayyesterday