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 │
└─────────────────────┴────────────┴────────────┴─────────────────────┘

UTCTimestamp

Introduced in: v22.11

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

Arguments

None. Returned value

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

Examples

Get current UTC timestamp

SELECT UTCTimestamp()

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

YYYYMMDDToDate

Introduced in: v23.9

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

YYYYMMDD — Number containing the year, month and day. (U)Int* or Float* or Decimal

Returned value

Returns a Date value from the provided arguments Date

Examples

Example

SELECT YYYYMMDDToDate(20230911);

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

YYYYMMDDToDate32

Introduced in: v23.9

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

YYYYMMDD — Number containing the year, month and day. (U)Int* or Float* or Decimal

Returned value

Returns a Date32 value from the provided arguments Date32

Examples

Example

SELECT YYYYMMDDToDate32(20000507);

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

YYYYMMDDhhmmssToDateTime

Introduced in: v23.9

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

Syntax

YYYYMMDDhhmmssToDateTime(YYYYMMDDhhmmss[, timezone])

Arguments

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

Returned value

Returns a DateTime value from the provided arguments DateTime

Examples

Example

SELECT YYYYMMDDToDateTime(20230911131415);

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

YYYYMMDDhhmmssToDateTime64

Introduced in: v23.9

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

Syntax

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

Arguments

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

Returned value

Returns a DateTime64 value from the provided arguments DateTime64

Examples

Example

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

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

addDate

Introduced in: v23.9

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

Syntax

addDate(datetime, interval)

Arguments

datetime — The date or date with time to which interval is added. Date or Date32 or DateTime or DateTime64 or String
interval — Interval to add. Interval

Returned value

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

Examples

Add interval to date

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

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

addDays

Introduced in: v1.1

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

Syntax

addDays(datetime, num)

Arguments

datetime — Date or date with time to add specified number of days to. Date or Date32 or DateTime or DateTime64 or String
num — Number of days to add. (U)Int* or Float*

Returned value

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

Examples

Add days to different date types

WITH
    toDate('2024-01-01') AS date,
    toDateTime('2024-01-01 00:00:00') AS date_time,
    '2024-01-01 00:00:00' AS date_time_string
SELECT
    addDays(date, 5) AS add_days_with_date,
    addDays(date_time, 5) AS add_days_with_date_time,
    addDays(date_time_string, 5) AS add_days_with_date_time_string

┌─add_days_with_date─┬─add_days_with_date_time─┬─add_days_with_date_time_string─┐
│         2024-01-06 │     2024-01-06 00:00:00 │        2024-01-06 00:00:00.000 │
└────────────────────┴─────────────────────────┴────────────────────────────────┘

Using alternative INTERVAL syntax

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

