Skip to main content

Documentation Index

Fetch the complete documentation index at: https://private-7c7dfe99-page-updates.mintlify.app/llms.txt

Use this file to discover all available pages before exploring further.

​
Functions for working with dates and times

Most functions in this section accept an optional time zone argument, e.g. Europe/Amsterdam. In this case, the time zone is the specified one instead of the local (default) one. Example 
SELECT
    toDateTime('2016-06-15 23:00:00') AS time,
    toDate(time) AS date_local,
    toDate(time, 'Asia/Yekaterinburg') AS date_yekat,
    toString(time, 'US/Samoa') AS time_samoa

┌────────────────time─┬─date_local─┬─date_yekat─┬─time_samoa──────────┐
│ 2016-06-15 23:00:00 │ 2016-06-15 │ 2016-06-16 │ 2016-06-15 09:00:00 │
└─────────────────────┴────────────┴────────────┴─────────────────────┘
For SQL standard compatibility the following functions, NOW, CURRENT_TIMESTAMP, TODAY, and CURRENT_DATE can be used without parentheses.

​
UTCTimestamp

Introduced in: v22.11.0 Returns the current date and time at the moment of query analysis. The function is a constant expression. This function gives the same result that now('UTC') would. It was added only for MySQL support. now is the preferred usage. Syntax 
UTCTimestamp()
Aliases: UTC_timestamp Arguments
  • None.
Returned value Returns the current date and time at the moment of query analysis. DateTime Examples Get current UTC timestamp
Query
SELECT UTCTimestamp()
Response
┌──────UTCTimestamp()─┐
│ 2024-05-28 08:32:09 │
└─────────────────────┘

​
YYYYMMDDToDate

Introduced in: v23.9.0 Converts a number containing the year, month and day number to a Date. This function is the opposite of function toYYYYMMDD(). 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
Query
SELECT YYYYMMDDToDate(20230911);
Response
┌─toYYYYMMDD(20230911)─┐
│           2023-09-11 │
└──────────────────────┘

​
YYYYMMDDToDate32

Introduced in: v23.9.0 Converts a number containing the year, month and day number to a Date32. This function is the opposite of function toYYYYMMDD(). 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
Query
SELECT YYYYMMDDToDate32(20000507);
Response
┌─YYYYMMDDToDate32(20000507)─┐
│                 2000-05-07 │
└────────────────────────────┘

​
YYYYMMDDhhmmssToDateTime

Introduced in: v23.9.0 Converts a number containing the year, month, day, hour, minute, and second to a DateTime. This function is the opposite of function toYYYYMMDDhhmmss(). 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
Query
SELECT YYYYMMDDToDateTime(20230911131415);
Response
┌──────YYYYMMDDhhmmssToDateTime(20230911131415)─┐
│                           2023-09-11 13:14:15 │
└───────────────────────────────────────────────┘

​
YYYYMMDDhhmmssToDateTime64

Introduced in: v23.9.0 Converts a number containing the year, month, day, hour, minute, and second to a DateTime64. This function is the opposite of function toYYYYMMDDhhmmss(). 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
Query
SELECT YYYYMMDDhhmmssToDateTime64(20230911131415, 3, 'Asia/Istanbul');
Response
┌─YYYYMMDDhhmm⋯/Istanbul')─┐
│  2023-09-11 13:14:15.000 │
└──────────────────────────┘

​
addDate

Introduced in: v23.9.0 Adds the time interval to the provided date, date with time or string-encoded date or date with time. If the addition results in a value outside the bounds of the data type, the result is undefined. Syntax 
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
Query
SELECT addDate(toDate('2018-01-01'), INTERVAL 3 YEAR)
Response
┌─addDate(toDa⋯valYear(3))─┐
│               2021-01-01 │
└──────────────────────────┘

​
addDays

Introduced in: v1.1.0 Adds a specified number of days to a date, a date with time or a string-encoded date or date with time. Syntax 
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
Query
WITH
    toDate('2024-01-01') AS date,
    toDateTime('2024-01-01 00:00:00') AS date_time,
    '2024-01-01 00:00:00' AS date_time_string
SELECT
    addDays(date, 5) AS add_days_with_date,
    addDays(date_time, 5) AS add_days_with_date_time,
    addDays(date_time_string, 5) AS add_days_with_date_time_string