┌─plus(CAST('1⋯valDay(10))─┐
│               1998-06-26 │
└──────────────────────────┘

addHours

Introduced in: v1.1

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

datetime — Date or date with time to add specified number of hours to. Date or Date32 or DateTime or DateTime64 or String
num — Number of hours to add. (U)Int* or Float*

Returned value

Returns datetime plus num hours DateTime or DateTime64(3)

Examples

Add hours to different date types

WITH
    toDate('2024-01-01') AS date,
    toDateTime('2024-01-01 00:00:00') AS date_time,
    '2024-01-01 00:00:00' AS date_time_string
SELECT
    addHours(date, 12) AS add_hours_with_date,
    addHours(date_time, 12) AS add_hours_with_date_time,
    addHours(date_time_string, 12) AS add_hours_with_date_time_string

┌─add_hours_with_date─┬─add_hours_with_date_time─┬─add_hours_with_date_time_string─┐
│ 2024-01-01 12:00:00 │      2024-01-01 12:00:00 │         2024-01-01 12:00:00.000 │
└─────────────────────┴──────────────────────────┴─────────────────────────────────┘

Using alternative INTERVAL syntax

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

┌─plus(CAST('1⋯alHour(10))─┐
│      1998-06-16 10:00:00 │
└──────────────────────────┘

addInterval

Introduced in: v22.11

Adds an interval to another interval or tuple of intervals.

Note

Intervals of the same type will be combined into a single interval. For instance if toIntervalDay(1) and toIntervalDay(2) are passed then the result will be (3) rather than (1,1).

Syntax

addInterval(interval_1, interval_2)

Arguments

interval_1 — First interval or tuple of intervals. Interval or Tuple(Interval)
interval_2 — Second interval to be added. Interval

Returned value

Returns a tuple of intervals Tuple(Interval)

Examples

Add intervals

SELECT addInterval(INTERVAL 1 DAY, INTERVAL 1 MONTH);
SELECT addInterval((INTERVAL 1 DAY, INTERVAL 1 YEAR), INTERVAL 1 MONTH);
SELECT addInterval(INTERVAL 2 DAY, INTERVAL 1 DAY)

┌─addInterval(toIntervalDay(1), toIntervalMonth(1))─┐
│ (1,1)                                             │
└───────────────────────────────────────────────────┘
┌─addInterval((toIntervalDay(1), toIntervalYear(1)), toIntervalMonth(1))─┐
│ (1,1,1)                                                                │
└────────────────────────────────────────────────────────────────────────┘
┌─addInterval(toIntervalDay(2), toIntervalDay(1))─┐
│ (3)                                             │
└─────────────────────────────────────────────────┘

addMicroseconds

Introduced in: v22.6

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

Syntax

addMicroseconds(datetime, num)

Arguments

datetime — Date with time to add specified number of microseconds to. DateTime or DateTime64 or String
num — Number of microseconds to add. (U)Int* or Float*

Returned value

Returns date_time plus num microseconds DateTime64

Examples

Add microseconds to different date time types

WITH
    toDateTime('2024-01-01 00:00:00') AS date_time,
    '2024-01-01 00:00:00' AS date_time_string
SELECT
    addMicroseconds(date_time, 1000000) AS add_microseconds_with_date_time,
    addMicroseconds(date_time_string, 1000000) AS add_microseconds_with_date_time_string

┌─add_microseconds_with_date_time─┬─add_microseconds_with_date_time_string─┐
│      2024-01-01 00:00:01.000000 │             2024-01-01 00:00:01.000000 │
└─────────────────────────────────┴────────────────────────────────────────┘

Using alternative INTERVAL syntax

SELECT dateAdd('1998-06-16'::DateTime, INTERVAL 10 microsecond)

┌─plus(CAST('19⋯osecond(10))─┐
│ 1998-06-16 00:00:00.000010 │
└────────────────────────────┘

addMilliseconds

Introduced in: v22.6

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

Syntax

addMilliseconds(datetime, num)

Arguments

datetime — Date with time to add specified number of milliseconds to. DateTime or DateTime64 or String
num — Number of milliseconds to add. (U)Int* or Float*

Returned value

Returns datetime plus num milliseconds DateTime64

Examples

Add milliseconds to different date time types

WITH
    toDateTime('2024-01-01 00:00:00') AS date_time,
    '2024-01-01 00:00:00' AS date_time_string
SELECT
    addMilliseconds(date_time, 1000) AS add_milliseconds_with_date_time,
    addMilliseconds(date_time_string, 1000) AS add_milliseconds_with_date_time_string

┌─add_milliseconds_with_date_time─┬─add_milliseconds_with_date_time_string─┐
│         2024-01-01 00:00:01.000 │                2024-01-01 00:00:01.000 │
└─────────────────────────────────┴────────────────────────────────────────┘

Using alternative INTERVAL syntax

SELECT dateAdd('1998-06-16'::DateTime, INTERVAL 10 millisecond)

┌─plus(CAST('1⋯second(10))─┐
│  1998-06-16 00:00:00.010 │
└──────────────────────────┘

addMinutes

Introduced in: v1.1

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

datetime — Date or date with time to add specified number of minutes to. Date or Date32 or DateTime or DateTime64 or String
num — Number of minutes to add. (U)Int* or Float*

Returned value

Returns datetime plus num minutes DateTime or DateTime64(3)

Examples

Add minutes to different date types

WITH
    toDate('2024-01-01') AS date,
    toDateTime('2024-01-01 00:00:00') AS date_time,
    '2024-01-01 00:00:00' AS date_time_string
SELECT
    addMinutes(date, 20) AS add_minutes_with_date,
    addMinutes(date_time, 20) AS add_minutes_with_date_time,
    addMinutes(date_time_string, 20) AS add_minutes_with_date_time_string

┌─add_minutes_with_date─┬─add_minutes_with_date_time─┬─add_minutes_with_date_time_string─┐
│   2024-01-01 00:20:00 │        2024-01-01 00:20:00 │           2024-01-01 00:20:00.000 │
└───────────────────────┴────────────────────────────┴───────────────────────────────────┘

Using alternative INTERVAL syntax

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

┌─plus(CAST('1⋯Minute(10))─┐
│      1998-06-16 00:10:00 │
└──────────────────────────┘

addMonths

Introduced in: v1.1

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

datetime — Date or date with time to add specified number of months to. Date or Date32 or DateTime or DateTime64 or String
num — Number of months to add. (U)Int* or Float*

Returned value

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

Examples

Add months to different date types

WITH
    toDate('2024-01-01') AS date,
    toDateTime('2024-01-01 00:00:00') AS date_time,
    '2024-01-01 00:00:00' AS date_time_string
SELECT
    addMonths(date, 6) AS add_months_with_date,
    addMonths(date_time, 6) AS add_months_with_date_time,
    addMonths(date_time_string, 6) AS add_months_with_date_time_string

┌─add_months_with_date─┬─add_months_with_date_time─┬─add_months_with_date_time_string─┐
│           2024-07-01 │       2024-07-01 00:00:00 │          2024-07-01 00:00:00.000 │
└──────────────────────┴───────────────────────────┴──────────────────────────────────┘

Using alternative INTERVAL syntax

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

┌─plus(CAST('1⋯lMonth(10))─┐
│               1999-04-16 │
└──────────────────────────┘

addNanoseconds

Introduced in: v22.6

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

Syntax

addNanoseconds(datetime, num)

Arguments

datetime — Date with time to add specified number of nanoseconds to. DateTime or DateTime64 or String
num — Number of nanoseconds to add. (U)Int* or Float*

Returned value

Returns datetime plus num nanoseconds DateTime64

Examples

Add nanoseconds to different date time types

WITH
    toDateTime('2024-01-01 00:00:00') AS date_time,
    '2024-01-01 00:00:00' AS date_time_string
SELECT
    addNanoseconds(date_time, 1000) AS add_nanoseconds_with_date_time,
    addNanoseconds(date_time_string, 1000) AS add_nanoseconds_with_date_time_string

┌─add_nanoseconds_with_date_time─┬─add_nanoseconds_with_date_time_string─┐
│  2024-01-01 00:00:00.000001000 │         2024-01-01 00:00:00.000001000 │
└────────────────────────────────┴───────────────────────────────────────┘

Using alternative INTERVAL syntax

SELECT dateAdd('1998-06-16'::DateTime, INTERVAL 1000 nanosecond)

┌─plus(CAST('199⋯osecond(1000))─┐
│ 1998-06-16 00:00:00.000001000 │
└───────────────────────────────┘

addQuarters

Introduced in: v20.1

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

datetime — Date or date with time to add specified number of quarters to. Date or Date32 or DateTime or DateTime64 or String
num — Number of quarters to add. (U)Int* or Float*

Returned value

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

Examples

Add quarters to different date types

WITH
    toDate('2024-01-01') AS date,
    toDateTime('2024-01-01 00:00:00') AS date_time,
    '2024-01-01 00:00:00' AS date_time_string
SELECT
    addQuarters(date, 1) AS add_quarters_with_date,
    addQuarters(date_time, 1) AS add_quarters_with_date_time,
    addQuarters(date_time_string, 1) AS add_quarters_with_date_time_string

┌─add_quarters_with_date─┬─add_quarters_with_date_time─┬─add_quarters_with_date_time_string─┐
│             2024-04-01 │         2024-04-01 00:00:00 │            2024-04-01 00:00:00.000 │
└────────────────────────┴─────────────────────────────┴────────────────────────────────────┘

Using alternative INTERVAL syntax

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

┌─plus(CAST('1⋯uarter(10))─┐
│               2000-12-16 │
└──────────────────────────┘

addSeconds

Introduced in: v1.1

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

datetime — Date or date with time to add specified number of seconds to. Date or Date32 or DateTime or DateTime64 or String
num — Number of seconds to add. (U)Int* or Float*

Returned value

Returns datetime plus num seconds DateTime or DateTime64(3)

Examples

Add seconds to different date types

WITH
    toDate('2024-01-01') AS date,
    toDateTime('2024-01-01 00:00:00') AS date_time,
    '2024-01-01 00:00:00' AS date_time_string
SELECT
    addSeconds(date, 30) AS add_seconds_with_date,
    addSeconds(date_time, 30) AS add_seconds_with_date_time,
    addSeconds(date_time_string, 30) AS add_seconds_with_date_time_string

┌─add_seconds_with_date─┬─add_seconds_with_date_time─┬─add_seconds_with_date_time_string─┐
│   2024-01-01 00:00:30 │        2024-01-01 00:00:30 │           2024-01-01 00:00:30.000 │
└───────────────────────┴────────────────────────────┴───────────────────────────────────┘

Using alternative INTERVAL syntax

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

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

addTupleOfIntervals

Introduced in: v22.11

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

Syntax

addTupleOfIntervals(datetime, intervals)

Arguments

datetime — Date or date with time to add intervals to. Date or Date32 or DateTime or DateTime64
intervals — Tuple of intervals to add to datetime. Tuple(Interval)

Returned value

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

Examples

Add tuple of intervals to date

WITH toDate('2018-01-01') AS date
SELECT addTupleOfIntervals(date, (INTERVAL 1 DAY, INTERVAL 1 MONTH, INTERVAL 1 YEAR))

┌─addTupleOfIntervals(date, (toIntervalDay(1), toIntervalMonth(1), toIntervalYear(1)))─┐
│                                                                           2019-02-02 │
└──────────────────────────────────────────────────────────────────────────────────────┘

addWeeks

Introduced in: v1.1

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

datetime — Date or date with time to add specified number of weeks to. Date or Date32 or DateTime or DateTime64 or String
num — Number of weeks to add. (U)Int* or Float*

Returned value

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

Examples

Add weeks to different date types

WITH
    toDate('2024-01-01') AS date,
    toDateTime('2024-01-01 00:00:00') AS date_time,
    '2024-01-01 00:00:00' AS date_time_string
SELECT
    addWeeks(date, 5) AS add_weeks_with_date,
    addWeeks(date_time, 5) AS add_weeks_with_date_time,
    addWeeks(date_time_string, 5) AS add_weeks_with_date_time_string

┌─add_weeks_with_date─┬─add_weeks_with_date_time─┬─add_weeks_with_date_time_string─┐
│          2024-02-05 │      2024-02-05 00:00:00 │         2024-02-05 00:00:00.000 │
└─────────────────────┴──────────────────────────┴─────────────────────────────────┘

Using alternative INTERVAL syntax

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

┌─plus(CAST('1⋯alWeek(10))─┐
│               1998-08-25 │
└──────────────────────────┘

addYears

Introduced in: v1.1

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

datetime — Date or date with time to add specified number of years to. Date or Date32 or DateTime or DateTime64 or String
num — Number of years to add. (U)Int* or Float*

Returned value

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

Examples

Add years to different date types

WITH
    toDate('2024-01-01') AS date,
    toDateTime('2024-01-01 00:00:00') AS date_time,
    '2024-01-01 00:00:00' AS date_time_string
SELECT
    addYears(date, 1) AS add_years_with_date,
    addYears(date_time, 1) AS add_years_with_date_time,
    addYears(date_time_string, 1) AS add_years_with_date_time_string

┌─add_years_with_date─┬─add_years_with_date_time─┬─add_years_with_date_time_string─┐
│          2025-01-01 │      2025-01-01 00:00:00 │         2025-01-01 00:00:00.000 │
└─────────────────────┴──────────────────────────┴─────────────────────────────────┘

Using alternative INTERVAL syntax

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

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

age

Introduced in: v23.1

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

Syntax

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

Arguments

unit — The type of interval for result.

Unit	Possible values
nanosecond	`nanosecond`, `nanoseconds`, `ns`
microsecond	`microsecond`, `microseconds`, `us`, `u`
millisecond	`millisecond`, `milliseconds`, `ms`
second	`second`, `seconds`, `ss`, `s`
minute	`minute`, `minutes`, `mi`, `n`
hour	`hour`, `hours`, `hh`, `h`
day	`day`, `days`, `dd`, `d`
week	`week`, `weeks`, `wk`, `ww`
month	`month`, `months`, `mm`, `m`
quarter	`quarter`, `quarters`, `qq`, `q`
year	`year`, `years`, `yyyy`, `yy`

startdate — The first time value to subtract (the subtrahend). Date or Date32 or DateTime or DateTime64
enddate — The second time value to subtract from (the minuend). Date or Date32 or DateTime or DateTime64
timezone — Optional. Timezone name. If specified, it is applied to both startdate and enddate. If not specified, timezones of startdate and enddate are used. If they are not the same, the result is unspecified. String

Returned value

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

Examples

Calculate age in hours

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

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

Calculate age in different units

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

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

changeDay

Introduced in: v24.7

Changes the day component of a date or date time.

Syntax

changeDay(date_or_datetime, value);

Arguments

date_or_datetime — The value to change. Date or Date32 or DateTime or DateTime64
value — The new value. (U)Int*

Returned value

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

Examples

Usage example

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

2024-01-15 00:00:00

changeHour

Introduced in: v24.7

Changes the hour component of a date or date time.

Syntax

changeHour(date_or_datetime, value);

Arguments

date_or_datetime — The value to change. Date or Date32 or DateTime or DateTime64
value — The new value. (U)Int*

Returned value

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

Examples

Usage example

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

2024-01-01 05:00:00

changeMinute

Introduced in: v24.7

Changes the minute component of a date or date time.

Syntax

changeMinute(date_or_datetime, value);

Arguments

date_or_datetime — The value to change. Date or Date32 or DateTime or DateTime64
value — The new value. (U)Int*

Returned value

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

Examples

Usage example

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

2024-01-01 12:45:00

changeMonth

Introduced in: v24.7

Changes the month component of a date or date time.

Syntax

changeMonth(date_or_datetime, value);

Arguments

date_or_datetime — The value to change. Date or Date32 or DateTime or DateTime64
value — The new value. (U)Int*

Returned value

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

Examples

Usage example

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

2024-12-01 00:00:00

changeSecond

Introduced in: v24.7

Changes the second component of a date or date time.

Syntax

changeSecond(date_or_datetime, value);

Arguments

date_or_datetime — The value to change. Date or Date32 or DateTime or DateTime64
value — The new value. (U)Int*

Returned value

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

Examples

Usage example

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

2024-01-01 12:30:15

changeYear

Introduced in: v24.7

Changes the year component of a date or date time.

Syntax

changeYear(date_or_datetime, value);

Arguments

date_or_datetime — The value to change. Date or Date32 or DateTime or DateTime64
value — The new value. (U)Int*

Returned value

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

Examples

Usage example

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

2023-01-01 00:00:00

dateName

Introduced in: v21.7

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

date_part — The part of the date that you want to extract. String
datetime — A date or date with time value. Date or Date32 or DateTime or DateTime64
timezone — Optional. Timezone. String

Returned value

Returns the specified part of date. String

Examples

Extract different date parts

WITH toDateTime('2021-04-14 11:22:33') AS date_value
SELECT
    dateName('year', date_value),
    dateName('month', date_value),
    dateName('day', date_value)

┌─dateName('year', date_value)─┬─dateName('month', date_value)─┬─dateName('day', date_value)─┐
│ 2021                         │ April                         │ 14                          │
└──────────────────────────────┴───────────────────────────────┴─────────────────────────────┘

dateTrunc

Introduced in: v20.8

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

Syntax

dateTrunc(unit, datetime[, timezone])

Arguments

unit — The type of interval to truncate the result. unit argument is case-insensitive. | Unit | Compatibility | |--------------|---------------------------------| | nanosecond | Compatible only with DateTime64 | | microsecond| Compatible only with DateTime64 | | millisecond| Compatible only with 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 Argument	`datetime` Argument	Return Type
Year, Quarter, Month, Week	`Date32` or `DateTime64` or `Date` or `DateTime`	`Date32` or `Date`
Day, Hour, Minute, Second	`Date32`, `DateTime64`, `Date`, or `DateTime`	`DateTime64` or `DateTime`
Millisecond, Microsecond,	Any	`DateTime64`
Nanosecond		with scale 3, 6, or 9

Examples

Truncate without timezone

SELECT now(), dateTrunc('hour', now());

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

Truncate with specified timezone

SELECT now(), dateTrunc('hour', now(), 'Asia/Istanbul');

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

formatDateTime

Introduced in: v1.1

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:

Placeholder	Description	Example
%a	abbreviated weekday name (Mon-Sun)	Mon
%b	abbreviated month name (Jan-Dec)	Jan
%c	month as an integer number (01-12)	01
%C	year divided by 100 and truncated to integer (00-99)	20
%d	day of the month, zero-padded (01-31)	02
%D	Short MM/DD/YY date, equivalent to %m/%d/%y	01/02/18
%e	day of the month, space-padded (1-31)	2
%f	fractional second	123456
%F	short YYYY-MM-DD date, equivalent to %Y-%m-%d	2018-01-02
%g	two-digit year format, aligned to ISO 8601	18
%G	four-digit year format for ISO week number	2018
%h	hour in 12h format (01-12)	09
%H	hour in 24h format (00-23)	22
%i	minute (00-59)	33
%I	hour in 12h format (01-12)	10
%j	day of the year (001-366)	002
%k	hour in 24h format (00-23)	14
%l	hour in 12h format (01-12)	09
%m	month as an integer number (01-12)	01
%M	full month name (January-December)	January
%n	new-line character
%p	AM or PM designation	PM
%Q	Quarter (1-4)	1
%r	12-hour HH:MM AM/PM time, equivalent to %h:%i %p	10:30 PM
%R	24-hour HH:MM time, equivalent to %H:%i	22:33
%s	second (00-59)	44
%S	second (00-59)	44
%t	horizontal-tab character
%T	ISO 8601 time format (HH:MM:SS), equivalent to %H:%i:%S	22:33:44
%u	ISO 8601 weekday as number with Monday as 1 (1-7)	2
%V	ISO 8601 week number (01-53)	01
%w	weekday as a integer number with Sunday as 0 (0-6)	2
%W	full weekday name (Monday-Sunday)	Monday
%y	Year, last two digits (00-99)	18
%Y	Year	2018
%z	Time offset from UTC as +HHMM or -HHMM	-0500
%%	a % sign	%

In ClickHouse versions earlier than v23.4, %f prints a single zero (0) if the formatted value is a Date, Date32 or DateTime (which have no fractional seconds) or a DateTime64 with a precision of 0.
In ClickHouse versions earlier than v25.1, %f prints as many digits as specified by the scale of the DateTime64 instead of fixed 6 digits.
In ClickHouse versions earlier than v23.4, %M prints the minute (00-59) instead of the full month name (January-December).

Syntax

formatDateTime(datetime, format[, timezone])

Arguments

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

Returned value

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

Examples

Format date with year placeholder

SELECT formatDateTime(toDate('2010-01-04'), '%g')

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

Format DateTime64 with fractional seconds

SELECT formatDateTime(toDateTime64('2010-01-04 12:34:56.123456', 7), '%f')

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

Format with timezone

SELECT
    now() AS ts,
    time_zone,
    formatDateTime(ts, '%T', time_zone) AS str_tz_time
FROM system.time_zones
WHERE time_zone LIKE 'Europe%'
LIMIT 10

┌──────────────────ts─┬─time_zone─────────┬─str_tz_time─┐
│ 2023-09-08 19:13:40 │ Europe/Amsterdam  │ 21:13:40    │
│ 2023-09-08 19:13:40 │ Europe/Andorra    │ 21:13:40    │
│ 2023-09-08 19:13:40 │ Europe/Astrakhan  │ 23:13:40    │
│ 2023-09-08 19:13:40 │ Europe/Athens     │ 22:13:40    │
│ 2023-09-08 19:13:40 │ Europe/Belfast    │ 20:13:40    │
│ 2023-09-08 19:13:40 │ Europe/Belgrade   │ 21:13:40    │
│ 2023-09-08 19:13:40 │ Europe/Berlin     │ 21:13:40    │
│ 2023-09-08 19:13:40 │ Europe/Bratislava │ 21:13:40    │
│ 2023-09-08 19:13:40 │ Europe/Brussels   │ 21:13:40    │
│ 2023-09-08 19:13:40 │ Europe/Bucharest  │ 22:13:40    │
└─────────────────────┴───────────────────┴─────────────┘

formatDateTimeInJodaSyntax

Introduced in: v20.1

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:

Placeholder	Description	Presentation	Examples
G	era	text	AD
C	century of era (>=0)	number	20
Y	year of era (>=0)	year	1996
x	weekyear (not supported yet)	year	1996
w	week of weekyear (not supported yet)	number	27
e	day of week	number	2
E	day of week	text	Tuesday; Tue
y	year	year	1996
D	day of year	number	189
M	month of year	month	July; Jul; 07
d	day of month	number	10
a	halfday of day	text	PM
K	hour of halfday (0~11)	number	0
h	clockhour of halfday (1~12)	number	12
H	hour of day (0~23)	number	0
k	clockhour of day (1~24)	number	24
m	minute of hour	number	30
s	second of minute	number	55
S	fraction of second	number	978
z	time zone	text	Eastern Standard Time; EST
Z	time zone offset	zone	-0800; -0812
'	escape for text	delimiter
''	single quote	literal	'

Syntax

formatDateTimeInJodaSyntax(datetime, format[, timezone])

Arguments

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

Returned value

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

Examples

Format datetime using Joda syntax

SELECT formatDateTimeInJodaSyntax(toDateTime('2010-01-04 12:34:56'), 'yyyy-MM-dd HH:mm:ss')

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

fromDaysSinceYearZero

Introduced in: v23.11

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

The calculation is the same as in MySQL's FROM_DAYS() function. The result is undefined if it cannot be represented within the bounds of the Date type.

Syntax

fromDaysSinceYearZero(days)

Arguments

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

Returned value

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

Examples

Convert days since year zero to dates

SELECT
fromDaysSinceYearZero(739136) AS date1,
fromDaysSinceYearZero(toDaysSinceYearZero(toDate('2023-09-08'))) AS date2

┌──────date1─┬──────date2─┐
│ 2023-09-08 │ 2023-09-08 │
└────────────┴────────────┘

fromDaysSinceYearZero32

Introduced in: v23.11

For a given number of days elapsed since 1 January 0000, returns the corresponding date in the proleptic Gregorian calendar defined by ISO 8601. The calculation is the same as in MySQL's FROM_DAYS() function. The result is undefined if it cannot be represented within the bounds of the Date32 type.

Syntax

fromDaysSinceYearZero32(days)

Arguments

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

Returned value

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

Examples

Convert days since year zero to dates

SELECT
fromDaysSinceYearZero32(739136) AS date1,
fromDaysSinceYearZero32(toDaysSinceYearZero(toDate('2023-09-08'))) AS date2

┌──────date1─┬──────date2─┐
│ 2023-09-08 │ 2023-09-08 │
└────────────┴────────────┘

fromModifiedJulianDay

Introduced in: v21.1

Converts a Modified Julian Day number to a Proleptic Gregorian calendar date in text form YYYY-MM-DD. This function supports day number from -678941 to 2973483 (which represent 0000-01-01 and 9999-12-31 respectively). It raises an exception if the day number is outside of the supported range.

Syntax

fromModifiedJulianDay(day)

Arguments

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

Returned value

Returns date in text form. String

Examples

Convert Modified Julian Day to date

SELECT fromModifiedJulianDay(58849)

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

fromModifiedJulianDayOrNull

Introduced in: v21.1

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

Syntax

fromModifiedJulianDayOrNull(day)

Arguments

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

Returned value

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

Examples

Convert Modified Julian Day to date with null handling

SELECT fromModifiedJulianDayOrNull(58849);
SELECT fromModifiedJulianDayOrNull(60000000); -- invalid argument, returns NULL

┌─fromModified⋯Null(58849)─┐
│ 2020-01-01               │
└──────────────────────────┘
┌─fromModified⋯l(60000000)─┐
│ ᴺᵁᴸᴸ                     │
└──────────────────────────┘

fromUTCTimestamp

Introduced in: v22.1

Converts a date or date with time value from UTC timezone to a date or date with time value with the specified time zone. This function is mainly included for compatibility with Apache Spark and similar frameworks.

Syntax

fromUTCTimestamp(datetime, time_zone)

Arguments

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

Returned value

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

Examples

Convert UTC timezone to specified timezone

SELECT fromUTCTimestamp(toDateTime64('2023-03-16 10:00:00', 3), 'Asia/Shanghai')

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

fromUnixTimestamp

Introduced in: v20.8

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 formatDateTime. In this case, MySQL's datetime format style is used.

Syntax

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

Arguments

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

Returned value

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

Examples

Convert Unix timestamp to DateTime

SELECT fromUnixTimestamp(423543535)

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

Convert Unix timestamp with format

SELECT fromUnixTimestamp(1234334543, '%Y-%m-%d %R:%S') AS DateTime

┌─DateTime────────────┐
│ 2009-02-11 14:42:23 │
└─────────────────────┘

fromUnixTimestampInJodaSyntax

Introduced in: v23.1

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

It can be called in two ways:

When given a single argument of type Integer, it returns a value of type DateTime, i.e. behaves like toDateTime.

When given two or three arguments where the first argument is a value of type Integer, Date, Date32, DateTime or DateTime64, the second argument is a constant format string and the third argument is an optional constant time zone string, the function returns a value of type String, i.e. it behaves like formatDateTimeInJodaSyntax. In this case, Joda datetime format style is used.

Syntax

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

Arguments

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

Returned value

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

Examples

Convert Unix timestamp with Joda format

SELECT fromUnixTimestampInJodaSyntax(1234334543, 'yyyy-MM-dd HH:mm:ss', 'UTC') AS DateTime

┌─DateTime────────────┐
│ 2009-02-11 06:42:23 │
└─────────────────────┘

makeDate

Introduced in: v22.6

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

year — Year number. (U)Int* or Float* or Decimal
month — Month number (1-12). (U)Int* or Float* or Decimal
day — Day of the month (1-31). (U)Int* or Float* or Decimal
day_of_year — Day of the year (1-365). (U)Int* or Float* or Decimal

Returned value

Returns a Date value constructed from the provided arguments Date

Examples

Date from a year, month, day

SELECT makeDate(2023, 2, 28) AS date;

┌───────date─┐
│ 2023-02-28 │
└────────────┘

Date from year and day of year

SELECT makeDate(2023, 42) AS date;

┌───────date─┐
│ 2023-02-11 │
└────────────┘

makeDate32

Introduced in: v22.6

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

year — Year number. (U)Int* or Float* or Decimal
month — Month number (1-12). (U)Int* or Float* or Decimal
day — Day of the month (1-31). (U)Int* or Float* or Decimal
day_of_year — Day of the year (1-365). (U)Int* or Float* or Decimal

Returned value

Returns a Date32 value constructed from the provided arguments Date32

Examples

Date32 from a year, month, day

SELECT makeDate(2023, 2, 28) AS date;

┌───────date─┐
│ 2023-02-28 │
└────────────┘

Date32 from year and day of year

SELECT makeDate(2023, 42) AS date;

┌───────date─┐
│ 2023-02-11 │
└────────────┘

makeDateTime

Introduced in: v22.6

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

Syntax

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

Arguments

year — Year number. (U)Int* or Float* or Decimal
month — Month number (1-12). (U)Int* or Float* or Decimal
day — Day of the month (1-31). (U)Int* or Float* or Decimal
hour — Hour (0-23). (U)Int* or Float* or Decimal
minute — Minute (0-59). (U)Int* or Float* or Decimal
second — Second (0-59). (U)Int* or Float* or Decimal
timezone — Timezone name. String

Returned value

Returns a DateTime value constructed from the provided arguments DateTime

Examples

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

SELECT makeDateTime(2023, 2, 28, 17, 12, 33) AS DateTime;

┌────────────DateTime─┐
│ 2023-02-28 17:12:33 │
└─────────────────────┘

makeDateTime64

Introduced in: v22.6

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

year — Year number. (U)Int* or Float* or Decimal
month — Month number (1-12). (U)Int* or Float* or Decimal
day — Day of the month (1-31). (U)Int* or Float* or Decimal
hour — Hour (0-23). (U)Int* or Float* or Decimal
minute — Minute (0-59). (U)Int* or Float* or Decimal
second — Second (0-59). (U)Int* or Float* or Decimal
fraction — Fractional part of the 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 constructed from the provided arguments DateTime64

Examples

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

SELECT makeDateTime64(2023, 5, 15, 10, 30, 45, 779, 5);

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

monthName

Introduced in: v22.1

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

Syntax

monthName(datetime)

Arguments

datetime — Date or date with time. Date or Date32 or DateTime or DateTime64

Returned value

Returns the name of the month. String

Examples

Get month name from date

WITH toDateTime('2021-04-14 11:22:33') AS date_value
SELECT monthName(date_value)

┌─monthName(date_value)─┐
│ April                 │
└───────────────────────┘

now

Introduced in: v1.1

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

Syntax

now([timezone])

Arguments

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

Returned value

Returns the current date and time. DateTime

Examples

Query without timezone

SELECT now()

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

Query with specified timezone

SELECT now('Asia/Istanbul')

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

now64

Introduced in: v20.1

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

Syntax

now64([scale], [timezone])

Arguments

scale — Optional. Tick size (precision): 10^-precision seconds. Valid range: [0 : 9]. Typically, are used - 3 (default) (milliseconds), 6 (microseconds), 9 (nanoseconds). UInt8
timezone — Optional. Timezone name for the returned value. String

Returned value

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

Examples

Query with default and custom precision

SELECT now64(), now64(9, 'Asia/Istanbul')

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

nowInBlock

Introduced in: v22.8

Returns the current date and time at the moment of processing of each block of data. In contrast to the function now, it is not a constant expression, and the returned value will be different in different blocks for long-running queries.

It makes sense to use this function to generate the current time in long-running INSERT SELECT queries.

Syntax

nowInBlock([timezone])

Arguments

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

Returned value

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

Examples

Difference with the now() function

SELECT
    now(),
    nowInBlock(),
    sleep(1)
FROM numbers(3)
SETTINGS max_block_size = 1
FORMAT PrettyCompactMonoBlock

┌───────────────now()─┬────────nowInBlock()─┬─sleep(1)─┐
│ 2022-08-21 19:41:19 │ 2022-08-21 19:41:19 │        0 │
│ 2022-08-21 19:41:19 │ 2022-08-21 19:41:20 │        0 │
│ 2022-08-21 19:41:19 │ 2022-08-21 19:41:21 │        0 │
└─────────────────────┴─────────────────────┴──────────┘

nowInBlock64

Introduced in: v25.8

Returns the current date and time at the moment of processing of each block of data in milliseconds. In contrast to the function now64, it is not a constant expression, and the returned value will be different in different blocks for long-running queries.

It makes sense to use this function to generate the current time in long-running INSERT SELECT queries.

Syntax

nowInBlock([scale[, timezone]])

Arguments

scale — Optional. Tick size (precision): 10^-precision seconds. Valid range: [0 : 9]. Typically, are used - 3 (default) (milliseconds), 6 (microseconds), 9 (nanoseconds). UInt8
timezone — Optional. Timezone name for the returned value. String

Returned value

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

Examples

Difference with the now64() function

SELECT
    now64(),
    nowInBlock64(),
    sleep(1)
FROM numbers(3)
SETTINGS max_block_size = 1
FORMAT PrettyCompactMonoBlock

┌─────────────────now64()─┬──────────nowInBlock64()─┬─sleep(1)─┐
│ 2025-07-29 17:07:29.526 │ 2025-07-29 17:07:29.534 │        0 │
│ 2025-07-29 17:07:29.526 │ 2025-07-29 17:07:30.535 │        0 │
│ 2025-07-29 17:07:29.526 │ 2025-07-29 17:07:31.535 │        0 │
└─────────────────────────┴─────────────────────────┴──────────┘

serverTimezone

Introduced in: v23.6

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

Arguments

None. Returned value

Returns the server timezone as a String

Examples

Get server time zone

SELECT serverTimeZone()

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

subDate

Introduced in: v23.9

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

datetime — The date or date with time from which interval is subtracted. Date or Date32 or DateTime or DateTime64
interval — Interval to subtract. Interval

Returned value

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

Examples

Subtract interval from date

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

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

subtractDays

Introduced in: v1.1

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

datetime — Date or date with time to subtract specified number of days from. Date or Date32 or DateTime or DateTime64 or String
num — Number of days to subtract. (U)Int* or Float*

Returned value

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

Examples

Subtract days from different date types

WITH
    toDate('2024-01-01') AS date,
    toDateTime('2024-01-01 00:00:00') AS date_time,
    '2024-01-01 00:00:00' AS date_time_string
SELECT
    subtractDays(date, 31) AS subtract_days_with_date,
    subtractDays(date_time, 31) AS subtract_days_with_date_time,
    subtractDays(date_time_string, 31) AS subtract_days_with_date_time_string

┌─subtract_days_with_date─┬─subtract_days_with_date_time─┬─subtract_days_with_date_time_string─┐
│              2023-12-01 │          2023-12-01 00:00:00 │             2023-12-01 00:00:00.000 │
└─────────────────────────┴──────────────────────────────┴─────────────────────────────────────┘

Using alternative INTERVAL syntax

SELECT dateSub('1998-06-16'::Date, INTERVAL 10 day)

┌─minus(CAST('⋯valDay(10))─┐
│               1998-06-06 │
└──────────────────────────┘

subtractHours

Introduced in: v1.1

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

datetime — Date or date with time to subtract specified number of hours from. Date or Date32 or DateTime or DateTime64 or String
num — Number of hours to subtract. (U)Int* or Float*

Returned value

Returns datetime minus num hours DateTime or DateTime64(3)

Examples

Subtract hours from different date types

WITH
    toDate('2024-01-01') AS date,
    toDateTime('2024-01-01 00:00:00') AS date_time,
    '2024-01-01 00:00:00' AS date_time_string
SELECT
    subtractHours(date, 12) AS subtract_hours_with_date,
    subtractHours(date_time, 12) AS subtract_hours_with_date_time,
    subtractHours(date_time_string, 12) AS subtract_hours_with_date_time_string

┌─subtract_hours_with_date─┬─subtract_hours_with_date_time─┬─subtract_hours_with_date_time_string─┐
│      2023-12-31 12:00:00 │           2023-12-31 12:00:00 │              2023-12-31 12:00:00.000 │
└──────────────────────────┴───────────────────────────────┴──────────────────────────────────────┘

Using alternative INTERVAL syntax

SELECT dateSub('1998-06-16'::Date, INTERVAL 10 hour)

┌─minus(CAST('⋯alHour(10))─┐
│      1998-06-15 14:00:00 │
└──────────────────────────┘

subtractInterval

Introduced in: v22.11

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

interval_1 — First interval or interval of tuples. Interval or Tuple(Interval)
interval_2 — Second interval to be negated. Interval

Returned value

Returns a tuple of intervals Tuple(T)

Examples

Subtract intervals

SELECT subtractInterval(INTERVAL 1 DAY, INTERVAL 1 MONTH);
SELECT subtractInterval((INTERVAL 1 DAY, INTERVAL 1 YEAR), INTERVAL 1 MONTH);
SELECT subtractInterval(INTERVAL 2 DAY, INTERVAL 1 DAY);

┌─subtractInterval(toIntervalDay(1), toIntervalMonth(1))─┐
│ (1,-1)                                                 │
└────────────────────────────────────────────────────────┘
┌─subtractInterval((toIntervalDay(1), toIntervalYear(1)), toIntervalMonth(1))─┐
│ (1,1,-1)                                                                    │
└─────────────────────────────────────────────────────────────────────────────┘
┌─subtractInterval(toIntervalDay(2), toIntervalDay(1))─┐
│ (1)                                                  │
└──────────────────────────────────────────────────────┘

subtractMicroseconds

Introduced in: v22.6

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

Syntax

subtractMicroseconds(datetime, num)

Arguments

datetime — Date with time to subtract specified number of microseconds from. DateTime or DateTime64 or String
num — Number of microseconds to subtract. (U)Int* or Float*

Returned value

Returns datetime minus num microseconds DateTime64

Examples

Subtract microseconds from different date time types

WITH
    toDateTime('2024-01-01 00:00:00') AS date_time,
    '2024-01-01 00:00:00' AS date_time_string
SELECT
    subtractMicroseconds(date_time, 1000000) AS subtract_microseconds_with_date_time,
    subtractMicroseconds(date_time_string, 1000000) AS subtract_microseconds_with_date_time_string

┌─subtract_microseconds_with_date_time─┬─subtract_microseconds_with_date_time_string─┐
│           2023-12-31 23:59:59.000000 │                  2023-12-31 23:59:59.000000 │
└──────────────────────────────────────┴─────────────────────────────────────────────┘

Using alternative INTERVAL syntax

SELECT dateSub('1998-06-16'::DateTime, INTERVAL 10 microsecond)

┌─minus(CAST('1⋯osecond(10))─┐
│ 1998-06-15 23:59:59.999990 │
└────────────────────────────┘

subtractMilliseconds

Introduced in: v22.6

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

Syntax

subtractMilliseconds(datetime, num)

Arguments

datetime — Date with time to subtract specified number of milliseconds from. DateTime or DateTime64 or String
num — Number of milliseconds to subtract. (U)Int* or Float*

Returned value

Returns datetime minus num milliseconds DateTime64

Examples

Subtract milliseconds from different date time types

WITH
    toDateTime('2024-01-01 00:00:00') AS date_time,
    '2024-01-01 00:00:00' AS date_time_string
SELECT
    subtractMilliseconds(date_time, 1000) AS subtract_milliseconds_with_date_time,
    subtractMilliseconds(date_time_string, 1000) AS subtract_milliseconds_with_date_time_string

┌─subtract_milliseconds_with_date_time─┬─subtract_milliseconds_with_date_time_string─┐
│              2023-12-31 23:59:59.000 │                     2023-12-31 23:59:59.000 │
└──────────────────────────────────────┴─────────────────────────────────────────────┘

Using alternative INTERVAL syntax

SELECT dateSub('1998-06-16'::DateTime, INTERVAL 10 millisecond)

┌─minus(CAST('⋯second(10))─┐
│  1998-06-15 23:59:59.990 │
└──────────────────────────┘

subtractMinutes

Introduced in: v1.1

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

datetime — Date or date with time to subtract specified number of minutes from. Date or Date32 or DateTime or DateTime64 or String
num — Number of minutes to subtract. (U)Int* or Float*

Returned value

Returns datetime minus num minutes DateTime or DateTime64(3)

Examples

Subtract minutes from different date types

WITH
    toDate('2024-01-01') AS date,
    toDateTime('2024-01-01 00:00:00') AS date_time,
    '2024-01-01 00:00:00' AS date_time_string
SELECT
    subtractMinutes(date, 30) AS subtract_minutes_with_date,
    subtractMinutes(date_time, 30) AS subtract_minutes_with_date_time,
    subtractMinutes(date_time_string, 30) AS subtract_minutes_with_date_time_string

┌─subtract_minutes_with_date─┬─subtract_minutes_with_date_time─┬─subtract_minutes_with_date_time_string─┐
│        2023-12-31 23:30:00 │             2023-12-31 23:30:00 │                2023-12-31 23:30:00.000 │
└────────────────────────────┴─────────────────────────────────┴────────────────────────────────────────┘

Using alternative INTERVAL syntax

SELECT dateSub('1998-06-16'::Date, INTERVAL 10 minute)

┌─minus(CAST('⋯Minute(10))─┐
│      1998-06-15 23:50:00 │
└──────────────────────────┘

subtractMonths

Introduced in: v1.1

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

datetime — Date or date with time to subtract specified number of months from. Date or Date32 or DateTime or DateTime64 or String
num — Number of months to subtract. (U)Int* or Float*

Returned value

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

Examples

Subtract months from different date types

WITH
    toDate('2024-01-01') AS date,
    toDateTime('2024-01-01 00:00:00') AS date_time,
    '2024-01-01 00:00:00' AS date_time_string
SELECT
    subtractMonths(date, 1) AS subtract_months_with_date,
    subtractMonths(date_time, 1) AS subtract_months_with_date_time,
    subtractMonths(date_time_string, 1) AS subtract_months_with_date_time_string

┌─subtract_months_with_date─┬─subtract_months_with_date_time─┬─subtract_months_with_date_time_string─┐
│                2023-12-01 │            2023-12-01 00:00:00 │               2023-12-01 00:00:00.000 │
└───────────────────────────┴────────────────────────────────┴───────────────────────────────────────┘

Using alternative INTERVAL syntax

SELECT dateSub('1998-06-16'::Date, INTERVAL 10 month)

┌─minus(CAST('⋯lMonth(10))─┐
│               1997-08-16 │
└──────────────────────────┘

subtractNanoseconds

Introduced in: v20.1

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

Syntax

subtractNanoseconds(datetime, num)

Arguments

datetime — Date with time to subtract specified number of nanoseconds from. DateTime or DateTime64 or String
num — Number of nanoseconds to subtract. (U)Int* or Float*

Returned value

Returns datetime minus num nanoseconds DateTime64

Examples

Subtract nanoseconds from different date time types

WITH
    toDateTime('2024-01-01 00:00:00') AS date_time,
    '2024-01-01 00:00:00' AS date_time_string
SELECT
    subtractNanoseconds(date_time, 1000) AS subtract_nanoseconds_with_date_time,
    subtractNanoseconds(date_time_string, 1000) AS subtract_nanoseconds_with_date_time_string

┌─subtract_nanoseconds_with_date_time─┬─subtract_nanoseconds_with_date_time_string─┐
│       2023-12-31 23:59:59.999999000 │              2023-12-31 23:59:59.999999000 │
└─────────────────────────────────────┴────────────────────────────────────────────┘

Using alternative INTERVAL syntax

SELECT dateSub('1998-06-16'::DateTime, INTERVAL 10 nanosecond)

┌─minus(CAST('19⋯anosecond(10))─┐
│ 1998-06-15 23:59:59.999999990 │
└───────────────────────────────┘

subtractQuarters

Introduced in: v20.1

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

datetime — Date or date with time to subtract specified number of quarters from. Date or Date32 or DateTime or DateTime64 or String
num — Number of quarters to subtract. (U)Int* or Float*

Returned value

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

Examples

Subtract quarters from different date types

WITH
    toDate('2024-01-01') AS date,
    toDateTime('2024-01-01 00:00:00') AS date_time,
    '2024-01-01 00:00:00' AS date_time_string
SELECT
    subtractQuarters(date, 1) AS subtract_quarters_with_date,
    subtractQuarters(date_time, 1) AS subtract_quarters_with_date_time,
    subtractQuarters(date_time_string, 1) AS subtract_quarters_with_date_time_string

┌─subtract_quarters_with_date─┬─subtract_quarters_with_date_time─┬─subtract_quarters_with_date_time_string─┐
│                  2023-10-01 │              2023-10-01 00:00:00 │                 2023-10-01 00:00:00.000 │
└─────────────────────────────┴──────────────────────────────────┴─────────────────────────────────────────┘

Using alternative INTERVAL syntax

SELECT dateSub('1998-06-16'::Date, INTERVAL 10 quarter)

┌─minus(CAST('1⋯Quarter(10))─┐
│                1996-09-16 │
└───────────────────────────┘

subtractSeconds

Introduced in: v1.1

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

datetime — Date or date with time to subtract specified number of seconds from. Date or Date32 or DateTime or DateTime64 or String
num — Number of seconds to subtract. (U)Int* or Float*

Returned value

Returns datetime minus num seconds DateTime or DateTime64(3)

Examples

Subtract seconds from different date types

WITH
    toDate('2024-01-01') AS date,
    toDateTime('2024-01-01 00:00:00') AS date_time,
    '2024-01-01 00:00:00' AS date_time_string
SELECT
    subtractSeconds(date, 60) AS subtract_seconds_with_date,
    subtractSeconds(date_time, 60) AS subtract_seconds_with_date_time,
    subtractSeconds(date_time_string, 60) AS subtract_seconds_with_date_time_string

┌─subtract_seconds_with_date─┬─subtract_seconds_with_date_time─┬─subtract_seconds_with_date_time_string─┐
│        2023-12-31 23:59:00 │             2023-12-31 23:59:00 │                2023-12-31 23:59:00.000 │
└────────────────────────────┴─────────────────────────────────┴────────────────────────────────────────┘

Using alternative INTERVAL syntax

SELECT dateSub('1998-06-16'::Date, INTERVAL 10 second)

┌─minus(CAST('⋯Second(10))─┐
│      1998-06-15 23:59:50 │
└──────────────────────────┘

subtractTupleOfIntervals

Introduced in: v22.11

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

Syntax

subtractTupleOfIntervals(datetime, intervals)

Arguments

datetime — Date or date with time to subtract intervals from. Date or Date32 or DateTime or DateTime64
intervals — Tuple of intervals to subtract from datetime. Tuple(Interval)

Returned value

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

Examples

Subtract tuple of intervals from date

WITH toDate('2018-01-01') AS date SELECT subtractTupleOfIntervals(date, (INTERVAL 1 DAY, INTERVAL 1 YEAR))

┌─subtractTupl⋯alYear(1)))─┐
│               2016-12-31 │
└──────────────────────────┘

subtractWeeks

Introduced in: v1.1

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

datetime — Date or date with time to subtract specified number of weeks from. Date or Date32 or DateTime or DateTime64 or String
num — Number of weeks to subtract. (U)Int* or Float*

Returned value

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

Examples

Subtract weeks from different date types

WITH
    toDate('2024-01-01') AS date,
    toDateTime('2024-01-01 00:00:00') AS date_time,
    '2024-01-01 00:00:00' AS date_time_string
SELECT
    subtractWeeks(date, 1) AS subtract_weeks_with_date,
    subtractWeeks(date_time, 1) AS subtract_weeks_with_date_time,
    subtractWeeks(date_time_string, 1) AS subtract_weeks_with_date_time_string

┌─subtract_weeks_with_date─┬─subtract_weeks_with_date_time─┬─subtract_weeks_with_date_time_string─┐
│               2023-12-25 │           2023-12-25 00:00:00 │              2023-12-25 00:00:00.000 │
└──────────────────────────┴───────────────────────────────┴──────────────────────────────────────┘

Using alternative INTERVAL syntax

SELECT dateSub('1998-06-16'::Date, INTERVAL 10 week)

┌─minus(CAST('⋯alWeek(10))─┐
│               1998-04-07 │
└──────────────────────────┘

subtractYears

Introduced in: v1.1

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

datetime — Date or date with time to subtract specified number of years from. Date or Date32 or DateTime or DateTime64 or String
num — Number of years to subtract. (U)Int* or Float*

Returned value

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

Examples

Subtract years from different date types

WITH
    toDate('2024-01-01') AS date,
    toDateTime('2024-01-01 00:00:00') AS date_time,
    '2024-01-01 00:00:00' AS date_time_string
SELECT
    subtractYears(date, 1) AS subtract_years_with_date,
    subtractYears(date_time, 1) AS subtract_years_with_date_time,
    subtractYears(date_time_string, 1) AS subtract_years_with_date_time_string

┌─subtract_years_with_date─┬─subtract_years_with_date_time─┬─subtract_years_with_date_time_string─┐
│               2023-01-01 │           2023-01-01 00:00:00 │              2023-01-01 00:00:00.000 │
└──────────────────────────┴───────────────────────────────┴──────────────────────────────────────┘

Using alternative INTERVAL syntax

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

┌─minus(CAST('⋯alYear(10))─┐
│               1988-06-16 │
└──────────────────────────┘

timeDiff

Introduced in: v23.4

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 timeDiff 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 timeDiff, see function age.

Syntax

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

Arguments

unit — The type of interval for result.

Unit	Possible values
nanosecond	`nanosecond`, `nanoseconds`, `ns`
microsecond	`microsecond`, `microseconds`, `us`, `u`
millisecond	`millisecond`, `milliseconds`, `ms`
second	`second`, `seconds`, `ss`, `s`
minute	`minute`, `minutes`, `mi`, `n`
hour	`hour`, `hours`, `hh`, `h`
day	`day`, `days`, `dd`, `d`
week	`week`, `weeks`, `wk`, `ww`
month	`month`, `months`, `mm`, `m`
quarter	`quarter`, `quarters`, `qq`, `q`
year	`year`, `years`, `yyyy`, `yy`

startdate — The first time value to subtract (the subtrahend). Date or Date32 or DateTime or DateTime64
enddate — The second time value to subtract from (the minuend). Date or Date32 or DateTime or DateTime64
timezone — Optional. Timezone name. If specified, it is applied to both startdate and enddate. If not specified, timezones of startdate and enddate are used. If they are not the same, the result is unspecified. String

Returned value

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

Examples

Calculate date difference in hours

SELECT timeDiff('hour', toDateTime('2018-01-01 22:00:00'), toDateTime('2018-01-02 23:00:00')) AS res

┌─res─┐
│  25 │
└─────┘

Calculate date difference in different units

SELECT
    toDate('2022-01-01') AS e,
    toDate('2021-12-29') AS s,
    timeDiff('day', s, e) AS day_diff,
    timeDiff('month', s, e) AS month_diff,
    timeDiff('year', s, e) AS year_diff

┌──────────e─┬──────────s─┬─day_diff─┬─month_diff─┬─year_diff─┐
│ 2022-01-01 │ 2021-12-29 │        3 │          1 │         1 │
└────────────┴────────────┴──────────┴────────────┴───────────┘

timeSlot

Introduced in: v1.1

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

Note

Although this function can take values of the extended types Date32 and DateTime64 as an argument, passing it a time outside the normal range (year 1970 to 2149 for Date / 2106 for DateTime) will produce wrong results.

Syntax

timeSlot(time[, time_zone])

Arguments

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

Returned value

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

Examples

Round time to half-hour interval

SELECT timeSlot(toDateTime('2000-01-02 03:04:05', 'UTC'))

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

timeSlots

Introduced in: v1.1

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

StartTime — Starting time for the interval. DateTime or DateTime64
Duration — Duration of the interval in seconds. UInt32 or DateTime64
Size — Optional. Size of time slots in seconds. Default is 1800 (30 minutes). UInt32 or DateTime64

Returned value

Returns an array of DateTime/DateTime64 (return type matches the type of StartTime). For DateTime64, the return value's scale can differ from the scale of StartTime - the highest scale among all given arguments is taken. Array(DateTime) or Array(DateTime64)

Examples

Generate time slots for an interval

SELECT timeSlots(toDateTime('2012-01-01 12:20:00'), toUInt32(600));
SELECT timeSlots(toDateTime('1980-12-12 21:01:02', 'UTC'), toUInt32(600), 299);
SELECT timeSlots(toDateTime64('1980-12-12 21:01:02.1234', 4, 'UTC'), toDecimal64(600.1, 1), toDecimal64(299, 0))

┌─timeSlots(toDateTime('2012-01-01 12:20:00'), toUInt32(600))─┐
│ ['2012-01-01 12:00:00','2012-01-01 12:30:00']               │
└─────────────────────────────────────────────────────────────┘
┌─timeSlots(toDateTime('1980-12-12 21:01:02', 'UTC'), toUInt32(600), 299)─┐
│ ['1980-12-12 20:56:13','1980-12-12 21:01:12','1980-12-12 21:06:11']     │
└─────────────────────────────────────────────────────────────────────────┘
┌─timeSlots(toDateTime64('1980-12-12 21:01:02.1234', 4, 'UTC'), toDecimal64(600.1, 1), toDecimal64(299, 0))─┐
│ ['1980-12-12 20:56:13.0000','1980-12-12 21:01:12.0000','1980-12-12 21:06:11.0000']                        │
└───────────────────────────────────────────────────────────────────────────────────────────────────────────┘

timestamp

Introduced in: v23.9

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

Syntax

timestamp(expr[, expr_time])

Arguments

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

Returned value

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

Examples

Convert date string to DateTime64(6)

SELECT timestamp('2023-12-31') AS ts;

┌─────────────────────────ts─┐
│ 2023-12-31 00:00:00.000000 │
└────────────────────────────┘

Add time to date string

SELECT timestamp('2023-12-31 12:00:00', '12:00:00.11') AS ts;

┌─────────────────────────ts─┐
│ 2024-01-01 00:00:00.110000 │
└────────────────────────────┘

timezone

Introduced in: v1.1

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

Arguments

None. Returned value

Returns the canonical time zone name as a String

Examples

Get current session time zone

SELECT timezone()

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

timezoneOf

Introduced in: v21.4

Returns the timezone name of a DateTime or DateTime64 value.

Syntax

timeZoneOf(datetime)

Arguments

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

Returned value

Returns the timezone name for datetime String

Examples

Usage example

SELECT timezoneOf(now());

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

timezoneOffset

Introduced in: v21.6

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

Syntax

timeZoneOffset(datetime)

Arguments

datetime — DateTime value to get the timezone offset for. DateTime or DateTime64

Returned value

Returns the offset from UTC in seconds Int32

Examples

Usage example

SELECT toDateTime('2021-04-21 10:20:30', 'America/New_York') AS Time,
toTypeName(Time) AS Type,
timeZoneOffset(Time) AS Offset_in_seconds,
(Offset_in_seconds / 3600) AS Offset_in_hours;

┌────────────────Time─┬─Type─────────────────────────┬─Offset_in_seconds─┬─Offset_in_hours─┐
│ 2021-04-21 10:20:30 │ DateTime('America/New_York') │            -14400 │              -4 │
└─────────────────────┴──────────────────────────────┴───────────────────┴─────────────────┘

toDayOfMonth

Introduced in: v1.1

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

Syntax

toDayOfMonth(datetime)

Arguments

datetime — Date or date with time to get the day of month from. Date or Date32 or DateTime or DateTime64

Returned value

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

Examples

Usage example

SELECT toDayOfMonth(toDateTime('2023-04-21 10:20:30'))

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

toDayOfWeek

Introduced in: v1.1

Returns the number of the day within the week of a Date or DateTime value.

The two-argument form of toDayOfWeek() enables you to specify whether the week starts on Monday or Sunday, and whether the return value should be in the range from 0 to 6 or 1 to 7.

Mode	First day of week	Range
0	Monday	1-7: Monday = 1, Tuesday = 2, ..., Sunday = 7
1	Monday	0-6: Monday = 0, Tuesday = 1, ..., Sunday = 6
2	Sunday	0-6: Sunday = 0, Monday = 1, ..., Saturday = 6
3	Sunday	1-7: Sunday = 1, Monday = 2, ..., Saturday = 7

Syntax

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

Arguments

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

Returned value

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

Examples

Usage example

-- The following date is April 21, 2023, which was a Friday:
SELECT
    toDayOfWeek(toDateTime('2023-04-21')),
    toDayOfWeek(toDateTime('2023-04-21'), 1)

┌─toDayOfWeek(toDateTime('2023-04-21'))─┬─toDayOfWeek(toDateTime('2023-04-21'), 1)─┐
│                                     5 │                                        4 │
└───────────────────────────────────────┴──────────────────────────────────────────┘

toDayOfYear

Introduced in: v18.4

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

Syntax

toDayOfYear(datetime)

Arguments

datetime — Date or date with time to get the day of year from. Date or Date32 or DateTime or DateTime64

Returned value

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

Examples

Usage example

SELECT toDayOfYear(toDateTime('2023-04-21 10:20:30'))

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

toDaysSinceYearZero

Introduced in: v23.9

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

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

Syntax

toDaysSinceYearZero(date[, time_zone])

Arguments

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

Returned value

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

Examples

Calculate days since year zero

SELECT toDaysSinceYearZero(toDate('2023-09-08'))

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

toHour

Introduced in: v1.1

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

Syntax

toHour(datetime)

Arguments

datetime — Date with time to get the hour from. DateTime or DateTime64

Returned value

Returns the hour of the given Date or DateTime value UInt8

Examples

Usage example

SELECT toHour(toDateTime('2023-04-21 10:20:30'))

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

toISOYear

Introduced in: v18.4

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

Syntax

toISOYear(datetime)

Arguments

datetime — The value with date or date with time. Date or Date32 or DateTime or DateTime64

Returned value

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

Examples

Get ISO year from date values

SELECT
toISOYear(toDate('2024/10/02')) as year1,
toISOYear(toDateTime('2024-10-02 01:30:00')) as year2

┌─week1─┬─week2─┐
│    40 │    40 │
└───────┴───────┘

toLastDayOfMonth

Introduced in: v1.1

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

Note

The return type can be configured by setting enable_extended_results_for_datetime_functions.

Syntax

toLastDayOfMonth(value)

Arguments

value — The date or date with time to round up to the last day of the month. Date or Date32 or DateTime or DateTime64

Returned value

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

Examples

Round up to the last day of the month

SELECT toLastDayOfMonth(toDateTime('2023-04-21 10:20:30'))

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

toLastDayOfWeek

Introduced in: v23.5

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

Note

The return type can be configured by setting enable_extended_results_for_datetime_functions.

Syntax

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

Arguments

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

Returned value

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

Examples

Round up to the nearest Saturday or Sunday

SELECT
    toLastDayOfWeek(toDateTime('2023-04-21 10:20:30')), /* a Friday */
    toLastDayOfWeek(toDateTime('2023-04-21 10:20:30'), 1), /* a Friday */
    toLastDayOfWeek(toDate('2023-04-23')), /* a Sunday */
    toLastDayOfWeek(toDate('2023-04-23'), 1) /* a Sunday */
FORMAT Vertical

Row 1:
──────
toLastDayOfWeek(toDateTime('2023-04-21 10:20:30')):      2023-04-23
toLastDayOfWeek(toDateTime('2023-04-21 10:20:30'), 1):   2023-04-22
toLastDayOfWeek(toDate('2023-04-23')):                   2023-04-23
toLastDayOfWeek(toDate('2023-04-23'), 1):                2023-04-23

toMillisecond

Introduced in: v24.2

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

Syntax

toMillisecond(datetime)

Arguments

datetime — Date with time to get the millisecond from. DateTime or DateTime64

Returned value

Returns the millisecond in the minute (0 - 59) of the given Date or DateTime UInt16

Examples

Usage example

SELECT toMillisecond(toDateTime64('2023-04-21 10:20:30.456', 3));

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

toMinute

Introduced in: v1.1

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

Syntax

toMinute(datetime)

Arguments

datetime — Date or date with time to get the minute from. Date or Date32 or DateTime or DateTime64

Returned value

Returns the minute of the hour (0 - 59) of the given Date or DateTime value UInt8

Examples

Usage example

SELECT toMinute(toDateTime('2023-04-21 10:20:30'))

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

toModifiedJulianDay

Introduced in: v21.1

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

date — The date in String form. String or FixedString

Returned value

Returns Modified Julian Day number. Int32

Examples

Convert date to Modified Julian Day

SELECT toModifiedJulianDay('2020-01-01')

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

toModifiedJulianDayOrNull

Introduced in: v21.1

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

Syntax

toModifiedJulianDayOrNull(date)

Arguments

date — Date in text form. String or FixedString

Returned value

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

Examples

Convert date to Modified Julian Day with null handling

SELECT toModifiedJulianDayOrNull('2020-01-01');
SELECT toModifiedJulianDayOrNull('0000-00-00'); -- invalid date, returns NULL

┌─toModifiedJu⋯020-01-01')─┐
│                    58849 │
└──────────────────────────┘
┌─toModifiedJu⋯000-00-00')─┐
│                     ᴺᵁᴸᴸ │
└──────────────────────────┘

toMonday

Introduced in: v1.1

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

Note

The return type can be configured by setting enable_extended_results_for_datetime_functions.

Syntax

toMonday(value)

Arguments

value — Date or date with time to round down to the Monday of the week. Date or Date32 or DateTime or DateTime64

Returned value

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

Examples

Round down to the Monday of the week

SELECT
toMonday(toDateTime('2023-04-21 10:20:30')), -- A Friday
toMonday(toDate('2023-04-24'));              -- Already a Monday

┌─toMonday(toDateTime('2023-04-21 10:20:30'))─┬─toMonday(toDate('2023-04-24'))─┐
│                                  2023-04-17 │                     2023-04-24 │
└─────────────────────────────────────────────┴────────────────────────────────┘

toMonth

Introduced in: v1.1

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

Syntax

toMonth(datetime)

Arguments

datetime — Date or date with time to get the month from. Date or Date32 or DateTime or DateTime64

Returned value

Returns the month of the given date/time UInt8

Examples

Usage example

SELECT toMonth(toDateTime('2023-04-21 10:20:30'))

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

toMonthNumSinceEpoch

Introduced in: v25.3

Returns amount of months passed from year 1970

Syntax

toMonthNumSinceEpoch(date)

Arguments

date — A date or date with time. Date or DateTime or DateTime64

Returned value

Positive integer

Examples

Example

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

toQuarter

Introduced in: v1.1

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

Syntax

toQuarter(datetime)

Arguments

datetime — Date or date with time to get the quarter of the year from. Date or Date32 or DateTime or DateTime64

Returned value

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

Examples

Usage example

SELECT toQuarter(toDateTime('2023-04-21 10:20:30'))

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

toRelativeDayNum

Introduced in: v1.1

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

date — Date or date with time. Date or DateTime or DateTime64

Returned value

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

Examples

Get relative day numbers

SELECT toRelativeDayNum(toDate('2023-04-01')) - toRelativeDayNum(toDate('2023-01-01'))

┌─minus(toRela⋯3-01-01')))─┐
│                       90 │
└──────────────────────────┘

toRelativeHourNum

Introduced in: v1.1

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

date — Date or date with time. Date or DateTime or DateTime64

Returned value

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

Examples

Get relative hour numbers

SELECT toRelativeHourNum(toDateTime('2023-01-01 12:00:00')) - toRelativeHourNum(toDateTime('2023-01-01 00:00:00')) AS hours_difference

┌─hours_difference─┐
│               12 │
└──────────────────┘

toRelativeMinuteNum

Introduced in: v1.1

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

date — Date or date with time. Date or DateTime or DateTime64

Returned value

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

Examples

Get relative minute numbers

SELECT toRelativeMinuteNum(toDateTime('2023-01-01 00:30:00')) - toRelativeMinuteNum(toDateTime('2023-01-01 00:00:00')) AS minutes_difference

┌─minutes_difference─┐
│                 30 │
└────────────────────┘

toRelativeMonthNum

Introduced in: v1.1

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

date — Date or date with time. Date or DateTime or DateTime64

Returned value

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

Examples

Get relative month numbers

SELECT toRelativeMonthNum(toDate('2023-04-01')) - toRelativeMonthNum(toDate('2023-01-01')) AS months_difference

┌─months_difference─┐
│                 3 │
└───────────────────┘

toRelativeQuarterNum

Introduced in: v1.1

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

date — Date or date with time. Date or DateTime or DateTime64

Returned value

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

Examples

Get relative quarter numbers

SELECT toRelativeQuarterNum(toDate('2023-04-01')) - toRelativeQuarterNum(toDate('2023-01-01')) AS quarters_difference

┌─quarters_difference─┐
│                   1 │
└─────────────────────┘

toRelativeSecondNum

Introduced in: v1.1

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

date — Date or date with time. Date or DateTime or DateTime64

Returned value

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

Examples

Get relative second numbers

SELECT toRelativeSecondNum(toDateTime('2023-01-01 00:01:00')) - toRelativeSecondNum(toDateTime('2023-01-01 00:00:00')) AS seconds_difference

┌─seconds_difference─┐
│                 60 │
└────────────────────┘

toRelativeWeekNum

Introduced in: v1.1

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

date — Date or date with time. Date or DateTime or DateTime64

Returned value

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

Examples

Get relative week numbers

SELECT toRelativeWeekNum(toDate('2023-01-08')) - toRelativeWeekNum(toDate('2023-01-01')) AS weeks_difference

┌─weeks_difference─┐
│                1 │
└──────────────────┘

toRelativeYearNum

Introduced in: v1.1

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

date — Date or date with time. Date or DateTime or DateTime64

Returned value

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

Examples

Get relative year numbers

SELECT toRelativeYearNum('2010-10-01'::DateTime) - toRelativeYearNum('2000-01-01'::DateTime)

┌─minus(toRela⋯ateTime')))─┐
│                       10 │
└──────────────────────────┘

toSecond

Introduced in: v1.1

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

Syntax

toSecond(datetime)

Arguments

datetime — Date or date with time to get the second from. Date or Date32 or DateTime or DateTime64

Returned value

Returns the second in the minute (0 - 59) of the given Date or DateTime value UInt8

Examples

Usage example

SELECT toSecond(toDateTime('2023-04-21 10:20:30'))

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

toStartOfDay

Introduced in: v1.1

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

Note

The return type can be configured by setting enable_extended_results_for_datetime_functions.

Syntax

toStartOfDay(datetime)

Arguments

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

Returned value

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

Examples

Round down to the start of the day

SELECT toStartOfDay(toDateTime('2023-04-21 10:20:30'))

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

toStartOfFifteenMinutes

Introduced in: v1.1

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

Note

The return type can be configured by setting enable_extended_results_for_datetime_functions.

Syntax

toStartOfFifteenMinutes(datetime)

Arguments

datetime — A date or date with time to round. DateTime or DateTime64

Returned value

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

Examples

Example

SELECT
    toStartOfFifteenMinutes(toDateTime('2023-04-21 10:17:00')),
    toStartOfFifteenMinutes(toDateTime('2023-04-21 10:20:00')),
    toStartOfFifteenMinutes(toDateTime('2023-04-21 10:23:00'))
FORMAT Vertical

Row 1:
──────
toStartOfFifteenMinutes(toDateTime('2023-04-21 10:17:00')): 2023-04-21 10:15:00
toStartOfFifteenMinutes(toDateTime('2023-04-21 10:20:00')): 2023-04-21 10:15:00
toStartOfFifteenMinutes(toDateTime('2023-04-21 10:23:00')): 2023-04-21 10:15:00

toStartOfFiveMinutes

Introduced in: v22.6

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

Note

The return type can be configured by setting enable_extended_results_for_datetime_functions.

Syntax

toStartOfFiveMinutes(datetime)

Arguments

datetime — A date with time to round. DateTime or DateTime64

Returned value

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

Examples

Example

SELECT
    toStartOfFiveMinutes(toDateTime('2023-04-21 10:17:00')),
    toStartOfFiveMinutes(toDateTime('2023-04-21 10:20:00')),
    toStartOfFiveMinutes(toDateTime('2023-04-21 10:23:00'))
FORMAT Vertical

Row 1:
──────
toStartOfFiveMinutes(toDateTime('2023-04-21 10:17:00')): 2023-04-21 10:15:00
toStartOfFiveMinutes(toDateTime('2023-04-21 10:20:00')): 2023-04-21 10:20:00
toStartOfFiveMinutes(toDateTime('2023-04-21 10:23:00')): 2023-04-21 10:20:00

toStartOfHour

Introduced in: v1.1

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

Note

The return type can be configured by setting enable_extended_results_for_datetime_functions.

Syntax

toStartOfHour(datetime)

Arguments

datetime — A date with time to round. DateTime or DateTime64

Returned value

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

Examples

Round down to the start of the hour

SELECT
    toStartOfHour(toDateTime('2023-04-21 10:20:30'));

┌─────────────────res─┬─toTypeName(res)─┐
│ 2023-04-21 10:00:00 │ DateTime        │
└─────────────────────┴─────────────────┘

toStartOfISOYear

Introduced in: v1.1

Rounds down a date or date with time to the first day of the ISO year, which can be different than a regular year. See ISO week date.

Note

The return type can be configured by setting enable_extended_results_for_datetime_functions.

Syntax

toStartOfISOYear(value)

Arguments

value — The date or date with time to round down to the first day of the ISO year. Date or Date32 or DateTime or DateTime64

Returned value

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

Examples

Round down to the first day of the ISO year

SELECT toStartOfISOYear(toDateTime('2023-04-21 10:20:30'))

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

toStartOfInterval

Introduced in: v20.1

This function generalizes other toStartOf*() functions with toStartOfInterval(date_or_date_with_time, INTERVAL x unit [, time_zone]) syntax.

For example,

toStartOfInterval(t, INTERVAL 1 YEAR) returns the same as toStartOfYear(t),
toStartOfInterval(t, INTERVAL 1 MONTH) returns the same as toStartOfMonth(t),
toStartOfInterval(t, INTERVAL 1 DAY) returns the same as toStartOfDay(t),
toStartOfInterval(t, INTERVAL 15 MINUTE) returns the same as toStartOfFifteenMinutes(t).

The calculation is performed relative to specific points in time:

Interval	Start
YEAR	year 0
QUARTER	1900 Q1
MONTH	1900 January
WEEK	1970, 1st week (01-05)
DAY	1970-01-01
HOUR	(*)
MINUTE	1970-01-01 00:00:00
SECOND	1970-01-01 00:00:00
MILLISECOND	1970-01-01 00:00:00
MICROSECOND	1970-01-01 00:00:00
NANOSECOND	1970-01-01 00:00:00
(*) hour intervals are special: the calculation is always performed relative to 00:00:00 (midnight) of the current day. As a result, only
hour values between 1 and 23 are useful.

If unit WEEK was specified, toStartOfInterval assumes that weeks start on Monday. Note that this behavior is different from that of function toStartOfWeek in which weeks start by default on Sunday.

The second overload emulates TimescaleDB's time_bucket() function, respectively PostgreSQL's date_bin() function.

Syntax

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

Arguments

value — Date or date with time value to round down. Date or DateTime or DateTime64
x — Interval length number. - unit — Interval unit: YEAR, QUARTER, MONTH, WEEK, DAY, HOUR, MINUTE, SECOND, MILLISECOND, MICROSECOND, NANOSECOND. - time_zone — Optional. Time zone name as a string. - origin — Optional. Origin point for calculation (second overload only).

Returned value

Returns the start of the interval containing the input value. DateTime

Examples

Basic interval rounding

SELECT toStartOfInterval(toDateTime('2023-01-15 14:30:00'), INTERVAL 1 MONTH)

┌─toStartOfInt⋯alMonth(1))─┐
│               2023-01-01 │
└──────────────────────────┘

Using origin point

SELECT toStartOfInterval(toDateTime('2023-01-01 14:45:00'), INTERVAL 1 MINUTE, toDateTime('2023-01-01 14:35:30'))

┌─toStartOfInt⋯14:35:30'))─┐
│      2023-01-01 14:44:30 │
└──────────────────────────┘

toStartOfMicrosecond

Introduced in: v22.6

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

Syntax

toStartOfMicrosecond(datetime, [timezone])

Arguments

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

Returned value

Input value with sub-microseconds DateTime64

Examples

Query without timezone

WITH toDateTime64('2020-01-01 10:20:30.999999999', 9) AS dt64
SELECT toStartOfMicrosecond(dt64);

┌────toStartOfMicrosecond(dt64)─┐
│ 2020-01-01 10:20:30.999999000 │
└───────────────────────────────┘

Query with timezone

WITH toDateTime64('2020-01-01 10:20:30.999999999', 9) AS dt64
SELECT toStartOfMicrosecond(dt64, 'Asia/Istanbul');

┌─toStartOfMicrosecond(dt64, 'Asia/Istanbul')─┐
│               2020-01-01 12:20:30.999999000 │
└─────────────────────────────────────────────┘

toStartOfMillisecond

Introduced in: v22.6

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

Syntax

toStartOfMillisecond(datetime, [timezone])

Arguments

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

Returned value

Input value with sub-milliseconds. DateTime64

Examples

Query without timezone

WITH toDateTime64('2020-01-01 10:20:30.999999999', 9) AS dt64
SELECT toStartOfMillisecond(dt64);

┌────toStartOfMillisecond(dt64)─┐
│ 2020-01-01 10:20:30.999000000 │
└───────────────────────────────┘

Query with timezone

WITH toDateTime64('2020-01-01 10:20:30.999999999', 9) AS dt64
SELECT toStartOfMillisecond(dt64, 'Asia/Istanbul');

┌─toStartOfMillisecond(dt64, 'Asia/Istanbul')─┐
│               2020-01-01 12:20:30.999000000 │
└─────────────────────────────────────────────┘

toStartOfMinute

Introduced in: v1.1

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

Note

The return type can be configured by setting enable_extended_results_for_datetime_functions.

Syntax

toStartOfMinute(datetime)

Arguments

datetime — A date with time to round. DateTime or DateTime64

Returned value

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

Examples

Round down to the start of the minute

SELECT
    toStartOfMinute(toDateTime('2023-04-21 10:20:30')),
    toStartOfMinute(toDateTime64('2023-04-21 10:20:30.5300', 8))
FORMAT Vertical

Row 1:
──────
toStartOfMinute(toDateTime('2023-04-21 10:20:30')):           2023-04-21 10:20:00
toStartOfMinute(toDateTime64('2023-04-21 10:20:30.5300', 8)): 2023-04-21 10:20:00

toStartOfMonth

Introduced in: v1.1

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

Note

The return type can be configured by setting enable_extended_results_for_datetime_functions.

Syntax

toStartOfMonth(value)

Arguments

value — The date or date with time to round down to the first day of the month. Date or Date32 or DateTime or DateTime64

Returned value

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

Examples

Round down to the first day of the month

SELECT toStartOfMonth(toDateTime('2023-04-21 10:20:30'))

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

toStartOfNanosecond

Introduced in: v22.6

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

Syntax

toStartOfNanosecond(datetime, [timezone])

Arguments

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

Returned value

Input value with nanoseconds. DateTime64

Examples

Query without timezone

WITH toDateTime64('2020-01-01 10:20:30.999999999', 9) AS dt64
SELECT toStartOfNanosecond(dt64);

┌─────toStartOfNanosecond(dt64)─┐
│ 2020-01-01 10:20:30.999999999 │
└───────────────────────────────┘

Query with timezone

WITH toDateTime64('2020-01-01 10:20:30.999999999', 9) AS dt64
SELECT toStartOfNanosecond(dt64, 'Asia/Istanbul');

┌─toStartOfNanosecond(dt64, 'Asia/Istanbul')─┐
│              2020-01-01 12:20:30.999999999 │
└────────────────────────────────────────────┘

toStartOfQuarter

Introduced in: v1.1

Rounds down a date or date with time to the first day of the quarter. The first day of the quarter is either 1 January, 1 April, 1 July, or 1 October.

Note

The return type can be configured by setting enable_extended_results_for_datetime_functions.

Syntax

toStartOfQuarter(value)

Arguments

value — The date or date with time to round down to the first day of the quarter. Date or Date32 or DateTime or DateTime64

Returned value

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

Examples

Round down to the first day of the quarter

SELECT toStartOfQuarter(toDateTime('2023-04-21 10:20:30'))

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

toStartOfSecond

Introduced in: v20.5

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

Syntax

toStartOfSecond(datetime, [timezone])

Arguments

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

Returned value

Returns the input value without sub-seconds. DateTime64

Examples

Query without timezone

WITH toDateTime64('2020-01-01 10:20:30.999', 3) AS dt64
SELECT toStartOfSecond(dt64);

┌───toStartOfSecond(dt64)─┐
│ 2020-01-01 10:20:30.000 │
└─────────────────────────┘

Query with timezone

WITH toDateTime64('2020-01-01 10:20:30.999', 3) AS dt64
SELECT toStartOfSecond(dt64, 'Asia/Istanbul');

┌─toStartOfSecond(dt64, 'Asia/Istanbul')─┐
│                2020-01-01 13:20:30.000 │
└────────────────────────────────────────┘

toStartOfTenMinutes

Introduced in: v20.1

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

Note

The return type can be configured by setting enable_extended_results_for_datetime_functions.

Syntax

toStartOfTenMinutes(datetime)

Arguments

datetime — A date with time. DateTime or DateTime64

Returned value

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

Examples

Example

SELECT
    toStartOfTenMinutes(toDateTime('2023-04-21 10:17:00')),
    toStartOfTenMinutes(toDateTime('2023-04-21 10:20:00')),
    toStartOfTenMinutes(toDateTime('2023-04-21 10:23:00'))
FORMAT Vertical

Row 1:
──────
toStartOfTenMinutes(toDateTime('2023-04-21 10:17:00')): 2023-04-21 10:10:00
toStartOfTenMinutes(toDateTime('2023-04-21 10:20:00')): 2023-04-21 10:20:00
toStartOfTenMinutes(toDateTime('2023-04-21 10:23:00')): 2023-04-21 10:20:00

toStartOfWeek

Introduced in: v20.1

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

Note

The return type can be configured by setting enable_extended_results_for_datetime_functions.

Syntax

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

Arguments

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

Returned value

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

Examples

Round down to the nearest Sunday or Monday

SELECT
        toStartOfWeek(toDateTime('2023-04-21 10:20:30')), /* a Friday */
        toStartOfWeek(toDateTime('2023-04-21 10:20:30'), 1), /* a Friday */
        toStartOfWeek(toDate('2023-04-24')), /* a Monday */
        toStartOfWeek(toDate('2023-04-24'), 1) /* a Monday */
    FORMAT Vertical

Row 1:
    ──────
    toStartOfWeek(toDateTime('2023-04-21 10:20:30')):      2023-04-17
    toStartOfWeek(toDateTime('2023-04-21 10:20:30'), 1):   2023-04-17
    toStartOfWeek(toDate('2023-04-24')):                   2023-04-24
    toStartOfWeek(toDate('2023-04-24'), 1):                2023-04-24

toStartOfYear

Introduced in: v1.1

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

Note

The return type can be configured by setting enable_extended_results_for_datetime_functions.

Syntax

toStartOfYear(value)

Arguments

value — The date or date with time to round down. Date or Date32 or DateTime or DateTime64

Returned value

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

Examples

Round down to the first day of the year

SELECT toStartOfYear(toDateTime('2023-04-21 10:20:30'))

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

toTimeWithFixedDate

Introduced in: v1.1

Extracts the time component of a date or date with time. The returned result is an offset to a fixed point in time, currently 1970-01-02, but the exact point in time is an implementation detail which may change in future.

toTime should therefore not be used standalone. The main purpose of the function is to calculate the time difference between two dates or dates with time, e.g., toTime(dt1) - toTime(dt2).

Syntax

toTime(date[, timezone])

Arguments

date — Date to convert to a time. Date or DateTime or DateTime64
timezone — Optional. Timezone for the returned value. String

Returned value

Returns the time component of a date or date with time in the form of an offset to a fixed point in time (selected as 1970-01-02, currently). DateTime

Examples

Calculate the time difference between two dates

SELECT toTime('2025-06-15 12:00:00'::DateTime) - toTime('2024-05-10 11:00:00'::DateTime) AS result, toTypeName(result)

┌─result─┬─toTypeName(result)─┐
│   3600 │ Int32              │
└────────┴────────────────────┘

toTimezone

Introduced in: v1.1

Converts a DateTime or DateTime64 to the specified time zone. The internal value (number of unix seconds) of the data doesn't change. Only the value's time zone attribute and the value's string representation changes.

Syntax

toTimeZone(datetime, timezone)

Arguments

date — The value to convert. DateTime or DateTime64
timezone — The target time zone name. String

Returned value

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

Examples

Usage example

SELECT toDateTime('2019-01-01 00:00:00', 'UTC') AS time_utc,
toTypeName(time_utc) AS type_utc,
toInt32(time_utc) AS int32utc,
toTimeZone(time_utc, 'Asia/Yekaterinburg') AS time_yekat,
toTypeName(time_yekat) AS type_yekat,
toInt32(time_yekat) AS int32yekat,
toTimeZone(time_utc, 'US/Samoa') AS time_samoa,
toTypeName(time_samoa) AS type_samoa,
toInt32(time_samoa) AS int32samoa
FORMAT Vertical;

Row 1:
──────
time_utc:   2019-01-01 00:00:00
type_utc:   DateTime('UTC')
int32utc:   1546300800
time_yekat: 2019-01-01 05:00:00
type_yekat: DateTime('Asia/Yekaterinburg')
int32yekat: 1546300800
time_samoa: 2018-12-31 13:00:00
type_samoa: DateTime('US/Samoa')
int32samoa: 1546300800

toUTCTimestamp

Introduced in: v23.8

Converts a date or date with time value from one time zone to UTC timezone timestamp. This function is mainly included for compatibility with Apache Spark and similar frameworks.

Syntax

toUTCTimestamp(datetime, time_zone)

Arguments

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

Returned value

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

Examples

Convert timezone to UTC

SELECT toUTCTimestamp(toDateTime('2023-03-16'), 'Asia/Shanghai')

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

toUnixTimestamp

Introduced in: v1.1

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

date — Value to convert. Date or Date32 or DateTime or DateTime64 or String
timezone — Optional.Timezone to use for conversion. If not specified, the server's timezone is used. String

Returned value

Returns the Unix timestamp as UInt32

Examples

Usage example

SELECT
'2017-11-05 08:07:47' AS dt_str,
toUnixTimestamp(dt_str) AS from_str,
toUnixTimestamp(dt_str, 'Asia/Tokyo') AS from_str_tokyo,
toUnixTimestamp(toDateTime(dt_str)) AS from_datetime,
toUnixTimestamp(toDateTime64(dt_str, 0)) AS from_datetime64,
toUnixTimestamp(toDate(dt_str)) AS from_date,
toUnixTimestamp(toDate32(dt_str)) AS from_date32
FORMAT Vertical;

Row 1:
──────
dt_str:          2017-11-05 08:07:47
from_str:        1509869267
from_str_tokyo:  1509836867
from_datetime:   1509869267
from_datetime64: 1509869267
from_date:       1509840000
from_date32:     1509840000

toWeek

Introduced in: v20.1

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.

Mode	First day of week	Range	Week 1 is the first week ...
0	Sunday	0-53	with a Sunday in this year
1	Monday	0-53	with 4 or more days this year
2	Sunday	1-53	with a Sunday in this year
3	Monday	1-53	with 4 or more days this year
4	Sunday	0-53	with 4 or more days this year
5	Monday	0-53	with a Monday in this year
6	Sunday	1-53	with 4 or more days this year
7	Monday	1-53	with a Monday in this year
8	Sunday	1-53	contains January 1
9	Monday	1-53	contains January 1

For mode values with a meaning of "with 4 or more days this year," weeks are numbered according to ISO 8601:1988:

If the week containing January 1 has 4 or more days in the new year, it is week 1.
Otherwise, it is the last week of the previous year, and the next week is week 1.

For mode values with a meaning of "contains January 1", the week contains January 1 is week 1. It does not matter how many days in the new year the week contained, even if it contained only one day. I.e. if the last week of December contains January 1 of the next year, it will be week 1 of the next year.

The first argument can also be specified as String in a format supported by parseDateTime64BestEffort(). Support for string arguments exists only for reasons of compatibility with MySQL which is expected by certain 3rd party tools. As string argument support may in future be made dependent on new MySQL-compatibility settings and because string parsing is generally slow, it is recommended to not use it.

Syntax

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

Arguments

datetime — Date or date with time to get the week number from. Date or DateTime
mode — Optional. A mode 0 to 9 determines the first day of the week and the range of the week number. Default 0. - time_zone — Optional. Time zone. String

Returned value

Returns the week number according to the specified mode. UInt32

Examples

Get week numbers with different modes

SELECT toDate('2016-12-27') AS date, toWeek(date) AS week0, toWeek(date,1) AS week1, toWeek(date,9) AS week9

┌───────date─┬─week0─┬─week1─┬─week9─┐
│ 2016-12-27 │    52 │    52 │     1 │
└────────────┴───────┴───────┴───────┘

toYYYYMM

Introduced in: v1.1

Converts a date or date with time to a UInt32 number containing the year and month number (YYYY * 100 + MM). Accepts a second optional timezone argument. If provided, the timezone must be a string constant.

This function is the opposite of function YYYYMMDDToDate().

Syntax

toYYYYMM(datetime[, timezone])

Arguments

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

Returned value

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

Examples

Convert current date to YYYYMM format

SELECT toYYYYMM(now(), 'US/Eastern')

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

toYYYYMMDD

Introduced in: v1.1

Converts a date or date with time to a UInt32 number containing the year and month number (YYYY * 10000 + MM * 100 + DD). Accepts a second optional timezone argument. If provided, the timezone must be a string constant.

Syntax

toYYYYMMDD(datetime[, timezone])

Arguments

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

Returned value

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

Examples

Convert current date to YYYYMMDD format

SELECT toYYYYMMDD(now(), 'US/Eastern')

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

toYYYYMMDDhhmmss

Introduced in: v1.1

Converts a date or date with time to a UInt64 number containing the year and month number (YYYY * 10000000000 + MM * 100000000 + DD * 1000000 + hh * 10000 + mm * 100 + ss). Accepts a second optional timezone argument. If provided, the timezone must be a string constant.

Syntax

toYYYYMMDDhhmmss(datetime[, timezone])

Arguments

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

Returned value

Returns a UInt64 number containing the year, month, day, hour, minute and second (YYYY * 10000000000 + MM * 100000000 + DD * 1000000 + hh * 10000 + mm * 100 + ss). UInt64

Examples

Convert current date and time to YYYYMMDDhhmmss format

SELECT toYYYYMMDDhhmmss(now(), 'US/Eastern')

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

toYear

Introduced in: v1.1

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

Syntax

toYear(datetime)

Arguments

datetime — Date or date with time to get the year from. Date or Date32 or DateTime or DateTime64

Returned value

Returns the year of the given Date or DateTime UInt16

Examples

Usage example

SELECT toYear(toDateTime('2023-04-21 10:20:30'))

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

toYearNumSinceEpoch

Introduced in: v25.3

Returns amount of years passed from year 1970

Syntax

toYearNumSinceEpoch(date)

Arguments

date — A date or date with time to convert. Date or DateTime or DateTime64

Returned value

Positive integer

Examples

Example

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

toYearWeek

Introduced in: v20.1

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.

Syntax

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

Arguments

datetime — Date or date with time to get the year and week of. Date or DateTime
mode — Optional. A mode 0 to 9 determines the first day of the week and the range of the week number. Default 0. - timezone — Optional. Time zone. String

Returned value

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

Examples

Get year-week combinations with different modes

SELECT toDate('2016-12-27') AS date, toYearWeek(date) AS yearWeek0, toYearWeek(date,1) AS yearWeek1, toYearWeek(date,9) AS yearWeek9, toYearWeek(toDate('2022-01-01')) AS prev_yearWeek

┌───────date─┬─yearWeek0─┬─yearWeek1─┬─yearWeek9─┬─prev_yearWeek─┐
│ 2016-12-27 │    201652 │    201652 │    201701 │        202152 │
└────────────┴───────────┴───────────┴───────────┴───────────────┘

today

Introduced in: v1.1

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

Syntax

today()

Arguments

None. Returned value

Returns the current date Date

Examples

Usage example

SELECT today() AS today, curdate() AS curdate, current_date() AS current_date FORMAT Pretty

┏━━━━━━━━━━━━┳━━━━━━━━━━━━┳━━━━━━━━━━━━━━┓
┃      today ┃    curdate ┃ current_date ┃
┡━━━━━━━━━━━━╇━━━━━━━━━━━━╇━━━━━━━━━━━━━━┩
│ 2025-03-03 │ 2025-03-03 │   2025-03-03 │
└────────────┴────────────┴──────────────┘

yesterday

Introduced in: v1.1

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

SELECT yesterday();
SELECT today() - 1;

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

UTCTimestamp​

YYYYMMDDToDate​

YYYYMMDDToDate32​

YYYYMMDDhhmmssToDateTime​

YYYYMMDDhhmmssToDateTime64​

addDate​

addDays​

addHours​

addInterval​

addMicroseconds​

addMilliseconds​

addMinutes​

addMonths​

addNanoseconds​

addQuarters​

addSeconds​

addTupleOfIntervals​

addWeeks​

addYears​

age​

changeDay​

changeHour​

changeMinute​

changeMonth​

changeSecond​

changeYear​

dateName​

dateTrunc​

formatDateTime​

formatDateTimeInJodaSyntax​

fromDaysSinceYearZero​

fromDaysSinceYearZero32​

fromModifiedJulianDay​

fromModifiedJulianDayOrNull​

fromUTCTimestamp​

fromUnixTimestamp​

fromUnixTimestampInJodaSyntax​

makeDate​

makeDate32​

makeDateTime​

makeDateTime64​

monthName​

now​

now64​

nowInBlock​

nowInBlock64​

serverTimezone​

subDate​

subtractDays​

subtractHours​

subtractInterval​

subtractMicroseconds​

subtractMilliseconds​

subtractMinutes​

subtractMonths​

subtractNanoseconds​

subtractQuarters​

subtractSeconds​

subtractTupleOfIntervals​

subtractWeeks​

subtractYears​

timeDiff​

timeSlot​

timeSlots​

timestamp​

timezone​

timezoneOf​

timezoneOffset​

toDayOfMonth​

toDayOfWeek​

toDayOfYear​

toDaysSinceYearZero​

toHour​

toISOYear​

toLastDayOfMonth​

toLastDayOfWeek​

toMillisecond​

toMinute​

toModifiedJulianDay​

toModifiedJulianDayOrNull​

UTCTimestamp

YYYYMMDDToDate

YYYYMMDDToDate32

YYYYMMDDhhmmssToDateTime

YYYYMMDDhhmmssToDateTime64

addDate

addDays

addHours

addInterval

addMicroseconds

addMilliseconds

addMinutes

addMonths

addNanoseconds

addQuarters

addSeconds

addTupleOfIntervals

addWeeks

addYears

age

changeDay

changeHour

changeMinute

changeMonth

changeSecond

changeYear

dateName

dateTrunc

formatDateTime

formatDateTimeInJodaSyntax

fromDaysSinceYearZero

fromDaysSinceYearZero32

fromModifiedJulianDay

fromModifiedJulianDayOrNull

fromUTCTimestamp

fromUnixTimestamp

fromUnixTimestampInJodaSyntax

makeDate

makeDate32

makeDateTime

makeDateTime64

monthName

now

now64

nowInBlock

nowInBlock64

serverTimezone

subDate

subtractDays

subtractHours

subtractInterval

subtractMicroseconds

subtractMilliseconds

subtractMinutes

subtractMonths

subtractNanoseconds

subtractQuarters

subtractSeconds

subtractTupleOfIntervals

subtractWeeks

subtractYears

timeDiff

timeSlot

timeSlots

timestamp

timezone

timezoneOf

timezoneOffset

toDayOfMonth

toDayOfWeek

toDayOfYear

toDaysSinceYearZero

toHour

toISOYear

toLastDayOfMonth

toLastDayOfWeek

toMillisecond

toMinute

toModifiedJulianDay

toModifiedJulianDayOrNull