Response
┌─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
Query
SELECT dateAdd('1998-06-16'::Date, INTERVAL 10 day)
Response
┌─plus(CAST('1⋯valDay(10))─┐
│               1998-06-26 │
└──────────────────────────┘

​
addHours

Introduced in: v1.1.0 Adds a specified number of hours to a date, a date with time or a string-encoded date or date with time. Syntax 
addHours(datetime, num)
Arguments Returned value Returns datetime plus num hours DateTime or DateTime64(3) Examples Add hours to different date types
Query
WITH
    toDate('2024-01-01') AS date,
    toDateTime('2024-01-01 00:00:00') AS date_time,
    '2024-01-01 00:00:00' AS date_time_string
SELECT
    addHours(date, 12) AS add_hours_with_date,
    addHours(date_time, 12) AS add_hours_with_date_time,
    addHours(date_time_string, 12) AS add_hours_with_date_time_string
Response
┌─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
Query
SELECT dateAdd('1998-06-16'::Date, INTERVAL 10 hour)
Response
┌─plus(CAST('1⋯alHour(10))─┐
│      1998-06-16 10:00:00 │
└──────────────────────────┘

​
addInterval

Introduced in: v22.11.0 Adds an interval to another interval or tuple of intervals.
Intervals of the same type will be combined into a single interval. For instance if toIntervalDay(1) and toIntervalDay(2) are passed then the result will be (3) rather than (1,1).
Syntax 
addInterval(interval_1, interval_2)
Arguments Returned value Returns a tuple of intervals Tuple(Interval) Examples Add intervals
Query
SELECT addInterval(INTERVAL 1 DAY, INTERVAL 1 MONTH);
SELECT addInterval((INTERVAL 1 DAY, INTERVAL 1 YEAR), INTERVAL 1 MONTH);
SELECT addInterval(INTERVAL 2 DAY, INTERVAL 1 DAY)
Response
┌─addInterval(toIntervalDay(1), toIntervalMonth(1))─┐
│ (1,1)                                             │
└───────────────────────────────────────────────────┘
┌─addInterval((toIntervalDay(1), toIntervalYear(1)), toIntervalMonth(1))─┐
│ (1,1,1)                                                                │
└────────────────────────────────────────────────────────────────────────┘
┌─addInterval(toIntervalDay(2), toIntervalDay(1))─┐
│ (3)                                             │
└─────────────────────────────────────────────────┘

​
addMicroseconds

Introduced in: v22.6.0 Adds a specified number of microseconds to a date with time or a string-encoded date with time. Syntax 
addMicroseconds(datetime, num)
Arguments Returned value Returns date_time plus num microseconds DateTime64 Examples Add microseconds to different date time types
Query
WITH
    toDateTime('2024-01-01 00:00:00') AS date_time,
    '2024-01-01 00:00:00' AS date_time_string
SELECT
    addMicroseconds(date_time, 1000000) AS add_microseconds_with_date_time,
    addMicroseconds(date_time_string, 1000000) AS add_microseconds_with_date_time_string
Response
┌─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
Query
SELECT dateAdd('1998-06-16'::DateTime, INTERVAL 10 microsecond)
Response
┌─plus(CAST('19⋯osecond(10))─┐
│ 1998-06-16 00:00:00.000010 │
└────────────────────────────┘

​
addMilliseconds

Introduced in: v22.6.0 Adds a specified number of milliseconds to a date with time or a string-encoded date with time. Syntax 
addMilliseconds(datetime, num)
Arguments Returned value Returns datetime plus num milliseconds DateTime64 Examples Add milliseconds to different date time types
Query
WITH
    toDateTime('2024-01-01 00:00:00') AS date_time,
    '2024-01-01 00:00:00' AS date_time_string
SELECT
    addMilliseconds(date_time, 1000) AS add_milliseconds_with_date_time,
    addMilliseconds(date_time_string, 1000) AS add_milliseconds_with_date_time_string
Response
┌─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
Query
SELECT dateAdd('1998-06-16'::DateTime, INTERVAL 10 millisecond)
Response
┌─plus(CAST('1⋯second(10))─┐
│  1998-06-16 00:00:00.010 │
└──────────────────────────┘

​
addMinutes

Introduced in: v1.1.0 Adds a specified number of minutes to a date, a date with time or a string-encoded date or date with time. Syntax 
addMinutes(datetime, num)
Arguments Returned value Returns datetime plus num minutes DateTime or DateTime64(3) Examples Add minutes to different date types
Query
WITH
    toDate('2024-01-01') AS date,
    toDateTime('2024-01-01 00:00:00') AS date_time,
    '2024-01-01 00:00:00' AS date_time_string
SELECT
    addMinutes(date, 20) AS add_minutes_with_date,
    addMinutes(date_time, 20) AS add_minutes_with_date_time,
    addMinutes(date_time_string, 20) AS add_minutes_with_date_time_string
Response
┌─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
Query
SELECT dateAdd('1998-06-16'::Date, INTERVAL 10 minute)
Response
┌─plus(CAST('1⋯Minute(10))─┐
│      1998-06-16 00:10:00 │
└──────────────────────────┘

​
addMonths

Introduced in: v1.1.0 Adds a specified number of months to a date, a date with time or a string-encoded date or date with time. Syntax 
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
Query
WITH
    toDate('2024-01-01') AS date,
    toDateTime('2024-01-01 00:00:00') AS date_time,
    '2024-01-01 00:00:00' AS date_time_string
SELECT
    addMonths(date, 6) AS add_months_with_date,
    addMonths(date_time, 6) AS add_months_with_date_time,
    addMonths(date_time_string, 6) AS add_months_with_date_time_string
Response
┌─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
Query
SELECT dateAdd('1998-06-16'::Date, INTERVAL 10 month)
Response
┌─plus(CAST('1⋯lMonth(10))─┐
│               1999-04-16 │
└──────────────────────────┘

​
addNanoseconds

Introduced in: v22.6.0 Adds a specified number of nanoseconds to a date with time or a string-encoded date with time. Syntax 
addNanoseconds(datetime, num)
Arguments Returned value Returns datetime plus num nanoseconds DateTime64 Examples Add nanoseconds to different date time types
Query
WITH
    toDateTime('2024-01-01 00:00:00') AS date_time,
    '2024-01-01 00:00:00' AS date_time_string
SELECT
    addNanoseconds(date_time, 1000) AS add_nanoseconds_with_date_time,
    addNanoseconds(date_time_string, 1000) AS add_nanoseconds_with_date_time_string
Response
┌─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
Query
SELECT dateAdd('1998-06-16'::DateTime, INTERVAL 1000 nanosecond)
Response
┌─plus(CAST('199⋯osecond(1000))─┐
│ 1998-06-16 00:00:00.000001000 │
└───────────────────────────────┘

​
addQuarters

Introduced in: v20.1.0 Adds a specified number of quarters to a date, a date with time or a string-encoded date or date with time. Syntax 
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
Query
WITH
    toDate('2024-01-01') AS date,
    toDateTime('2024-01-01 00:00:00') AS date_time,
    '2024-01-01 00:00:00' AS date_time_string
SELECT
    addQuarters(date, 1) AS add_quarters_with_date,
    addQuarters(date_time, 1) AS add_quarters_with_date_time,
    addQuarters(date_time_string, 1) AS add_quarters_with_date_time_string
Response
┌─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
Query
SELECT dateAdd('1998-06-16'::Date, INTERVAL 10 quarter)
Response
┌─plus(CAST('1⋯uarter(10))─┐
│               2000-12-16 │
└──────────────────────────┘

​
addSeconds

Introduced in: v1.1.0 Adds a specified number of seconds to a date, a date with time or a string-encoded date or date with time. Syntax 
addSeconds(datetime, num)
Arguments Returned value Returns datetime plus num seconds DateTime or DateTime64(3) Examples Add seconds to different date types
Query
WITH
    toDate('2024-01-01') AS date,
    toDateTime('2024-01-01 00:00:00') AS date_time,
    '2024-01-01 00:00:00' AS date_time_string
SELECT
    addSeconds(date, 30) AS add_seconds_with_date,
    addSeconds(date_time, 30) AS add_seconds_with_date_time,
    addSeconds(date_time_string, 30) AS add_seconds_with_date_time_string
Response
┌─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
Query
SELECT dateAdd('1998-06-16'::Date, INTERVAL 10 second)
Response
┌─dateAdd('1998-06-16'::Date, INTERVAL 10 second)─┐
│                             1998-06-16 00:00:10 │
└─────────────────────────────────────────────────┘

​
addTupleOfIntervals

Introduced in: v22.11.0 Consecutively adds a tuple of intervals to a date or a date with time. Syntax 
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
Query
WITH toDate('2018-01-01') AS date
SELECT addTupleOfIntervals(date, (INTERVAL 1 DAY, INTERVAL 1 MONTH, INTERVAL 1 YEAR))
Response
┌─addTupleOfIntervals(date, (toIntervalDay(1), toIntervalMonth(1), toIntervalYear(1)))─┐
│                                                                           2019-02-02 │
└──────────────────────────────────────────────────────────────────────────────────────┘

​
addWeeks

Introduced in: v1.1.0 Adds a specified number of weeks to a date, a date with time or a string-encoded date or date with time. Syntax 
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
Query
WITH
    toDate('2024-01-01') AS date,
    toDateTime('2024-01-01 00:00:00') AS date_time,
    '2024-01-01 00:00:00' AS date_time_string
SELECT
    addWeeks(date, 5) AS add_weeks_with_date,
    addWeeks(date_time, 5) AS add_weeks_with_date_time,
    addWeeks(date_time_string, 5) AS add_weeks_with_date_time_string
Response
┌─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
Query
SELECT dateAdd('1998-06-16'::Date, INTERVAL 10 week)
Response
┌─plus(CAST('1⋯alWeek(10))─┐
│               1998-08-25 │
└──────────────────────────┘

​
addYears

Introduced in: v1.1.0 Adds a specified number of years to a date, a date with time or a string-encoded date or date with time. Syntax 
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
Query
WITH
    toDate('2024-01-01') AS date,
    toDateTime('2024-01-01 00:00:00') AS date_time,
    '2024-01-01 00:00:00' AS date_time_string
SELECT
    addYears(date, 1) AS add_years_with_date,
    addYears(date_time, 1) AS add_years_with_date_time,
    addYears(date_time_string, 1) AS add_years_with_date_time_string
Response
┌─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
Query
SELECT dateAdd('1998-06-16'::Date, INTERVAL 10 year)
Response
┌─plus(CAST('1⋯alYear(10))─┐
│               2008-06-16 │
└──────────────────────────┘

​
age

Introduced in: v23.1.0 Returns the unit component of the difference between startdate and enddate. The difference is calculated using a precision of 1 nanosecond. For example, the difference between 2021-12-29 and 2022-01-01 is 3 days for the day unit, 0 months for the month unit, and 0 years for the year unit. For an alternative to age, see function dateDiff. 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
Query
SELECT age('hour', toDateTime('2018-01-01 22:30:00'), toDateTime('2018-01-02 23:00:00'))
Response
┌─age('hour', toDateTime('2018-01-01 22:30:00'), toDateTime('2018-01-02 23:00:00'))─┐
│                                                                                24 │
└───────────────────────────────────────────────────────────────────────────────────┘
Calculate age in different units
Query
SELECT
    toDate('2022-01-01') AS e,
    toDate('2021-12-29') AS s,
    age('day', s, e) AS day_age,
    age('month', s, e) AS month_age,
    age('year', s, e) AS year_age
Response
┌──────────e─┬──────────s─┬─day_age─┬─month_age─┬─year_age─┐
│ 2022-01-01 │ 2021-12-29 │       3 │         0 │        0 │
└────────────┴────────────┴─────────┴───────────┴──────────┘

​
changeDay

Introduced in: v24.7.0 Changes the day component of a date or date time. Syntax 
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
Query
SELECT changeDay('2024-01-31'::DateTime, 15)
Response
2024-01-15 00:00:00

​
changeHour

Introduced in: v24.7.0 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
Query
SELECT changeHour('2024-01-01 12:00:00'::DateTime, 5)
Response
2024-01-01 05:00:00

​
changeMinute

Introduced in: v24.7.0 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
Query
SELECT changeMinute('2024-01-01 12:30:00'::DateTime, 45)
Response
2024-01-01 12:45:00

​
changeMonth

Introduced in: v24.7.0 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
Query
SELECT changeMonth('2024-01-01'::DateTime, 12)
Response
2024-12-01 00:00:00

​
changeSecond

Introduced in: v24.7.0 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
Query
SELECT changeSecond('2024-01-01 12:30:45'::DateTime, 15)
Response
2024-01-01 12:30:15

​
changeYear

Introduced in: v24.7.0 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
Query
SELECT changeYear('2024-01-01'::DateTime, 2023)
Response
2023-01-01 00:00:00

​
dateDiff

Introduced in: v23.4.0 Returns the count of the specified unit boundaries crossed between the startdate and the enddate. The difference is calculated using relative units. For example, the difference between 2021-12-29 and 2022-01-01 is 3 days for unit day (see toRelativeDayNum), 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])
Aliases: timestampDiff, DATE_DIFF, date_diff, TIMESTAMP_DIFF, timestamp_diff 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
Query
SELECT dateDiff('hour', toDateTime('2018-01-01 22:00:00'), toDateTime('2018-01-02 23:00:00')) AS res
Response
┌─res─┐
│  25 │
└─────┘
Calculate date difference in different units
Query
SELECT
    toDate('2022-01-01') AS e,
    toDate('2021-12-29') AS s,
    dateDiff('day', s, e) AS day_diff,
    dateDiff('month', s, e) AS month_diff,
    dateDiff('year', s, e) AS year_diff
Response
┌──────────e─┬──────────s─┬─day_diff─┬─month_diff─┬─year_diff─┐
│ 2022-01-01 │ 2021-12-29 │        3 │          1 │         1 │
└────────────┴────────────┴──────────┴────────────┴───────────┘

​
dateName

Introduced in: v21.7.0 Returns the specified part of the date. Possible values:
  • ‘year’
  • ‘quarter’
  • ‘month’
  • ‘week’
  • ‘dayofyear’
  • ‘day’
  • ‘weekday’
  • ‘hour’
  • ‘minute’
  • ‘second’
Syntax 
dateName(date_part, date[, timezone])
Arguments Returned value Returns the specified part of date. String Examples Extract different date parts
Query
WITH toDateTime('2021-04-14 11:22:33') AS date_value
SELECT
    dateName('year', date_value),
    dateName('month', date_value),
    dateName('day', date_value)
Response
┌─dateName('year', date_value)─┬─dateName('month', date_value)─┬─dateName('day', date_value)─┐
│ 2021                         │ April                         │ 14                          │
└──────────────────────────────┴───────────────────────────────┴─────────────────────────────┘

​
dateTrunc

Introduced in: v20.8.0 Truncates a date and time value to the specified part of the date. Syntax 
dateTrunc(unit, datetime[, timezone])
Aliases: DATE_TRUNC Arguments
  • unit — The type of interval to truncate the result. Possible values: nanosecond (only DateTime64), microsecond (only DateTime64), millisecond (only DateTime64), second, minute, hour, day, week, month, quarter, year. String
  • 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
Query
SELECT now(), dateTrunc('hour', now());
Response
┌───────────────now()─┬─dateTrunc('hour', now())──┐
│ 2020-09-28 10:40:45 │       2020-09-28 10:00:00 │
└─────────────────────┴───────────────────────────┘
Truncate with specified timezone
Query
SELECT now(), dateTrunc('hour', now(), 'Asia/Istanbul');
Response
┌───────────────now()─┬─dateTrunc('hour', now(), 'Asia/Istanbul')──┐
│ 2020-09-28 10:46:26 │                        2020-09-28 13:00:00 │
└─────────────────────┴────────────────────────────────────────────┘

​
formatDateTime

Introduced in: v1.1.0 Formats a date or date with time according to the given format string. format is a constant expression, so you cannot have multiple formats for a single result column. formatDateTime uses MySQL datetime format style, refer to the mysql docs. 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 ClickHouse versions earlier than v23.4, %f prints a single zero (0) if the formatted value is a Date, Date32 or DateTime (which have no fractional seconds) or a DateTime64 with a precision of 0.
  • In ClickHouse versions earlier than v25.1, %f prints as many digits as specified by the scale of the DateTime64 instead of fixed 6 digits.
  • In ClickHouse versions earlier than v23.4, %M prints the minute (00-59) instead of the full month name (January-December).
Syntax 
formatDateTime(datetime, format[, timezone])
Aliases: DATE_FORMAT 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
Query
SELECT formatDateTime(toDate('2010-01-04'), '%g')
Response
┌─formatDateTime(toDate('2010-01-04'), '%g')─┐
│ 10                                         │
└────────────────────────────────────────────┘
Format DateTime64 with fractional seconds
Query
SELECT formatDateTime(toDateTime64('2010-01-04 12:34:56.123456', 7), '%f')
Response
┌─formatDateTime(toDateTime64('2010-01-04 12:34:56.123456', 7), '%f')─┐
│ 1234560                                                             │
└─────────────────────────────────────────────────────────────────────┘
Format with timezone
Query
SELECT
    now() AS ts,
    time_zone,
    formatDateTime(ts, '%T', time_zone) AS str_tz_time
FROM system.time_zones
WHERE time_zone LIKE 'Europe%'
LIMIT 10
Response
┌──────────────────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    │
└─────────────────────┴───────────────────┴─────────────┘

​
formatDateTimeInJodaSyntax

Introduced in: v20.1.0 Similar to formatDateTime, except that it formats datetime in Joda style instead of MySQL style. Refer to Joda Time documentation. 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
Query
SELECT formatDateTimeInJodaSyntax(toDateTime('2010-01-04 12:34:56'), 'yyyy-MM-dd HH:mm:ss')
Response
┌─formatDateTimeInJodaSyntax(toDateTime('2010-01-04 12:34:56'), 'yyyy-MM-dd HH:mm:ss')─┐
│ 2010-01-04 12:34:56                                                                     │
└─────────────────────────────────────────────────────────────────────────────────────────┘

​
fromDaysSinceYearZero

Introduced in: v23.11.0 For a given number of days elapsed since 1 January 0000, 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)
Aliases: FROM_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
Query
SELECT
fromDaysSinceYearZero(739136) AS date1,
fromDaysSinceYearZero(toDaysSinceYearZero(toDate('2023-09-08'))) AS date2
Response
┌──────date1─┬──────date2─┐
│ 2023-09-08 │ 2023-09-08 │
└────────────┴────────────┘

​
fromDaysSinceYearZero32

Introduced in: v23.11.0 For a given number of days elapsed since 1 January 0000, 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
Query
SELECT
fromDaysSinceYearZero32(739136) AS date1,
fromDaysSinceYearZero32(toDaysSinceYearZero(toDate('2023-09-08'))) AS date2
Response
┌──────date1─┬──────date2─┐
│ 2023-09-08 │ 2023-09-08 │
└────────────┴────────────┘

​
fromModifiedJulianDay

Introduced in: v21.1.0 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
Query
SELECT fromModifiedJulianDay(58849)
Response
┌─fromModifiedJulianDay(58849)─┐
│ 2020-01-01                   │
└──────────────────────────────┘

​
fromModifiedJulianDayOrNull

Introduced in: v21.1.0 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
Query
SELECT fromModifiedJulianDayOrNull(58849);
SELECT fromModifiedJulianDayOrNull(60000000); -- invalid argument, returns NULL
Response
┌─fromModified⋯Null(58849)─┐
│ 2020-01-01               │
└──────────────────────────┘
┌─fromModified⋯l(60000000)─┐
│ ᴺᵁᴸᴸ                     │
└──────────────────────────┘

​
fromUTCTimestamp

Introduced in: v22.1.0 Converts a date or date with time value from UTC timezone to a date or date with time value with the specified time zone. This function is mainly included for compatibility with Apache Spark and similar frameworks. Syntax 
fromUTCTimestamp(datetime, time_zone)
Aliases: from_utc_timestamp 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
Query
SELECT fromUTCTimestamp(toDateTime64('2023-03-16 10:00:00', 3), 'Asia/Shanghai')
Response
┌─fromUTCTimestamp(toDateTime64('2023-03-16 10:00:00',3), 'Asia/Shanghai')─┐
│                                                 2023-03-16 18:00:00.000 │
└─────────────────────────────────────────────────────────────────────────┘

​
fromUnixTimestamp

Introduced in: v20.8.0 This function converts a Unix timestamp to a calendar date and a time of a day. It can be called in two ways: Syntax 
fromUnixTimestamp(timestamp)
fromUnixTimestamp(timestamp[, format[, timezone]])
Aliases: FROM_UNIXTIME 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
Query
SELECT fromUnixTimestamp(423543535)
Response
┌─fromUnixTimestamp(423543535)─┐
│          1983-06-04 10:58:55 │
└──────────────────────────────┘
Convert Unix timestamp with format
Query
SELECT fromUnixTimestamp(1234334543, '%Y-%m-%d %R:%S') AS DateTime
Response
┌─DateTime────────────┐
│ 2009-02-11 14:42:23 │
└─────────────────────┘

​
fromUnixTimestampInJodaSyntax

Introduced in: v23.1.0 This function converts a Unix timestamp to a calendar date and a time of a day. It can be called in two ways: When given a single argument of type Integer, 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
Query
SELECT fromUnixTimestampInJodaSyntax(1234334543, 'yyyy-MM-dd HH:mm:ss', 'UTC') AS DateTime
Response
┌─DateTime────────────┐
│ 2009-02-11 06:42:23 │
└─────────────────────┘

​
makeDate

Introduced in: v22.6.0 Creates a Date from either:
  • a year, month and day
  • a year and day of year
Syntax 
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
Query
SELECT makeDate(2023, 2, 28) AS date;
Response
┌───────date─┐
│ 2023-02-28 │
└────────────┘
Date from year and day of year
Query
SELECT makeDate(2023, 42) AS date;
Response
┌───────date─┐
│ 2023-02-11 │
└────────────┘

​
makeDate32

Introduced in: v22.6.0 Creates a Date32 from either:
  • a year, month and day
  • a year and day of year
Syntax 
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
Query
SELECT makeDate(2023, 2, 28) AS date;
Response
┌───────date─┐
│ 2023-02-28 │
└────────────┘
Date32 from year and day of year
Query
SELECT makeDate(2023, 42) AS date;
Response
┌───────date─┐
│ 2023-02-11 │
└────────────┘

​
makeDateTime

Introduced in: v22.6.0 Creates a DateTime from year, month, day, hour, minute, and second, with optional timezone. Syntax 
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
Query
SELECT makeDateTime(2023, 2, 28, 17, 12, 33) AS DateTime;
Response
┌────────────DateTime─┐
│ 2023-02-28 17:12:33 │
└─────────────────────┘

​
makeDateTime64

Introduced in: v22.6.0 Creates a DateTime64 from year, month, day, hour, minute, second, with optional fraction, precision, and timezone. Syntax 
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
Query
SELECT makeDateTime64(2023, 5, 15, 10, 30, 45, 779, 5);
Response
┌─makeDateTime64(2023, 5, 15, 10, 30, 45, 779, 5)─┐
│                       2023-05-15 10:30:45.00779 │
└─────────────────────────────────────────────────┘

​
monthName

Introduced in: v22.1.0 Returns the name of the month as a string from a date or date with time value. Syntax 
monthName(datetime)
Arguments Returned value Returns the name of the month. String Examples Get month name from date
Query
WITH toDateTime('2021-04-14 11:22:33') AS date_value
SELECT monthName(date_value)
Response
┌─monthName(date_value)─┐
│ April                 │
└───────────────────────┘

​
now

Introduced in: v1.1.0 Returns the current date and time at the moment of query analysis. The function is a constant expression. Syntax 
now([timezone])
Aliases: current_timestamp Arguments
  • timezone — Optional. Timezone name for the returned value. String
Returned value Returns the current date and time. DateTime Examples Query without timezone
Query
SELECT now()
Response
┌───────────────now()─┐
│ 2020-10-17 07:42:09 │
└─────────────────────┘
Query with specified timezone
Query
SELECT now('Asia/Istanbul')
Response
┌─now('Asia/Istanbul')─┐
│  2020-10-17 10:42:23 │
└──────────────────────┘
SQL standard syntax without parentheses
Query
SELECT NOW, CURRENT_TIMESTAMP
Response
┌─────────────────NOW─┬───CURRENT_TIMESTAMP─┐
│ 2020-10-17 07:42:19 │ 2020-10-17 07:42:19 │
└─────────────────────┴─────────────────────┘

​
now64

Introduced in: v20.1.0 Returns the current date and time with sub-second precision at the moment of query analysis. The function is a constant expression. Syntax 
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
Query
SELECT now64(), now64(9, 'Asia/Istanbul')
Response
┌─────────────────now64()─┬─────now64(9, 'Asia/Istanbul')─┐
│ 2022-08-21 19:34:26.196 │ 2022-08-21 22:34:26.196542766 │
└─────────────────────────┴───────────────────────────────┘

​
nowInBlock

Introduced in: v22.8.0 Returns the current date and time at the moment of processing of each block of data. In contrast to the function now, 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
Query
SELECT
    now(),
    nowInBlock(),
    sleep(1)
FROM numbers(3)
SETTINGS max_block_size = 1
FORMAT PrettyCompactMonoBlock
Response
┌───────────────now()─┬────────nowInBlock()─┬─sleep(1)─┐
│ 2022-08-21 19:41:19 │ 2022-08-21 19:41:19 │        0 │
│ 2022-08-21 19:41:19 │ 2022-08-21 19:41:20 │        0 │
│ 2022-08-21 19:41:19 │ 2022-08-21 19:41:21 │        0 │
└─────────────────────┴─────────────────────┴──────────┘

​
nowInBlock64

Introduced in: v25.8.0 Returns the current date and time at the moment of processing of each block of data in milliseconds. In contrast to the function now64, 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 
nowInBlock64([scale[, timezone]])
Arguments
  • scale — Optional. Tick size (precision): 10^-precision seconds. Valid range: [0 : 9]. Typically, are used - 3 (default) (milliseconds), 6 (microseconds), 9 (nanoseconds). UInt8
  • 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
Query
SELECT
    now64(),
    nowInBlock64(),
    sleep(1)
FROM numbers(3)
SETTINGS max_block_size = 1
FORMAT PrettyCompactMonoBlock
Response
┌─────────────────now64()─┬──────────nowInBlock64()─┬─sleep(1)─┐
│ 2025-07-29 17:07:29.526 │ 2025-07-29 17:07:29.534 │        0 │
│ 2025-07-29 17:07:29.526 │ 2025-07-29 17:07:30.535 │        0 │
│ 2025-07-29 17:07:29.526 │ 2025-07-29 17:07:31.535 │        0 │
└─────────────────────────┴─────────────────────────┴──────────┘

​
serverTimezone

Introduced in: v23.6.0 Returns the timezone of the server, i.e. the value of the timezone 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()
Aliases: serverTimeZone Arguments
  • None.
Returned value Returns the server timezone as a String Examples Usage example
Query
SELECT serverTimeZone()
Response
┌─serverTimeZone()─┐
│ UTC              │
└──────────────────┘

​
subDate

Introduced in: v23.9.0 Subtracts the time interval from the provided date, date with time or string-encoded date or date with time. If the subtraction results in a value outside the bounds of the data type, the result is undefined. Syntax 
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
Query
SELECT subDate(toDate('2018-01-01'), INTERVAL 3 YEAR)
Response
┌─subDate(toDate('2018-01-01'), toIntervalYear(3))─┐
│                                       2015-01-01 │
└──────────────────────────────────────────────────┘

​
subtractDays

Introduced in: v1.1.0 Subtracts a specified number of days from a date, a date with time or a string-encoded date or date with time. Syntax 
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
Query
WITH
    toDate('2024-01-01') AS date,
    toDateTime('2024-01-01 00:00:00') AS date_time,
    '2024-01-01 00:00:00' AS date_time_string
SELECT
    subtractDays(date, 31) AS subtract_days_with_date,
    subtractDays(date_time, 31) AS subtract_days_with_date_time,
    subtractDays(date_time_string, 31) AS subtract_days_with_date_time_string
Response
┌─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
Query
SELECT dateSub('1998-06-16'::Date, INTERVAL 10 day)
Response
┌─minus(CAST('⋯valDay(10))─┐
│               1998-06-06 │
└──────────────────────────┘

​
subtractHours

Introduced in: v1.1.0 Subtracts a specified number of hours from a date, a date with time or a string-encoded date or date with time. Syntax 
subtractHours(datetime, num)
Arguments Returned value Returns datetime minus num hours DateTime or DateTime64(3) Examples Subtract hours from different date types
Query
WITH
    toDate('2024-01-01') AS date,
    toDateTime('2024-01-01 00:00:00') AS date_time,
    '2024-01-01 00:00:00' AS date_time_string
SELECT
    subtractHours(date, 12) AS subtract_hours_with_date,
    subtractHours(date_time, 12) AS subtract_hours_with_date_time,
    subtractHours(date_time_string, 12) AS subtract_hours_with_date_time_string
Response
┌─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
Query
SELECT dateSub('1998-06-16'::Date, INTERVAL 10 hour)
Response
┌─minus(CAST('⋯alHour(10))─┐
│      1998-06-15 14:00:00 │
└──────────────────────────┘

​
subtractInterval

Introduced in: v22.11.0 Adds a negated interval to another interval or tuple of intervals. Note: Intervals of the same type will be combined into a single interval. For instance if toIntervalDay(2) and toIntervalDay(1) are passed then the result will be (1) rather than (2,1). Syntax 
subtractInterval(interval_1, interval_2)
Arguments Returned value Returns a tuple of intervals Tuple(T) Examples Subtract intervals
Query
SELECT subtractInterval(INTERVAL 1 DAY, INTERVAL 1 MONTH);
SELECT subtractInterval((INTERVAL 1 DAY, INTERVAL 1 YEAR), INTERVAL 1 MONTH);
SELECT subtractInterval(INTERVAL 2 DAY, INTERVAL 1 DAY);
Response
┌─subtractInterval(toIntervalDay(1), toIntervalMonth(1))─┐
│ (1,-1)                                                 │
└────────────────────────────────────────────────────────┘
┌─subtractInterval((toIntervalDay(1), toIntervalYear(1)), toIntervalMonth(1))─┐
│ (1,1,-1)                                                                    │
└─────────────────────────────────────────────────────────────────────────────┘
┌─subtractInterval(toIntervalDay(2), toIntervalDay(1))─┐
│ (1)                                                  │
└──────────────────────────────────────────────────────┘

​
subtractMicroseconds

Introduced in: v22.6.0 Subtracts a specified number of microseconds from a date with time or a string-encoded date with time. Syntax 
subtractMicroseconds(datetime, num)
Arguments Returned value Returns datetime minus num microseconds DateTime64 Examples Subtract microseconds from different date time types
Query
WITH
    toDateTime('2024-01-01 00:00:00') AS date_time,
    '2024-01-01 00:00:00' AS date_time_string
SELECT
    subtractMicroseconds(date_time, 1000000) AS subtract_microseconds_with_date_time,
    subtractMicroseconds(date_time_string, 1000000) AS subtract_microseconds_with_date_time_string
Response
┌─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
Query
SELECT dateSub('1998-06-16'::DateTime, INTERVAL 10 microsecond)
Response
┌─minus(CAST('1⋯osecond(10))─┐
│ 1998-06-15 23:59:59.999990 │
└────────────────────────────┘

​
subtractMilliseconds

Introduced in: v22.6.0 Subtracts a specified number of milliseconds from a date with time or a string-encoded date with time. Syntax 
subtractMilliseconds(datetime, num)
Arguments Returned value Returns datetime minus num milliseconds DateTime64 Examples Subtract milliseconds from different date time types
Query
WITH
    toDateTime('2024-01-01 00:00:00') AS date_time,
    '2024-01-01 00:00:00' AS date_time_string
SELECT
    subtractMilliseconds(date_time, 1000) AS subtract_milliseconds_with_date_time,
    subtractMilliseconds(date_time_string, 1000) AS subtract_milliseconds_with_date_time_string
Response
┌─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
Query
SELECT dateSub('1998-06-16'::DateTime, INTERVAL 10 millisecond)
Response
┌─minus(CAST('⋯second(10))─┐
│  1998-06-15 23:59:59.990 │
└──────────────────────────┘

​
subtractMinutes

Introduced in: v1.1.0 Subtracts a specified number of minutes from a date, a date with time or a string-encoded date or date with time. Syntax 
subtractMinutes(datetime, num)
Arguments Returned value Returns datetime minus num minutes DateTime or DateTime64(3) Examples Subtract minutes from different date types
Query
WITH
    toDate('2024-01-01') AS date,
    toDateTime('2024-01-01 00:00:00') AS date_time,
    '2024-01-01 00:00:00' AS date_time_string
SELECT
    subtractMinutes(date, 30) AS subtract_minutes_with_date,
    subtractMinutes(date_time, 30) AS subtract_minutes_with_date_time,
    subtractMinutes(date_time_string, 30) AS subtract_minutes_with_date_time_string
Response
┌─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
Query
SELECT dateSub('1998-06-16'::Date, INTERVAL 10 minute)
Response
┌─minus(CAST('⋯Minute(10))─┐
│      1998-06-15 23:50:00 │
└──────────────────────────┘

​
subtractMonths

Introduced in: v1.1.0 Subtracts a specified number of months from a date, a date with time or a string-encoded date or date with time. Syntax 
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
Query
WITH
    toDate('2024-01-01') AS date,
    toDateTime('2024-01-01 00:00:00') AS date_time,
    '2024-01-01 00:00:00' AS date_time_string
SELECT
    subtractMonths(date, 1) AS subtract_months_with_date,
    subtractMonths(date_time, 1) AS subtract_months_with_date_time,
    subtractMonths(date_time_string, 1) AS subtract_months_with_date_time_string
Response
┌─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
Query
SELECT dateSub('1998-06-16'::Date, INTERVAL 10 month)
Response
┌─minus(CAST('⋯lMonth(10))─┐
│               1997-08-16 │
└──────────────────────────┘

​
subtractNanoseconds

Introduced in: v20.1.0 Subtracts a specified number of nanoseconds from a date with time or a string-encoded date with time. Syntax 
subtractNanoseconds(datetime, num)
Arguments Returned value Returns datetime minus num nanoseconds DateTime64 Examples Subtract nanoseconds from different date time types
Query
WITH
    toDateTime('2024-01-01 00:00:00') AS date_time,
    '2024-01-01 00:00:00' AS date_time_string
SELECT
    subtractNanoseconds(date_time, 1000) AS subtract_nanoseconds_with_date_time,
    subtractNanoseconds(date_time_string, 1000) AS subtract_nanoseconds_with_date_time_string
Response
┌─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
Query
SELECT dateSub('1998-06-16'::DateTime, INTERVAL 10 nanosecond)
Response
┌─minus(CAST('19⋯anosecond(10))─┐
│ 1998-06-15 23:59:59.999999990 │
└───────────────────────────────┘

​
subtractQuarters

Introduced in: v20.1.0 Subtracts a specified number of quarters from a date, a date with time or a string-encoded date or date with time. Syntax 
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
Query
WITH
    toDate('2024-01-01') AS date,
    toDateTime('2024-01-01 00:00:00') AS date_time,
    '2024-01-01 00:00:00' AS date_time_string
SELECT
    subtractQuarters(date, 1) AS subtract_quarters_with_date,
    subtractQuarters(date_time, 1) AS subtract_quarters_with_date_time,
    subtractQuarters(date_time_string, 1) AS subtract_quarters_with_date_time_string
Response
┌─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
Query
SELECT dateSub('1998-06-16'::Date, INTERVAL 10 quarter)
Response
┌─minus(CAST('1⋯Quarter(10))─┐
│                1996-09-16 │
└───────────────────────────┘

​
subtractSeconds

Introduced in: v1.1.0 Subtracts a specified number of seconds from a date, a date with time or a string-encoded date or date with time. Syntax 
subtractSeconds(datetime, num)
Arguments Returned value Returns datetime minus num seconds DateTime or DateTime64(3) Examples Subtract seconds from different date types
Query
WITH
    toDate('2024-01-01') AS date,
    toDateTime('2024-01-01 00:00:00') AS date_time,
    '2024-01-01 00:00:00' AS date_time_string
SELECT
    subtractSeconds(date, 60) AS subtract_seconds_with_date,
    subtractSeconds(date_time, 60) AS subtract_seconds_with_date_time,
    subtractSeconds(date_time_string, 60) AS subtract_seconds_with_date_time_string
Response
┌─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
Query
SELECT dateSub('1998-06-16'::Date, INTERVAL 10 second)
Response
┌─minus(CAST('⋯Second(10))─┐
│      1998-06-15 23:59:50 │
└──────────────────────────┘

​
subtractTupleOfIntervals

Introduced in: v22.11.0 Consecutively subtracts a tuple of intervals from a date or a date with time. Syntax 
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
Query
WITH toDate('2018-01-01') AS date SELECT subtractTupleOfIntervals(date, (INTERVAL 1 DAY, INTERVAL 1 YEAR))
Response
┌─subtractTupl⋯alYear(1)))─┐
│               2016-12-31 │
└──────────────────────────┘

​
subtractWeeks

Introduced in: v1.1.0 Subtracts a specified number of weeks from a date, a date with time or a string-encoded date or date with time. Syntax 
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
Query
WITH
    toDate('2024-01-01') AS date,
    toDateTime('2024-01-01 00:00:00') AS date_time,
    '2024-01-01 00:00:00' AS date_time_string
SELECT
    subtractWeeks(date, 1) AS subtract_weeks_with_date,
    subtractWeeks(date_time, 1) AS subtract_weeks_with_date_time,
    subtractWeeks(date_time_string, 1) AS subtract_weeks_with_date_time_string
Response
┌─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
Query
SELECT dateSub('1998-06-16'::Date, INTERVAL 10 week)
Response
┌─minus(CAST('⋯alWeek(10))─┐
│               1998-04-07 │
└──────────────────────────┘

​
subtractYears

Introduced in: v1.1.0 Subtracts a specified number of years from a date, a date with time or a string-encoded date or date with time. Syntax 
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
Query
WITH
    toDate('2024-01-01') AS date,
    toDateTime('2024-01-01 00:00:00') AS date_time,
    '2024-01-01 00:00:00' AS date_time_string
SELECT
    subtractYears(date, 1) AS subtract_years_with_date,
    subtractYears(date_time, 1) AS subtract_years_with_date_time,
    subtractYears(date_time_string, 1) AS subtract_years_with_date_time_string
Response
┌─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
Query
SELECT dateSub('1998-06-16'::Date, INTERVAL 10 year)
Response
┌─minus(CAST('⋯alYear(10))─┐
│               1988-06-16 │
└──────────────────────────┘

​
timeDiff

Introduced in: v23.4.0 Returns the difference between two dates or dates with time values in seconds. The difference is calculated as enddate - startdate. This function is equivalent to dateDiff('second', startdate, enddate). For calculating time differences in other units (hours, days, months, etc.), use the dateDiff 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
Query
SELECT timeDiff(toDateTime('2018-01-01 22:00:00'), toDateTime('2018-01-02 23:00:00')) AS res
Response
┌───res─┐
│ 90000 │
└───────┘
Calculate time difference and convert to hours
Query
SELECT timeDiff(toDateTime('2018-01-01 22:00:00'), toDateTime('2018-01-02 23:00:00')) / 3600 AS hours
Response
┌─hours─┐
│    25 │
└───────┘
Equivalent to dateDiff with seconds
Query
SELECT
    timeDiff(toDateTime('2021-12-29'), toDateTime('2022-01-01')) AS time_diff_result,
    dateDiff('second', toDateTime('2021-12-29'), toDateTime('2022-01-01')) AS date_diff_result
Response
┌─time_diff_result─┬─date_diff_result─┐
│           259200 │           259200 │
└──────────────────┴──────────────────┘

​
timeSlot

Introduced in: v1.1.0 Round the time to the start of a half-an-hour length interval.
Although this function can take values of the extended types Date32 and DateTime64 as an argument, passing it a time outside the normal range (year 1970 to 2149 for Date / 2106 for DateTime) will produce wrong results.
Syntax 
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
Query
SELECT timeSlot(toDateTime('2000-01-02 03:04:05', 'UTC'))
Response
┌─timeSlot(toDateTime('2000-01-02 03:04:05', 'UTC'))─┐
│                                2000-01-02 03:00:00 │
└────────────────────────────────────────────────────┘

​
timeSlots

Introduced in: v1.1.0 For a time interval starting at StartTime and continuing for Duration seconds, it returns an array of moments in time, consisting of points from this interval rounded down to the Size in seconds. Size is an optional parameter set to 1800 (30 minutes) by default. This is necessary, for example, when searching for pageviews in the corresponding session. For DateTime64, the return value’s scale can differ from the scale of StartTime. The highest scale among all given arguments is taken. Syntax 
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
Query
SELECT timeSlots(toDateTime('2012-01-01 12:20:00'), toUInt32(600));
SELECT timeSlots(toDateTime('1980-12-12 21:01:02', 'UTC'), toUInt32(600), 299);
SELECT timeSlots(toDateTime64('1980-12-12 21:01:02.1234', 4, 'UTC'), toDecimal64(600.1, 1), toDecimal64(299, 0))
Response
┌─timeSlots(toDateTime('2012-01-01 12:20:00'), toUInt32(600))─┐
│ ['2012-01-01 12:00:00','2012-01-01 12:30:00']               │
└─────────────────────────────────────────────────────────────┘
┌─timeSlots(toDateTime('1980-12-12 21:01:02', 'UTC'), toUInt32(600), 299)─┐
│ ['1980-12-12 20:56:13','1980-12-12 21:01:12','1980-12-12 21:06:11']     │
└─────────────────────────────────────────────────────────────────────────┘
┌─timeSlots(toDateTime64('1980-12-12 21:01:02.1234', 4, 'UTC'), toDecimal64(600.1, 1), toDecimal64(299, 0))─┐
│ ['1980-12-12 20:56:13.0000','1980-12-12 21:01:12.0000','1980-12-12 21:06:11.0000']                        │
└───────────────────────────────────────────────────────────────────────────────────────────────────────────┘

​
timestamp

Introduced in: v23.9.0 Converts the first argument expr to type DateTime64(6). 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)
Query
SELECT timestamp('2023-12-31') AS ts;
Response
┌─────────────────────────ts─┐
│ 2023-12-31 00:00:00.000000 │
└────────────────────────────┘
Add time to date string
Query
SELECT timestamp('2023-12-31 12:00:00', '12:00:00.11') AS ts;
Response
┌─────────────────────────ts─┐
│ 2024-01-01 00:00:00.110000 │
└────────────────────────────┘

​
timezone

Introduced in: v21.4.0 Returns the time zone name of the current session or converts a time zone offset or name to a canonical time zone name. Syntax 
timezone()
Aliases: timeZone Arguments
  • None.
Returned value Returns the canonical time zone name as a String Examples Usage example
Query
SELECT timezone()
Response
┌─timezone()───────┐
│ Europe/Amsterdam │
└──────────────────┘

​
timezoneOf

Introduced in: v21.4.0 Returns the timezone name of a DateTime or DateTime64 value. Syntax 
timezoneOf(datetime)
Aliases: timeZoneOf 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
Query
SELECT timezoneOf(now());
Response
┌─timezoneOf(now())─┐
│ Europe/Amsterdam  │
└───────────────────┘

​
timezoneOffset

Introduced in: v21.6.0 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)
Aliases: timeZoneOffset Arguments Returned value Returns the offset from UTC in seconds Int32 Examples Usage example
Query
SELECT toDateTime('2021-04-21 10:20:30', 'America/New_York') AS Time,
toTypeName(Time) AS Type,
timezoneOffset(Time) AS Offset_in_seconds,
(Offset_in_seconds / 3600) AS Offset_in_hours;
Response
┌────────────────Time─┬─Type─────────────────────────┬─Offset_in_seconds─┬─Offset_in_hours─┐
│ 2021-04-21 10:20:30 │ DateTime('America/New_York') │            -14400 │              -4 │
└─────────────────────┴──────────────────────────────┴───────────────────┴─────────────────┘

​
toDayOfMonth

Introduced in: v1.1.0 Returns the day of the month (1-31) of a Date or DateTime. Syntax 
toDayOfMonth(datetime)
Aliases: DAY, DAYOFMONTH Arguments Returned value Returns the day of the month of the given date/time UInt8 Examples Usage example
Query
SELECT toDayOfMonth(toDateTime('2023-04-21 10:20:30'))
Response
┌─toDayOfMonth(toDateTime('2023-04-21 10:20:30'))─┐
│                                              21 │
└─────────────────────────────────────────────────┘

​
toDayOfWeek

Introduced in: v1.1.0 Returns the number of the day within the week of a Date or DateTime value. The two-argument form of toDayOfWeek() enables you to specify whether the week starts on Monday or Sunday, and whether the return value should be in the range from 0 to 6 or 1 to 7.
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]])
Aliases: DAYOFWEEK 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
Query
-- The following date is April 21, 2023, which was a Friday:
SELECT
    toDayOfWeek(toDateTime('2023-04-21')),
    toDayOfWeek(toDateTime('2023-04-21'), 1)
Response
┌─toDayOfWeek(toDateTime('2023-04-21'))─┬─toDayOfWeek(toDateTime('2023-04-21'), 1)─┐
│                                     5 │                                        4 │
└───────────────────────────────────────┴──────────────────────────────────────────┘

​
toDayOfYear

Introduced in: v18.4.0 Returns the number of the day within the year (1-366) of a Date or DateTime value. Syntax 
toDayOfYear(datetime)
Aliases: DAYOFYEAR Arguments Returned value Returns the day of the year of the given Date or DateTime UInt16 Examples Usage example
Query
SELECT toDayOfYear(toDateTime('2023-04-21 10:20:30'))
Response
┌─toDayOfYear(toDateTime('2023-04-21 10:20:30'))─┐
│                                            111 │
└────────────────────────────────────────────────┘

​
toDaysInMonth

Introduced in: v26.3.0 Returns the number of days in the month of a Date or DateTime. The returned value is in the range 28 to 31. Syntax 
toDaysInMonth(datetime)
Arguments Returned value Returns the number of days in the month of the given date/time. UInt8 Examples Usage example
Query
SELECT toDaysInMonth(toDate('2023-02-01')), toDaysInMonth(toDate('2024-02-01')), toDaysInMonth(toDate('2023-01-01'))
Response
┌─toDaysInMonth(toDate('2023-02-01'))─┬─toDaysInMonth(toDate('2024-02-01'))─┬─toDaysInMonth(toDate('2023-01-01'))─┐
│                                  28 │                                  29 │                                  31 │
└─────────────────────────────────────┴─────────────────────────────────────┴─────────────────────────────────────┘

​
toDaysSinceYearZero

Introduced in: v23.9.0 For a given date, returns the number of days which have passed since 1 January 0000 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])
Aliases: TO_DAYS 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
Query
SELECT toDaysSinceYearZero(toDate('2023-09-08'))
Response
┌─toDaysSinceYearZero(toDate('2023-09-08')))─┐
│                                     713569 │
└────────────────────────────────────────────┘

​
toHour

Introduced in: v1.1.0 Returns the hour component (0-23) of a DateTime or DateTime64 value. Syntax 
toHour(datetime)
Aliases: HOUR Arguments Returned value Returns the hour (0-23) of datetime. UInt8 Examples Usage example
Query
SELECT toHour(toDateTime('2023-04-21 10:20:30'))
Response
┌─toHour(toDateTime('2023-04-21 10:20:30'))─┐
│                                        10 │
└───────────────────────────────────────────┘

​
toISOWeek

Introduced in: v20.1.0 Returns the ISO week number of a date or date with time. This is a compatibility function that is equivalent to toWeek(date, 3). ISO weeks start on Monday and the first week of the year contains January 4th. According to ISO 8601, week numbers are in the range from 1 to 53. Note that dates near the beginning or end of a year may return a week number from the previous or next year. For example, December 29, 2025 returns week 1 because it falls in the first week that contains January 4, 2026. Syntax 
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
Query
SELECT toDate('2016-12-27') AS date, toISOWeek(date) AS isoWeek
Response
┌───────date─┬─isoWeek─┐
│ 2016-12-27 │      52 │
└────────────┴─────────┘
ISO week can belong to different year
Query
SELECT toDate('2025-12-29') AS date, toISOWeek(date) AS isoWeek, toYear(date) AS year
Response
┌───────date─┬─isoWeek─┬─year─┐
│ 2025-12-29 │       1 │ 2025 │
└────────────┴─────────┴──────┘

​
toISOYear

Introduced in: v18.4.0 Converts a date or date with time to the ISO year number. Syntax 
toISOYear(datetime)
Arguments Returned value Returns the input value converted to an ISO year number. UInt16 Examples Get ISO year from date values
Query
SELECT
toISOYear(toDate('2024/10/02')) as year1,
toISOYear(toDateTime('2024-10-02 01:30:00')) as year2
Response
┌─week1─┬─week2─┐
│    40 │    40 │
└───────┴───────┘

​
toLastDayOfMonth

Introduced in: v1.1.0 Rounds up a date or date with time to the last day of the month.
The return type can be configured by setting enable_extended_results_for_datetime_functions.
Syntax 
toLastDayOfMonth(value)
Aliases: LAST_DAY 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
Query
SELECT toLastDayOfMonth(toDateTime('2023-04-21 10:20:30'))
Response
┌─toLastDayOfMonth(toDateTime('2023-04-21 10:20:30'))─┐
│                                          2023-04-30 │
└─────────────────────────────────────────────────────┘

​
toLastDayOfWeek

Introduced in: v23.5.0 Rounds a date or date with time up to the nearest Saturday or Sunday.
The return type can be configured by setting enable_extended_results_for_datetime_functions.
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 Examples Round up to the nearest Saturday or Sunday
Query
SELECT
    toLastDayOfWeek(toDateTime('2023-04-21 10:20:30')), /* a Friday */
    toLastDayOfWeek(toDateTime('2023-04-21 10:20:30'), 1), /* a Friday */
    toLastDayOfWeek(toDate('2023-04-23')), /* a Sunday */
    toLastDayOfWeek(toDate('2023-04-23'), 1) /* a Sunday */
FORMAT Vertical
Response
Row 1:
──────
toLastDayOfWeek(toDateTime('2023-04-21 10:20:30')):      2023-04-23
toLastDayOfWeek(toDateTime('2023-04-21 10:20:30'), 1):   2023-04-22
toLastDayOfWeek(toDate('2023-04-23')):                   2023-04-23
toLastDayOfWeek(toDate('2023-04-23'), 1):                2023-04-23

​
toMillisecond

Introduced in: v24.2.0 Returns the millisecond component (0-999) of a DateTime or DateTime64 value. Syntax 
toMillisecond(datetime)
Aliases: MILLISECOND Arguments Returned value Returns the millisecond in the minute (0 - 59) of datetime. UInt16 Examples Usage example
Query
SELECT toMillisecond(toDateTime64('2023-04-21 10:20:30.456', 3));
Response
┌──toMillisecond(toDateTime64('2023-04-21 10:20:30.456', 3))─┐
│                                                        456 │
└────────────────────────────────────────────────────────────┘

​
toMinute

Introduced in: v1.1.0 Returns the minute component (0-59) of a Date or DateTime value. Syntax 
toMinute(datetime)
Aliases: MINUTE Arguments Returned value Returns the minute of the hour (0 - 59) of datetime. UInt8 Examples Usage example
Query
SELECT toMinute(toDateTime('2023-04-21 10:20:30'))
Response
┌─toMinute(toDateTime('2023-04-21 10:20:30'))─┐
│                                          20 │
└─────────────────────────────────────────────┘

​
toModifiedJulianDay

Introduced in: v21.1.0 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
Query
SELECT toModifiedJulianDay('2020-01-01')
Response
┌─toModifiedJulianDay('2020-01-01')─┐
│                             58849 │
└───────────────────────────────────┘

​
toModifiedJulianDayOrNull

Introduced in: v21.1.0 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
Query
SELECT toModifiedJulianDayOrNull('2020-01-01');
SELECT toModifiedJulianDayOrNull('0000-00-00'); -- invalid date, returns NULL
Response
┌─toModifiedJu⋯020-01-01')─┐
│                    58849 │
└──────────────────────────┘
┌─toModifiedJu⋯000-00-00')─┐
│                     ᴺᵁᴸᴸ │
└──────────────────────────┘

​
toMonday

Introduced in: v1.1.0 Rounds down a date or date with time to the Monday of the same week. Returns the date.
The return type can be configured by setting enable_extended_results_for_datetime_functions.
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
Query
SELECT
toMonday(toDateTime('2023-04-21 10:20:30')), -- A Friday
toMonday(toDate('2023-04-24'));              -- Already a Monday
Response
┌─toMonday(toDateTime('2023-04-21 10:20:30'))─┬─toMonday(toDate('2023-04-24'))─┐
│                                  2023-04-17 │                     2023-04-24 │
└─────────────────────────────────────────────┴────────────────────────────────┘

​
toMonth

Introduced in: v1.1.0 Returns the month component (1-12) of a Date or DateTime value. Syntax 
toMonth(datetime)
Aliases: MONTH Arguments Returned value Returns the month of the given date/time UInt8 Examples Usage example
Query
SELECT toMonth(toDateTime('2023-04-21 10:20:30'))
Response
┌─toMonth(toDateTime('2023-04-21 10:20:30'))─┐
│                                          4 │
└────────────────────────────────────────────┘

​
toMonthNumSinceEpoch

Introduced in: v25.3.0 Returns amount of months passed from year 1970 Syntax 
toMonthNumSinceEpoch(date)
Arguments Returned value Positive integer Examples Example
Query
SELECT toMonthNumSinceEpoch(toDate('2024-10-01'))
Response
657

​
toQuarter

Introduced in: v1.1.0 Returns the quarter of the year (1-4) for a given Date or DateTime value. Syntax 
toQuarter(datetime)
Aliases: QUARTER Arguments Returned value Returns the quarter of the year for the given date/time UInt8 Examples Usage example
Query
SELECT toQuarter(toDateTime('2023-04-21 10:20:30'))
Response
┌─toQuarter(toDateTime('2023-04-21 10:20:30'))─┐
│                                            2 │
└──────────────────────────────────────────────┘

​
toRelativeDayNum

Introduced in: v1.1.0 Converts a date or date with time to the number of days elapsed since a certain fixed point in the past. The exact point in time is an implementation detail, and therefore this function is not intended to be used standalone. The main purpose of the function is to calculate the difference in days between two dates or dates with time, e.g., toRelativeDayNum(dt1) - toRelativeDayNum(dt2). Syntax 
toRelativeDayNum(date)
Arguments Returned value Returns the number of days from a fixed reference point in the past. UInt32 Examples Get relative day numbers
Query
SELECT toRelativeDayNum(toDate('2023-04-01')) - toRelativeDayNum(toDate('2023-01-01'))
Response
┌─minus(toRela⋯3-01-01')))─┐
│                       90 │
└──────────────────────────┘

​
toRelativeHourNum

Introduced in: v1.1.0 Converts a date or date with time to the number of hours elapsed since a certain fixed point in the past. The exact point in time is an implementation detail, and therefore this function is not intended to be used standalone. The main purpose of the function is to calculate the difference in hours between two dates or dates with time, e.g., toRelativeHourNum(dt1) - toRelativeHourNum(dt2). Syntax 
toRelativeHourNum(date)
Arguments Returned value Returns the number of hours from a fixed reference point in the past. UInt32 Examples Get relative hour numbers
Query
SELECT toRelativeHourNum(toDateTime('2023-01-01 12:00:00')) - toRelativeHourNum(toDateTime('2023-01-01 00:00:00')) AS hours_difference
Response
┌─hours_difference─┐
│               12 │
└──────────────────┘

​
toRelativeMinuteNum

Introduced in: v1.1.0 Converts a date or date with time to the number of minutes elapsed since a certain fixed point in the past. The exact point in time is an implementation detail, and therefore this function is not intended to be used standalone. The main purpose of the function is to calculate the difference in minutes between two dates or dates with time, e.g., toRelativeMinuteNum(dt1) - toRelativeMinuteNum(dt2). Syntax 
toRelativeMinuteNum(date)
Arguments Returned value Returns the number of minutes from a fixed reference point in the past. UInt32 Examples Get relative minute numbers
Query
SELECT toRelativeMinuteNum(toDateTime('2023-01-01 00:30:00')) - toRelativeMinuteNum(toDateTime('2023-01-01 00:00:00')) AS minutes_difference
Response
┌─minutes_difference─┐
│                 30 │
└────────────────────┘

​
toRelativeMonthNum

Introduced in: v1.1.0 Converts a date or date with time to the number of months elapsed since a certain fixed point in the past. The exact point in time is an implementation detail, and therefore this function is not intended to be used standalone. The main purpose of the function is to calculate the difference in months between two dates or dates with time, e.g., toRelativeMonthNum(dt1) - toRelativeMonthNum(dt2). Syntax 
toRelativeMonthNum(date)
Arguments Returned value Returns the number of months from a fixed reference point in the past. UInt32 Examples Get relative month numbers
Query
SELECT toRelativeMonthNum(toDate('2023-04-01')) - toRelativeMonthNum(toDate('2023-01-01')) AS months_difference
Response
┌─months_difference─┐
│                 3 │
└───────────────────┘

​
toRelativeQuarterNum

Introduced in: v1.1.0 Converts a date or date with time to the number of quarters elapsed since a certain fixed point in the past. The exact point in time is an implementation detail, and therefore this function is not intended to be used standalone. The main purpose of the function is to calculate the difference in quarters between two dates or dates with time, e.g., toRelativeQuarterNum(dt1) - toRelativeQuarterNum(dt2). Syntax 
toRelativeQuarterNum(date)
Arguments Returned value Returns the number of quarters from a fixed reference point in the past. UInt32 Examples Get relative quarter numbers
Query
SELECT toRelativeQuarterNum(toDate('2023-04-01')) - toRelativeQuarterNum(toDate('2023-01-01')) AS quarters_difference
Response
┌─quarters_difference─┐
│                   1 │
└─────────────────────┘

​
toRelativeSecondNum

Introduced in: v1.1.0 Converts a date or date with time to the number of seconds elapsed since a certain fixed point in the past. The exact point in time is an implementation detail, and therefore this function is not intended to be used standalone. The main purpose of the function is to calculate the difference in seconds between two dates or dates with time, e.g., toRelativeSecondNum(dt1) - toRelativeSecondNum(dt2). Syntax 
toRelativeSecondNum(date)
Arguments Returned value Returns the number of seconds from a fixed reference point in the past. UInt32 Examples Get relative second numbers
Query
SELECT toRelativeSecondNum(toDateTime('2023-01-01 00:01:00')) - toRelativeSecondNum(toDateTime('2023-01-01 00:00:00')) AS seconds_difference
Response
┌─seconds_difference─┐
│                 60 │
└────────────────────┘

​
toRelativeWeekNum

Introduced in: v1.1.0 Converts a date or date with time to the number of weeks elapsed since a certain fixed point in the past. The exact point in time is an implementation detail, and therefore this function is not intended to be used standalone. The main purpose of the function is to calculate the difference in weeks between two dates or dates with time, e.g., toRelativeWeekNum(dt1) - toRelativeWeekNum(dt2). Syntax 
toRelativeWeekNum(date)
Arguments Returned value Returns the number of weeks from a fixed reference point in the past. UInt32 Examples Get relative week numbers
Query
SELECT toRelativeWeekNum(toDate('2023-01-08')) - toRelativeWeekNum(toDate('2023-01-01')) AS weeks_difference
Response
┌─weeks_difference─┐
│                1 │
└──────────────────┘

​
toRelativeYearNum

Introduced in: v1.1.0 Converts a date or date with time to the number of years elapsed since a certain fixed point in the past. The exact point in time is an implementation detail, and therefore this function is not intended to be used standalone. The main purpose of the function is to calculate the difference in years between two dates or dates with time, e.g., toRelativeYearNum(dt1) - toRelativeYearNum(dt2). Syntax 
toRelativeYearNum(date)
Arguments Returned value Returns the number of years from a fixed reference point in the past. UInt16 Examples Get relative year numbers
Query
SELECT toRelativeYearNum('2010-10-01'::DateTime) - toRelativeYearNum('2000-01-01'::DateTime)
Response
┌─minus(toRela⋯ateTime')))─┐
│                       10 │
└──────────────────────────┘

​
toSecond

Introduced in: v1.1.0 Returns the second component (0-59) of a DateTime or DateTime64 value. Syntax 
toSecond(datetime)
Aliases: SECOND Arguments Returned value Returns the second in the minute (0 - 59) of datetime. UInt8 Examples Usage example
Query
SELECT toSecond(toDateTime('2023-04-21 10:20:30'))
Response
┌─toSecond(toDateTime('2023-04-21 10:20:30'))─┐
│                                          30 │
└─────────────────────────────────────────────┘

​
toStartOfDay

Introduced in: v1.1.0 Rounds down a date with time to the start of the day.
The return type can be configured by setting enable_extended_results_for_datetime_functions.
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. DateTime or DateTime64 Examples Round down to the start of the day
Query
SELECT toStartOfDay(toDateTime('2023-04-21 10:20:30'))
Response
┌─toStartOfDay(toDateTime('2023-04-21 10:20:30'))─┐
│                             2023-04-21 00:00:00 │
└─────────────────────────────────────────────────┘

​
toStartOfFifteenMinutes

Introduced in: v1.1.0 Rounds down the date with time to the start of the fifteen-minute interval.
The return type can be configured by setting enable_extended_results_for_datetime_functions.
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
Query
SELECT
    toStartOfFifteenMinutes(toDateTime('2023-04-21 10:17:00')),
    toStartOfFifteenMinutes(toDateTime('2023-04-21 10:20:00')),
    toStartOfFifteenMinutes(toDateTime('2023-04-21 10:23:00'))
FORMAT Vertical
Response
Row 1:
──────
toStartOfFifteenMinutes(toDateTime('2023-04-21 10:17:00')): 2023-04-21 10:15:00
toStartOfFifteenMinutes(toDateTime('2023-04-21 10:20:00')): 2023-04-21 10:15:00
toStartOfFifteenMinutes(toDateTime('2023-04-21 10:23:00')): 2023-04-21 10:15:00

​
toStartOfFiveMinutes

Introduced in: v22.6.0 Rounds down a date with time to the start of the nearest five-minute interval.
The return type can be configured by setting enable_extended_results_for_datetime_functions.
Syntax 
toStartOfFiveMinutes(datetime)
Aliases: toStartOfFiveMinute Arguments Returned value Returns the date with time rounded to the start of the nearest five-minute interval. DateTime or DateTime64 Examples Example
Query
SELECT
    toStartOfFiveMinutes(toDateTime('2023-04-21 10:17:00')),
    toStartOfFiveMinutes(toDateTime('2023-04-21 10:20:00')),
    toStartOfFiveMinutes(toDateTime('2023-04-21 10:23:00'))
FORMAT Vertical
Response
Row 1:
──────
toStartOfFiveMinutes(toDateTime('2023-04-21 10:17:00')): 2023-04-21 10:15:00
toStartOfFiveMinutes(toDateTime('2023-04-21 10:20:00')): 2023-04-21 10:20:00
toStartOfFiveMinutes(toDateTime('2023-04-21 10:23:00')): 2023-04-21 10:20:00

​
toStartOfHour

Introduced in: v1.1.0 Rounds down a date with time to the start of the hour.
The return type can be configured by setting enable_extended_results_for_datetime_functions.
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
Query
SELECT
    toStartOfHour(toDateTime('2023-04-21 10:20:30'));
Response
┌─────────────────res─┬─toTypeName(res)─┐
│ 2023-04-21 10:00:00 │ DateTime        │
└─────────────────────┴─────────────────┘

​
toStartOfISOYear

Introduced in: v1.1.0 Rounds down a date or date with time to the first day of the ISO year, which can be different than a regular year. See ISO week date.
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
Query
SELECT toStartOfISOYear(toDateTime('2023-04-21 10:20:30'))
Response
┌─toStartOfISOYear(toDateTime('2023-04-21 10:20:30'))─┐
│                                          2023-01-02 │
└─────────────────────────────────────────────────────┘

​
toStartOfInterval

Introduced in: v20.1.0 This function generalizes other toStartOf*() functions with toStartOfInterval(date_or_date_with_time, INTERVAL x unit [, time_zone]) syntax. For example,
  • toStartOfInterval(t, INTERVAL 1 YEAR) returns the same as toStartOfYear(t),
  • toStartOfInterval(t, INTERVAL 1 MONTH) returns the same as toStartOfMonth(t),
  • toStartOfInterval(t, INTERVAL 1 DAY) returns the same as toStartOfDay(t),
  • toStartOfInterval(t, INTERVAL 15 MINUTE) returns the same as toStartOfFifteenMinutes(t).
The calculation is performed relative to specific points in time:
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]])
Aliases: time_bucket, date_bin 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
Query
SELECT toStartOfInterval(toDateTime('2023-01-15 14:30:00'), INTERVAL 1 MONTH)
Response
┌─toStartOfInt⋯alMonth(1))─┐
│               2023-01-01 │
└──────────────────────────┘
Using origin point
Query
SELECT toStartOfInterval(toDateTime('2023-01-01 14:45:00'), INTERVAL 1 MINUTE, toDateTime('2023-01-01 14:35:30'))
Response
┌─toStartOfInt⋯14:35:30'))─┐
│      2023-01-01 14:44:30 │
└──────────────────────────┘

​
toStartOfMicrosecond

Introduced in: v22.6.0 Rounds down a date with time to the start of the microseconds. Syntax 
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
Query
WITH toDateTime64('2020-01-01 10:20:30.999999999', 9) AS dt64
SELECT toStartOfMicrosecond(dt64);
Response
┌────toStartOfMicrosecond(dt64)─┐
│ 2020-01-01 10:20:30.999999000 │
└───────────────────────────────┘
Query with timezone
Query
WITH toDateTime64('2020-01-01 10:20:30.999999999', 9) AS dt64
SELECT toStartOfMicrosecond(dt64, 'Asia/Istanbul');
Response
┌─toStartOfMicrosecond(dt64, 'Asia/Istanbul')─┐
│               2020-01-01 12:20:30.999999000 │
└─────────────────────────────────────────────┘

​
toStartOfMillisecond

Introduced in: v22.6.0 Rounds down a date with time to the start of the milliseconds. Syntax 
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
Query
WITH toDateTime64('2020-01-01 10:20:30.999999999', 9) AS dt64
SELECT toStartOfMillisecond(dt64);
Response
┌────toStartOfMillisecond(dt64)─┐
│ 2020-01-01 10:20:30.999000000 │
└───────────────────────────────┘
Query with timezone
Query
WITH toDateTime64('2020-01-01 10:20:30.999999999', 9) AS dt64
SELECT toStartOfMillisecond(dt64, 'Asia/Istanbul');
Response
┌─toStartOfMillisecond(dt64, 'Asia/Istanbul')─┐
│               2020-01-01 12:20:30.999000000 │
└─────────────────────────────────────────────┘

​
toStartOfMinute

Introduced in: v1.1.0 Rounds down a date with time to the start of the minute.
The return type can be configured by setting enable_extended_results_for_datetime_functions.
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
Query
SELECT
    toStartOfMinute(toDateTime('2023-04-21 10:20:30')),
    toStartOfMinute(toDateTime64('2023-04-21 10:20:30.5300', 8))
FORMAT Vertical
Response
Row 1:
──────
toStartOfMinute(toDateTime('2023-04-21 10:20:30')):           2023-04-21 10:20:00
toStartOfMinute(toDateTime64('2023-04-21 10:20:30.5300', 8)): 2023-04-21 10:20:00

​
toStartOfMonth

Introduced in: v1.1.0 Rounds down a date or date with time to the first day of the month.
The return type can be configured by setting enable_extended_results_for_datetime_functions.
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
Query
SELECT toStartOfMonth(toDateTime('2023-04-21 10:20:30'))
Response
┌─toStartOfMonth(toDateTime('2023-04-21 10:20:30'))─┐
│                                        2023-04-01 │
└───────────────────────────────────────────────────┘

​
toStartOfNanosecond

Introduced in: v22.6.0 Rounds down a date with time to the start of the nanoseconds. Syntax 
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
Query
WITH toDateTime64('2020-01-01 10:20:30.999999999', 9) AS dt64
SELECT toStartOfNanosecond(dt64);
Response
┌─────toStartOfNanosecond(dt64)─┐
│ 2020-01-01 10:20:30.999999999 │
└───────────────────────────────┘
Query with timezone
Query
WITH toDateTime64('2020-01-01 10:20:30.999999999', 9) AS dt64
SELECT toStartOfNanosecond(dt64, 'Asia/Istanbul');
Response
┌─toStartOfNanosecond(dt64, 'Asia/Istanbul')─┐
│              2020-01-01 12:20:30.999999999 │
└────────────────────────────────────────────┘

​
toStartOfQuarter

Introduced in: v1.1.0 Rounds down a date or date with time to the first day of the quarter. The first day of the quarter is either 1 January, 1 April, 1 July, or 1 October.
The return type can be configured by setting enable_extended_results_for_datetime_functions.
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
Query
SELECT toStartOfQuarter(toDateTime('2023-04-21 10:20:30'))
Response
┌─toStartOfQuarter(toDateTime('2023-04-21 10:20:30'))─┐
│                                          2023-04-01 │
└─────────────────────────────────────────────────────┘

​
toStartOfSecond

Introduced in: v20.5.0 Rounds down a date with time to the start of the seconds. Syntax 
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
Query
WITH toDateTime64('2020-01-01 10:20:30.999', 3) AS dt64
SELECT toStartOfSecond(dt64);
Response
┌───toStartOfSecond(dt64)─┐
│ 2020-01-01 10:20:30.000 │
└─────────────────────────┘
Query with timezone
Query
WITH toDateTime64('2020-01-01 10:20:30.999', 3) AS dt64
SELECT toStartOfSecond(dt64, 'Asia/Istanbul');
Response
┌─toStartOfSecond(dt64, 'Asia/Istanbul')─┐
│                2020-01-01 13:20:30.000 │
└────────────────────────────────────────┘

​
toStartOfTenMinutes

Introduced in: v20.1.0 Rounds down a date with time to the start of the nearest ten-minute interval.
The return type can be configured by setting enable_extended_results_for_datetime_functions.
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
Query
SELECT
    toStartOfTenMinutes(toDateTime('2023-04-21 10:17:00')),
    toStartOfTenMinutes(toDateTime('2023-04-21 10:20:00')),
    toStartOfTenMinutes(toDateTime('2023-04-21 10:23:00'))
FORMAT Vertical
Response
Row 1:
──────
toStartOfTenMinutes(toDateTime('2023-04-21 10:17:00')): 2023-04-21 10:10:00
toStartOfTenMinutes(toDateTime('2023-04-21 10:20:00')): 2023-04-21 10:20:00
toStartOfTenMinutes(toDateTime('2023-04-21 10:23:00')): 2023-04-21 10:20:00

​
toStartOfWeek

Introduced in: v20.1.0 Rounds a date or date with time down to the nearest Sunday or Monday.
The return type can be configured by setting enable_extended_results_for_datetime_functions.
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 Examples Round down to the nearest Sunday or Monday
Query
SELECT
        toStartOfWeek(toDateTime('2023-04-21 10:20:30')), /* a Friday */
        toStartOfWeek(toDateTime('2023-04-21 10:20:30'), 1), /* a Friday */
        toStartOfWeek(toDate('2023-04-24')), /* a Monday */
        toStartOfWeek(toDate('2023-04-24'), 1) /* a Monday */
    FORMAT Vertical
Response
Row 1:
    ──────
    toStartOfWeek(toDateTime('2023-04-21 10:20:30')):      2023-04-17
    toStartOfWeek(toDateTime('2023-04-21 10:20:30'), 1):   2023-04-17
    toStartOfWeek(toDate('2023-04-24')):                   2023-04-24
    toStartOfWeek(toDate('2023-04-24'), 1):                2023-04-24

​
toStartOfYear

Introduced in: v1.1.0 Rounds down a date or date with time to the first day of the year. Returns the date as a Date object.
The return type can be configured by setting enable_extended_results_for_datetime_functions.
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
Query
SELECT toStartOfYear(toDateTime('2023-04-21 10:20:30'))
Response
┌─toStartOfYear(toDateTime('2023-04-21 10:20:30'))─┐
│                                       2023-01-01 │
└──────────────────────────────────────────────────┘

​
toTimeWithFixedDate

Introduced in: v1.1.0 Extracts the time component of a date or date with time. The returned result is an offset to a fixed point in time, currently 1970-01-02, but the exact point in time is an implementation detail which may change in future. toTime should therefore not be used standalone. The main purpose of the function is to calculate the time difference between two dates or dates with time, e.g., toTime(dt1) - toTime(dt2). Syntax 
toTimeWithFixedDate(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
Query
SELECT toTimeWithFixedDate('2025-06-15 12:00:00'::DateTime) - toTimeWithFixedDate('2024-05-10 11:00:00'::DateTime) AS result, toTypeName(result)
Response
┌─result─┬─toTypeName(result)─┐
│   3600 │ Int32              │
└────────┴────────────────────┘

​
toTimezone

Introduced in: v1.1.0 Converts a DateTime or DateTime64 to the specified time zone. The internal value (number of unix seconds) of the data doesn’t change. Only the value’s time zone attribute and the value’s string representation changes. Syntax 
toTimezone(datetime, timezone)
Aliases: toTimeZone Arguments Returned value Returns the same timestamp as the input, but with the specified time zone DateTime or DateTime64 Examples Usage example
Query
SELECT toDateTime('2019-01-01 00:00:00', 'UTC') AS time_utc,
toTypeName(time_utc) AS type_utc,
toInt32(time_utc) AS int32utc,
toTimezone(time_utc, 'Asia/Yekaterinburg') AS time_yekat,
toTypeName(time_yekat) AS type_yekat,
toInt32(time_yekat) AS int32yekat,
toTimezone(time_utc, 'US/Samoa') AS time_samoa,
toTypeName(time_samoa) AS type_samoa,
toInt32(time_samoa) AS int32samoa
FORMAT Vertical;
Response
Row 1:
──────
time_utc:   2019-01-01 00:00:00
type_utc:   DateTime('UTC')
int32utc:   1546300800
time_yekat: 2019-01-01 05:00:00
type_yekat: DateTime('Asia/Yekaterinburg')
int32yekat: 1546300800
time_samoa: 2018-12-31 13:00:00
type_samoa: DateTime('US/Samoa')
int32samoa: 1546300800

​
toUTCTimestamp

Introduced in: v23.8.0 Converts a date or date with time value from one time zone to UTC timezone timestamp. This function is mainly included for compatibility with Apache Spark and similar frameworks. Syntax 
toUTCTimestamp(datetime, time_zone)
Aliases: to_utc_timestamp 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
Query
SELECT toUTCTimestamp(toDateTime('2023-03-16'), 'Asia/Shanghai')
Response
┌─toUTCTimestamp(toDateTime('2023-03-16'), 'Asia/Shanghai')─┐
│                                     2023-03-15 16:00:00 │
└─────────────────────────────────────────────────────────┘

​
toUnixTimestamp

Introduced in: v1.1.0 Converts a String, Date, or DateTime to a Unix timestamp (seconds since 1970-01-01 00:00:00 UTC) as UInt32. Syntax 
toUnixTimestamp(date[, timezone])
Arguments Returned value Returns the Unix timestamp. UInt32 Examples Usage example
Query
SELECT
'2017-11-05 08:07:47' AS dt_str,
toUnixTimestamp(dt_str) AS from_str,
toUnixTimestamp(dt_str, 'Asia/Tokyo') AS from_str_tokyo,
toUnixTimestamp(toDateTime(dt_str)) AS from_datetime,
toUnixTimestamp(toDateTime64(dt_str, 0)) AS from_datetime64,
toUnixTimestamp(toDate(dt_str)) AS from_date,
toUnixTimestamp(toDate32(dt_str)) AS from_date32
FORMAT Vertical;
Response
Row 1:
──────
dt_str:          2017-11-05 08:07:47
from_str:        1509869267
from_str_tokyo:  1509836867
from_datetime:   1509869267
from_datetime64: 1509869267
from_date:       1509840000
from_date32:     1509840000

​
toWeek

Introduced in: v20.1.0 This function returns the week number for date or datetime. The two-argument form of toWeek() enables you to specify whether the week starts on Sunday or Monday and whether the return value should be in the range from 0 to 53 or from 1 to 53. toISOWeek() 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]])
Aliases: week 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
Query
SELECT toDate('2016-12-27') AS date, toWeek(date) AS week0, toWeek(date,1) AS week1, toWeek(date,9) AS week9
Response
┌───────date─┬─week0─┬─week1─┬─week9─┐
│ 2016-12-27 │    52 │    52 │     1 │
└────────────┴───────┴───────┴───────┘

​
toYYYYMM

Introduced in: v1.1.0 Converts a date or date with time to a UInt32 number containing the year and month number (YYYY * 100 + MM). Accepts a second optional timezone argument. If provided, the timezone must be a string constant. This function is the opposite of function YYYYMMDDToDate(). Syntax 
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
Query
SELECT toYYYYMM(now(), 'US/Eastern')
Response
┌─toYYYYMM(now(), 'US/Eastern')─┐
│                        202303 │
└───────────────────────────────┘

​
toYYYYMMDD

Introduced in: v1.1.0 Converts a date or date with time to a UInt32 number containing the year and month number (YYYY * 10000 + MM * 100 + DD). Accepts a second optional timezone argument. If provided, the timezone must be a string constant. Syntax 
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
Query
SELECT toYYYYMMDD(now(), 'US/Eastern')
Response
┌─toYYYYMMDD(now(), 'US/Eastern')─┐
│                        20230302 │
└─────────────────────────────────┘

​
toYYYYMMDDhhmmss

Introduced in: v1.1.0 Converts a date or date with time to a UInt64 number containing the year and month number (YYYY * 10000000000 + MM * 100000000 + DD * 1000000 + hh * 10000 + mm * 100 + ss). Accepts a second optional timezone argument. If provided, the timezone must be a string constant. Syntax 
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
Query
SELECT toYYYYMMDDhhmmss(now(), 'US/Eastern')
Response
┌─toYYYYMMDDhhmmss(now(), 'US/Eastern')─┐
│                        20230302112209 │
└───────────────────────────────────────┘

​
toYear

Introduced in: v1.1.0 Returns the year component (AD) of a Date or DateTime value. Syntax 
toYear(datetime)
Aliases: YEAR Arguments Returned value Returns the year of the given Date or DateTime UInt16 Examples Usage example
Query
SELECT toYear(toDateTime('2023-04-21 10:20:30'))
Response
┌─toYear(toDateTime('2023-04-21 10:20:30'))─┐
│                                     2023  │
└───────────────────────────────────────────┘

​
toYearNumSinceEpoch

Introduced in: v25.3.0 Returns amount of years passed from year 1970 Syntax 
toYearNumSinceEpoch(date)
Arguments Returned value Positive integer Examples Example
Query
SELECT toYearNumSinceEpoch(toDate('2024-10-01'))
Response
54

​
toYearWeek

Introduced in: v20.1.0 Returns the year and week for a date. The year in the result may be different from the year in the date argument for the first and the last week of the year. The mode argument works like the mode argument of toWeek(). 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]])
Aliases: yearweek 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
Query
SELECT toDate('2016-12-27') AS date, toYearWeek(date) AS yearWeek0, toYearWeek(date,1) AS yearWeek1, toYearWeek(date,9) AS yearWeek9, toYearWeek(toDate('2022-01-01')) AS prev_yearWeek
Response
┌───────date─┬─yearWeek0─┬─yearWeek1─┬─yearWeek9─┬─prev_yearWeek─┐
│ 2016-12-27 │    201652 │    201652 │    201701 │        202152 │
└────────────┴───────────┴───────────┴───────────┴───────────────┘

​
today

Introduced in: v1.1.0 Returns the current date at moment of query analysis. Same as toDate(now()). Syntax 
today()
Aliases: curdate, current_date Arguments
  • None.
Returned value Returns the current date Date Examples Usage example
Query
SELECT today() AS today, curdate() AS curdate, current_date() AS current_date FORMAT Pretty
Response
┏━━━━━━━━━━━━┳━━━━━━━━━━━━┳━━━━━━━━━━━━━━┓
┃      today ┃    curdate ┃ current_date ┃
┡━━━━━━━━━━━━╇━━━━━━━━━━━━╇━━━━━━━━━━━━━━┩
│ 2025-03-03 │ 2025-03-03 │   2025-03-03 │
└────────────┴────────────┴──────────────┘
SQL standard syntax without parentheses
Query
SELECT TODAY, CURDATE,CURRENT_DATE
Response
┏━━━━━━━━━━━━┳━━━━━━━━━━━━┳━━━━━━━━━━━━━━┓
┃      TODAY ┃    CURDATE ┃ CURRENT_DATE ┃
┡━━━━━━━━━━━━╇━━━━━━━━━━━━╇━━━━━━━━━━━━━━┩
│ 2025-03-04 │ 2025-03-04 │   2025-03-04 │
└────────────┴────────────┴──────────────┘

​
yesterday

Introduced in: v1.1.0 Accepts zero arguments and returns yesterday’s date at one of the moments of query analysis. Syntax 
yesterday()
Arguments
  • None.
Returned value Returns yesterday’s date. Date Examples Get yesterday’s date
Query
SELECT yesterday();
SELECT today() - 1;
Response
┌─yesterday()─┐
│  2025-06-09 │
└─────────────┘
┌─minus(today(), 1)─┐
│        2025-06-09 │
└───────────────────┘