Functions for working with dates and times

Most functions in this section accept an optional time zone argument, for example Europe/Amsterdam. In this case, the time zone is the specified one instead of the local or 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 │
└─────────────────────┴────────────┴────────────┴─────────────────────┘

makeDate

Constructs a Date value from individual year, month, and day components, or from a year and a day-of-year number.

Syntax

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

Alias:

  • MAKEDATE(year, month, day);
  • MAKEDATE(year, day_of_year);

Arguments

Returns

  • A date created from the arguments. Date.

Example

Create a Date from a year, month and day:

SELECT makeDate(2023, 2, 28) AS Date

Result:

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

Create a Date from a year and day of year argument:

SELECT makeDate(2023, 42) AS Date

Result:

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

makeDate32

Generates a Date32 value from specified year, month, and day, or from a year and a day-of-year.

Syntax

makeDate32(year, [month,] day)

Arguments

If month is omitted then day should take a value between 1 and 365, otherwise it should take a value between 1 and 31.

Returns

  • A date created from the arguments. Date32.

Example

Create a date from a year, month, and day:

SELECT makeDate32(2024, 1, 1)

Result:

2024-01-01

Create a Date from a year and day of year:

SELECT makeDate32(2024, 100)

Result:

2024-04-09

makeDateTime

Assembles a DateTime value from its constituent year, month, day, hour, minute, and second parts.

Syntax

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

Arguments

Returns

  • A date with time created from the arguments. DateTime.

Example

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

Result:

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

makeDateTime64

Constructs a DateTime64 value using provided year, month, day, hour, minute, and second, with an optional sub-second precision.

Syntax

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

Arguments

Returns

  • A date and time created from the supplied arguments. DateTime64.

Example

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

timestamp

Converts a date or datetime expression into a DateTime64(6) value. An optional second argument can specify a time to add to the converted value.

Syntax

timestamp(expr[, expr_time])

Alias: TIMESTAMP

Arguments

  • expr: Date or date with time. String.
  • expr_time: Optional parameter. Time to add. String.

Returns

Example

SELECT timestamp('2023-12-31') as ts

Result:

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

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

Result:

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

timeZone

Retrieves the timezone configured for the current session.

Syntax

timeZone()

Alias: timezone.

Returns

Example

SELECT timezone()

Result:

┌─timezone()─────┐
│ America/Denver │
└────────────────┘

serverTimeZone

Provides the timezone configured for the server.

Syntax

serverTimeZone()

Alias: serverTimezone.

Returns

Example

SELECT serverTimeZone()

Result:

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

toTimeZone

Changes the timezone attribute of a DateTime or DateTime64 value without altering its underlying Unix timestamp. This affects how the time is displayed.

Syntax

toTimezone(value, timezone)

Alias: toTimezone.

Arguments

  • value: Time or date and time. DateTime64.
  • timezone: Timezone for the returned value. String. This argument is a constant, because toTimezone changes the timezone of a column (timezone is an attribute of DateTime* types).

Returns

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

Result:

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

timeZoneOf

Extracts the timezone name associated with a DateTime or DateTime64 value.

Syntax

timeZoneOf(value)

Alias: timezoneOf.

Arguments

Returns

Example

SELECT timezoneOf(now())

Result:

┌─timezoneOf(now())─┐
│ Etc/UTC           │
└───────────────────┘

timeZoneOffset

Calculates the offset in seconds from UTC for a given DateTime or DateTime64 value, considering daylight saving time and historical timezone changes.

Syntax

timeZoneOffset(value)

Alias: timezoneOffset.

Arguments

Returns

  • Offset from UTC in seconds. Int32.

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

Result:

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

toYear

Extracts the year component from a date or datetime value.

Syntax

toYear(value)

Alias: YEAR

Arguments

Returns

  • The year of the given date/time. UInt16.

Example

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

Result:

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

toQuarter

Retrieves the quarter of the year (1-4) from a date or datetime value.

Syntax

toQuarter(value)

Alias: QUARTER

Arguments

Returns

  • The quarter of the year (1, 2, 3 or 4) of the given date/time. UInt8.

Example

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

Result:

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

toMonth

Extracts the month number (1-12) from a date or datetime value.

Syntax

toMonth(value)

Alias: MONTH

Arguments

Returns

  • The month of the year (1 - 12) of the given date/time. UInt8.

Example

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

Result:

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

toDayOfYear

Returns the day number within the year (1 to 366) for a given date or datetime value.

Syntax

toDayOfYear(value)

Alias: DAYOFYEAR

Arguments

Returns

  • The day of the year (1 - 366) of the given date/time. UInt16.

Example

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

Result:

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

toDayOfMonth

Extracts the day number within its month (1 to 31) from a date or datetime value.

Syntax

toDayOfMonth(value)

Aliases: DAYOFMONTH, DAY

Arguments

Returns

  • The day of the month (1 - 31) of the given date/time. UInt8.

Example

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

Result:

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

toDayOfWeek

Determines the day of the week for a given date or datetime. You can specify the week's starting day and the numbering range (0-6 or 1-7) using an optional mode argument, and also provide a timezone.

Syntax

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

Alias: DAYOFWEEK.

Arguments

  • t: a Date, Date32, DateTime or DateTime64.
  • mode: determines what the first day of the week is. Possible values are 0, 1, 2 or 3. See the table below for the differences.
  • timezone: optional parameter, it behaves like any other conversion function.
ModeFirst day of weekRange
0Monday1-7: Monday = 1, Tuesday = 2, ..., Sunday = 7
1Monday0-6: Monday = 0, Tuesday = 1, ..., Sunday = 6
2Sunday0-6: Sunday = 0, Monday = 1, ..., Saturday = 6
3Sunday1-7: Sunday = 1, Monday = 2, ..., Saturday = 7

Returns

  • The day of the week (1-7), depending on the chosen mode, of the given date/time. UInt8.

Example

The following date is April 21, 2023, which was a Friday:

SELECT
    toDayOfWeek(toDateTime('2023-04-21')),
    toDayOfWeek(toDateTime('2023-04-21'), 1)

Result:

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

toHour

Extracts the hour component (0-23) from a datetime value. This function accounts for daylight saving time changes based on typical rules.

Syntax

toHour(value)

Alias: HOUR

Arguments

Returns

  • The hour of the day (0 - 23) of the given date/time. UInt8.

Example

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

Result:

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

toMinute

Extracts the minute component (0-59) from a datetime value.

Syntax

toMinute(value)

Alias: MINUTE

Arguments

Returns

  • The minute of the hour (0 - 59) of the given date/time. UInt8.

Example

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

Result:

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

toSecond

Extracts the second component (0-59) from a datetime value, ignoring leap seconds.

Syntax

toSecond(value)

Alias: SECOND

Arguments

Returns

  • The second in the minute (0 - 59) of the given date/time. UInt8.

Example

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

Result:

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

toMillisecond

Extracts the millisecond component (0-999) from a datetime value.

Syntax

toMillisecond(value)

Alias: MILLISECOND

Arguments

Returns

  • The millisecond in the minute (0 - 59) of the given date/time. UInt16.

Example

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

Result:

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

toUnixTimestamp

Converts a date, datetime, or string representation of a date/time into a Unix timestamp (seconds since epoch) as a UInt32. An optional timezone can be specified for string inputs.

Syntax

toUnixTimestamp(date)
toUnixTimestamp(str, [timezone])

Returns

  • Returns the unix timestamp. UInt32.

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

Result:

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

The return type of toStartOf*, toLastDayOf*, toMonday, timeSlot functions described below is determined by the configuration parameter enable_extended_results_for_datetime_functions which is 0 by default.

Behavior for

  • enable_extended_results_for_datetime_functions = 0:
    • Functions toStartOfYear, toStartOfISOYear, toStartOfQuarter, toStartOfMonth, toStartOfWeek, toLastDayOfWeek, toLastDayOfMonth, toMonday return Date or DateTime.
    • Functions toStartOfDay, toStartOfHour, toStartOfFifteenMinutes, toStartOfTenMinutes, toStartOfFiveMinutes, toStartOfMinute, timeSlot return DateTime. Though these functions can take values of the extended types Date32 and DateTime64 as an argument, passing them a time outside the normal range (year 1970 to 2149 for Date / 2106 for DateTime) will produce wrong results.
  • enable_extended_results_for_datetime_functions = 1:
    • Functions toStartOfYear, toStartOfISOYear, toStartOfQuarter, toStartOfMonth, toStartOfWeek, toLastDayOfWeek, toLastDayOfMonth, toMonday return Date or DateTime if their argument is a Date or DateTime, and they return Date32 or DateTime64 if their argument is a Date32 or DateTime64.
    • Functions toStartOfDay, toStartOfHour, toStartOfFifteenMinutes, toStartOfTenMinutes, toStartOfFiveMinutes, toStartOfMinute, timeSlot return DateTime if their argument is a Date or DateTime, and they return DateTime64 if their argument is a Date32 or DateTime64.

toStartOfYear

Truncates a date or datetime value to the beginning of its respective year.

Syntax

toStartOfYear(value)

Arguments

Returns

  • The first day of the year of the input date/time. Date.

Example

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

Result:

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

toStartOfISOYear

Truncates a date or datetime value to the first day of its ISO year, which may differ from the calendar year start.

Syntax

toStartOfISOYear(value)

Arguments

Returns

  • The first day of the year of the input date/time. Date.

Example

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

Result:

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

toStartOfQuarter

Truncates a date or datetime value to the first day of its quarter (January 1, April 1, July 1, or October 1).

Syntax

toStartOfQuarter(value)

Arguments

Returns

  • The first day of the quarter of the given date/time. Date.

Example

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

Result:

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

toStartOfMonth

Truncates a date or datetime value to the first day of its month.

Syntax

toStartOfMonth(value)

Arguments

Returns

  • The first day of the month of the given date/time. Date.

Example

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

Result:

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

The behavior of parsing incorrect dates is implementation specific. The implementation may return zero date, throw an exception, or do "natural" overflow.

toLastDayOfMonth

Adjusts a date or datetime value to the last day of its month.

Syntax

toLastDayOfMonth(value)

Alias: LAST_DAY

Arguments

Returns

  • The last day of the month of the given date/time. Date.

Example

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

Result:

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

toMonday

Finds the most recent Monday on or before the given date or datetime.

Syntax

toMonday(value)

Arguments

Returns

  • The date of the nearest Monday on or prior to the given date. Date.

Example

SELECT
    toMonday(toDateTime('2023-04-21 10:20:30')), /* a Friday */
    toMonday(toDate('2023-04-24')) /* already a Monday */

Result:

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

toStartOfWeek

Truncates a date or datetime value to the beginning of its week, allowing specification of whether the week starts on Sunday or Monday.

Syntax

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

Arguments

  • t: a Date, Date32, DateTime or DateTime64.
  • mode: determines the first day of the week as described in the toWeek() function.
  • timezone: Optional parameter, it behaves like any other conversion function.

Returns

  • The date of the nearest Sunday or Monday on or prior to the given date, depending on the mode. Date.

Example

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

Result:

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

toLastDayOfWeek

Adjusts a date or datetime value to the end of its week, allowing specification of whether the week ends on Saturday or Sunday.

Syntax

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

Arguments

  • t: a Date, Date32, DateTime or DateTime64.
  • mode: determines the last day of the week as described in the toWeek function.
  • timezone: Optional parameter, it behaves like any other conversion function.

Returns

  • The date of the nearest Sunday or Monday on or after the given date, depending on the mode. Date.

Example

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-22')), /* a Saturday */
    toLastDayOfWeek(toDate('2023-04-22'), 1) /* a Saturday */
FORMAT Vertical

Result:

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

toStartOfDay

Truncates a datetime value to the very beginning of its day (midnight).

Syntax

toStartOfDay(value)

Arguments

Returns

  • The start of the day of the given date/time. DateTime.

Example

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

Result:

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

toStartOfHour

Truncates a datetime value to the beginning of its hour.

Syntax

toStartOfHour(value)

Arguments

Returns

  • The start of the hour of the given date/time. DateTime.

Example

SELECT
    toStartOfHour(toDateTime('2023-04-21 10:20:30')),
    toStartOfHour(toDateTime64('2023-04-21', 6))

Result:

┌─toStartOfHour(toDateTime('2023-04-21 10:20:30'))─┬─toStartOfHour(toDateTime64('2023-04-21', 6))─┐
│                              2023-04-21 10:00:00 │                          2023-04-21 00:00:00 │
└──────────────────────────────────────────────────┴──────────────────────────────────────────────┘

toStartOfMinute

Truncates a datetime value to the beginning of its minute.

Syntax

toStartOfMinute(value)

Arguments

Returns

  • The start of the minute of the given date/time. DateTime.

Example

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

Result:

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

toStartOfSecond

Removes any fractional second components from a DateTime64 value, effectively rounding down to the nearest second.

Syntax

toStartOfSecond(value, [timezone])

Arguments

  • value: Date and time. DateTime64.
  • timezone: Timezone for the returned value (optional). If not specified, the function uses the timezone of the value parameter. String.

Returns

Example

Query without timezone:

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

Result:

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

Result:

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

toStartOfMillisecond

Truncates a DateTime64 value to the beginning of its millisecond.

Syntax

toStartOfMillisecond(value, [timezone])

Arguments

  • value: Date and time. DateTime64.
  • timezone: Timezone for the returned value (optional). If not specified, the function uses the timezone of the value parameter. String.

Returns

Example

Query without timezone:

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

Result:

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

Result:

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

toStartOfMicrosecond

Truncates a DateTime64 value to the beginning of its microsecond.

Syntax

toStartOfMicrosecond(value, [timezone])

Arguments

  • value: Date and time. DateTime64.
  • timezone: Timezone for the returned value (optional). If not specified, the function uses the timezone of the value parameter. String.

Returns

Example

Query without timezone:

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

Result:

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

Result:

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

toStartOfNanosecond

Truncates a DateTime64 value to the beginning of its nanosecond.

Syntax

toStartOfNanosecond(value, [timezone])

Arguments

  • value: Date and time. DateTime64.
  • timezone: Timezone for the returned value (optional). If not specified, the function uses the timezone of the value parameter. String.

Returns

Example

Query without timezone:

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

Result:

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

Result:

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

toStartOfFiveMinutes

Truncates a datetime value to the start of the nearest five-minute interval.

Syntax

toStartOfFiveMinutes(value)

Arguments

Returns

  • The start of the five-minute interval of the given date/time. DateTime.

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

Result:

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

toStartOfTenMinutes

Truncates a datetime value to the start of the nearest ten-minute interval.

Syntax

toStartOfTenMinutes(value)

Arguments

Returns

  • The start of the ten-minute interval of the given date/time. DateTime.

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

Result:

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

toStartOfFifteenMinutes

Truncates a datetime value to the start of the nearest fifteen-minute interval.

Syntax

toStartOfFifteenMinutes(value)

Arguments

Returns

  • The start of the fifteen-minute interval of the given date/time. DateTime.

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

Result:

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

toStartOfInterval

Truncates a date or datetime value to the beginning of a specified time interval (e.g., year, month, day, hour). This function offers flexible interval rounding, similar to other toStartOf* functions.

Syntax

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

Aliases: time_bucket, date_bin.

Arguments

  • value: a Date, Date32, DateTime or DateTime64.
  • x: The number of unit intervals. Integer.
  • unit: The time unit for the interval. Possible values: YEAR, QUARTER, MONTH, WEEK, DAY, HOUR, MINUTE, SECOND, MILLISECOND, MICROSECOND, NANOSECOND.
  • origin: An optional reference point for the interval calculation. DateTime.
  • time_zone: Optional Timezone for the returned value.

Returns

Example

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

Result:

┌───toStartOfInterval(...)─┐
│      2023-01-01 14:44:30 │
└──────────────────────────┘

toTime

Extracts the time component from a date or datetime value and sets the date to 1970-01-02.

Syntax

toTime(date[,timezone])

Arguments

Returns

  • DateTime with date equated to 1970-01-02 while preserving the time.

If the date input argument contained sub-second components, they will be dropped in the returned DateTime value with second-accuracy.

Example

Query:

SELECT toTime(toDateTime64('1970-12-10 01:20:30.3000',3)) AS result, toTypeName(result)

Result:

┌──────────────result─┬─toTypeName(result)─┐
│ 1970-01-02 01:20:30 │ DateTime           │
└─────────────────────┴────────────────────┘

toRelativeYearNum

Calculates the number of years that have passed since a predefined historical reference point for a given date or datetime.

Syntax

toRelativeYearNum(date)

Arguments

Returns

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

Example

Query:

SELECT
    toRelativeYearNum(toDate('2002-12-08')) AS y1,
    toRelativeYearNum(toDate('2010-10-26')) AS y2

Result:

┌───y1─┬───y2─┐
│ 2002 │ 2010 │
└──────┴──────┘

toRelativeQuarterNum

Calculates the number of quarters that have passed since a predefined historical reference point for a given date or datetime.

Syntax

toRelativeQuarterNum(date)

Arguments

Returns

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

Example

Query:

SELECT
  toRelativeQuarterNum(toDate('1993-11-25')) AS q1,
  toRelativeQuarterNum(toDate('2005-01-05')) AS q2

Result:

┌───q1─┬───q2─┐
│ 7975 │ 8020 │
└──────┴──────┘

toRelativeMonthNum

Calculates the number of months that have passed since a predefined historical reference point for a given date or datetime.

Syntax

toRelativeMonthNum(date)

Arguments

Returns

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

Example

Query:

SELECT
  toRelativeMonthNum(toDate('2001-04-25')) AS m1,
  toRelativeMonthNum(toDate('2009-07-08')) AS m2

Result:

┌────m1─┬────m2─┐
│ 24016 │ 24115 │
└───────┴───────┘

toRelativeWeekNum

Calculates the number of weeks that have passed since a predefined historical reference point for a given date or datetime.

Syntax

toRelativeWeekNum(date)

Arguments

Returns

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

Example

Query:

SELECT
  toRelativeWeekNum(toDate('2000-02-29')) AS w1,
  toRelativeWeekNum(toDate('2001-01-12')) AS w2

Result:

┌───w1─┬───w2─┐
│ 1574 │ 1619 │
└──────┴──────┘

toRelativeDayNum

Calculates the number of days that have passed since a predefined historical reference point for a given date or datetime.

Syntax

toRelativeDayNum(date)

Arguments

Returns

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

Example

Query:

SELECT
  toRelativeDayNum(toDate('1993-10-05')) AS d1,
  toRelativeDayNum(toDate('2000-09-20')) AS d2

Result:

┌───d1─┬────d2─┐
│ 8678 │ 11220 │
└──────┴───────┘

toRelativeHourNum

Calculates the number of hours that have passed since a predefined historical reference point for a given date or datetime.

Syntax

toRelativeHourNum(date)

Arguments

Returns

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

Example

Query:

SELECT
  toRelativeHourNum(toDateTime('1993-10-05 05:20:36')) AS h1,
  toRelativeHourNum(toDateTime('2000-09-20 14:11:29')) AS h2

Result:

┌─────h1─┬─────h2─┐
│ 208276 │ 269292 │
└────────┴────────┘

toRelativeMinuteNum

Calculates the number of minutes that have passed since a predefined historical reference point for a given date or datetime.

Syntax

toRelativeMinuteNum(date)

Arguments

Returns

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

Example

Query:

SELECT
  toRelativeMinuteNum(toDateTime('1993-10-05 05:20:36')) AS m1,
  toRelativeMinuteNum(toDateTime('2000-09-20 14:11:29')) AS m2

Result:

┌───────m1─┬───────m2─┐
│ 12496580 │ 16157531 │
└──────────┴──────────┘

toRelativeSecondNum

Calculates the number of seconds that have passed since a predefined historical reference point for a given date or datetime.

Syntax

toRelativeSecondNum(date)

Arguments

Returns

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

Example

Query:

SELECT
  toRelativeSecondNum(toDateTime('1993-10-05 05:20:36')) AS s1,
  toRelativeSecondNum(toDateTime('2000-09-20 14:11:29')) AS s2

Result:

┌────────s1─┬────────s2─┐
│ 749794836 │ 969451889 │
└───────────┴───────────┘

toISOYear

Extracts the ISO year number from a date or datetime value.

Syntax

toISOYear(value)

Arguments

Returns

  • The input value converted to a ISO year number. UInt16.

Example

Query:

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

Result:

┌─year1─┬─year2─┐
│  2024 │  2024 │
└───────┴───────┘

toISOWeek

Extracts the ISO week number (1-53) from a date or datetime value.

Syntax

toISOWeek(value)

Arguments

Returns

  • value converted to the current ISO week number. UInt8.

Example

Query:

SELECT
  toISOWeek(toDate('2024/10/02')) AS week1,
  toISOWeek(toDateTime('2024/10/02 01:30:00')) AS week2

Response:

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

toWeek

Returns the week number for a given date or datetime. It supports various modes to define the start day of the week and the numbering scheme (0-53 or 1-53).

Syntax

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

Alias: WEEK

Arguments

  • t: Date or DateTime.
  • mode: Optional parameter, Range of values is [0,9], default is 0.
  • Timezone: Optional parameter, it behaves like any other conversion function.
ModeFirst day of weekRangeWeek 1 is the first week ...
0Sunday0-53with a Sunday in this year
1Monday0-53with 4 or more days this year
2Sunday1-53with a Sunday in this year
3Monday1-53with 4 or more days this year
4Sunday0-53with 4 or more days this year
5Monday0-53with a Monday in this year
6Sunday1-53with 4 or more days this year
7Monday1-53with a Monday in this year
8Sunday1-53contains January 1
9Monday1-53contains January 1

Returns

  • The week number (0-53 or 1-53), depending on the chosen mode, of the given date/time. UInt8.

Example

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

toYearWeek

Combines the year and week number into a single integer (YYYYWW) for a given date. The year component might adjust for weeks spanning across calendar year boundaries.

Syntax

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

Alias: YEARWEEK

Arguments

  • t: Date or DateTime.
  • mode: Optional parameter, Range of values is [0,9], default is 0.
  • Timezone: Optional parameter, it behaves like any other conversion function.

Returns

  • Year and week number combined into a single integer. UInt32.

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.

Example

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

toDaysSinceYearZero

Calculates the total number of days that have elapsed since January 1, year 0, according to the proleptic Gregorian calendar.

Syntax

toDaysSinceYearZero(date[, time_zone])

Alias: TO_DAYS

Arguments

  • date: The date to calculate the number of days passed since year zero from. Date, Date32, DateTime or DateTime64.
  • time_zone: A String type constant value or an expression representing the time zone.

Returns

The number of days passed since date 0000-01-01. UInt32.

Example

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

Result:

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

fromDaysSinceYearZero

Converts a count of days since January 1, year 0, into a Date value in the proleptic Gregorian calendar.

Syntax

fromDaysSinceYearZero(days)

Alias: FROM_DAYS

Arguments

  • days: The number of days passed since year zero. Integer.

Returns

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

Example

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

Result:

┌─fromDaysSinceYearZero(739136)─┬─fromDaysSinceYearZero(toDaysSinceYearZero(toDate('2023-09-08')))─┐
│                    2023-09-08 │                                                       2023-09-08 │
└───────────────────────────────┴──────────────────────────────────────────────────────────────────┘

fromDaysSinceYearZero32

Converts a count of days since January 1, year 0, into a Date32 value.

Syntax

fromDaysSinceYearZero32(days)

Arguments

  • days: The number of days passed since year zero. Integer.

Returns

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

Example

SELECT fromDaysSinceYearZero32(739136)

Result:

┌─fromDaysSinceYearZero32(739136)─┐
│                      2023-09-08 │
└─────────────────────────────────┘

age

Calculates the difference between two date or datetime values in a specified time unit (e.g., hours, days, months). The calculation is precise to the nanosecond.

Syntax

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

Arguments

  • unit: The type of interval for result. String. Possible values:
    • nanosecond, nanoseconds, ns
    • microsecond, microseconds, us, u
    • millisecond, milliseconds, ms
    • second, seconds, ss, s
    • minute, minutes, mi, n
    • hour, hours, hh, h
    • day, days, dd, d
    • week, weeks, wk, ww
    • month, months, mm, m
    • quarter, quarters, qq, q
    • year, years, yyyy, yy
  • startdate: The first time value to subtract (the subtrahend). Date, Date32, DateTime or DateTime64.
  • enddate: The second time value to subtract from (the minuend). Date, Date32, DateTime or DateTime64.
  • timezone: Timezone name (optional). If specified, it's applied to both startdate and enddate. If not specified, timezones of startdate and enddate are used. If they aren't the same, the result is unspecified.

Returns

Difference between enddate and startdate expressed in unit. Int.

Example

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

Result:

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

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

Result:

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

date_diff

Computes the number of interval boundaries (e.g., day, month, year) crossed between two date or datetime values. This function uses relative units for its calculation.

Syntax

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

Aliases: dateDiff, DATE_DIFF, timestampDiff, timestamp_diff, TIMESTAMP_DIFF.

Arguments

  • unit: The type of interval for result. String. Possible values:
    • nanosecond, nanoseconds, ns
    • microsecond, microseconds, us, u
    • millisecond, milliseconds, ms
    • second, seconds, ss, s
    • minute, minutes, mi, n
    • hour, hours, hh, h
    • day, days, dd, d
    • week, weeks, wk, ww
    • month, months, mm, m
    • quarter, quarters, qq, q
    • year, years, yyyy, yy
  • startdate: The first time value to subtract (the subtrahend). Date, Date32, DateTime or DateTime64.
  • enddate: The second time value to subtract from (the minuend). Date, Date32, DateTime or DateTime64.
  • timezone: Timezone name (optional). If specified, it's applied to both startdate and enddate. If not specified, timezones of startdate and enddate are used. If they aren't the same, the result is unspecified.

Returns

Difference between enddate and startdate expressed in unit. Int.

Example

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

Result:

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

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

Result:

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

date_trunc

Truncates a date or datetime value to the beginning of a specified time unit (e.g., hour, day, month, year).

Syntax

date_trunc(unit, value[, timezone])

Alias: dateTrunc.

Arguments

  • unit: The type of interval to truncate the result. String Literal. Possible values:
    • nanosecond - Compatible only with DateTime64
    • microsecond - Compatible only with DateTime64
    • milisecond - Compatible only with DateTime64
    • second
    • minute
    • hour
    • day
    • week
    • month
    • quarter
    • year unit argument is case-insensitive.
  • value: Date and time. Date, Date32, DateTime or DateTime64.
  • timezone: Timezone name for the returned value (optional). If not specified, the function uses the timezone of the value parameter.

Returns

  • Value, truncated to the specified part of date. DateTime.

Example

Query without timezone:

SELECT now(), date_trunc('hour', now())

Result:

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

Query with the specified timezone:

SELECT now(), date_trunc('hour', now(), 'Asia/Istanbul')

Result:

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

date_add

Adds a specified time interval to a date or datetime value.

Syntax

date_add(unit, value, date)

Alternative syntax:

date_add(date, INTERVAL value unit)

Aliases: dateAdd, DATE_ADD.

Arguments

  • unit: The type of interval to add. Note: This isn't a String and must therefore not be quoted. Possible values:
    • second
    • minute
    • hour
    • day
    • week
    • month
    • quarter
    • year
  • value: Value of interval to add. Int.
  • date: The date or date with time to which value is added. Date, Date32, DateTime or DateTime64.

Returns

Date or date with time obtained by adding value, expressed in unit, to date. Date, Date32, DateTime or DateTime64.

Example

SELECT date_add(YEAR, 3, toDate('2018-01-01'))

Result:

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

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

Result:

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

date_sub

Subtracts a specified time interval from a date or datetime value.

Syntax

date_sub(unit, value, date)

Alternative syntax:

date_sub(date, INTERVAL value unit)

Aliases: dateSub, DATE_SUB.

Arguments

  • unit: The type of interval to subtract. Note: This isn't a String and must therefore not be quoted. Possible values:
    • second
    • minute
    • hour
    • day
    • week
    • month
    • quarter
    • year
  • value: Value of interval to subtract. Int.
  • date: The date or date with time from which value is subtracted. Date, Date32, DateTime or DateTime64.

Returns

Date or date with time obtained by subtracting value, expressed in unit, from date. Date, Date32, DateTime or DateTime64.

Example

SELECT date_sub(YEAR, 3, toDate('2018-01-01'))

Result:

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

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

Result:

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

timestamp_add

Adds a specified time interval to a date or datetime value.

Syntax

timestamp_add(date, INTERVAL value unit)

Aliases: timeStampAdd, TIMESTAMP_ADD.

Arguments

  • date: Date or date with time. Date, Date32, DateTime or DateTime64.
  • value: Value of interval to add. Int.
  • unit: The type of interval to add. String. Possible values:
    • second
    • minute
    • hour
    • day
    • week
    • month
    • quarter
    • year

Returns

Date or date with time with the specified value expressed in unit added to date. Date, Date32, DateTime or DateTime64.

Example

select timestamp_add(toDate('2018-01-01'), INTERVAL 3 MONTH)

Result:

┌─plus(toDate('2018-01-01'), toIntervalMonth(3))─┐
│                                     2018-04-01 │
└────────────────────────────────────────────────┘

timestamp_sub

Subtracts a specified time interval from a date or datetime value.

Syntax

timestamp_sub(unit, value, date)

Aliases: timeStampSub, TIMESTAMP_SUB.

Arguments

  • unit: The type of interval to subtract. String. Possible values:
    • second
    • minute
    • hour
    • day
    • week
    • month
    • quarter
    • year
  • value: Value of interval to subtract. Int.
  • date: Date or date with time. Date, Date32, DateTime or DateTime64.

Returns

Date or date with time obtained by subtracting value, expressed in unit, from date. Date, Date32, DateTime or DateTime64.

Example

select timestamp_sub(MONTH, 5, toDateTime('2018-12-18 01:02:03'))

Result:

┌─minus(toDateTime('2018-12-18 01:02:03'), toIntervalMonth(5))─┐
│                                          2018-07-18 01:02:03 │
└──────────────────────────────────────────────────────────────┘

addDate

Adds a specified time interval to a date, datetime, or string representation of a date/time.

Syntax

addDate(date, interval)

Alias: ADDDATE

Arguments

Returns

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

Example

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

Result:

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

subDate

Subtracts a specified time interval from a date, datetime, or string representation of a date/time.

Syntax

subDate(date, interval)

Alias: SUBDATE

Arguments

Returns

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

Example

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

Result:

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

now

Returns the current date and time when the query is analyzed. This value remains constant throughout the query execution.

Syntax

now([timezone])

Alias: current_timestamp.

Arguments

Returns

Example

Query without timezone:

SELECT now()

Result:

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

Query with the specified timezone:

SELECT now('Asia/Istanbul')

Result:

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

now64

Returns the current date and time with sub-second precision when the query is analyzed. This value remains constant throughout the query execution.

Syntax

now64([scale], [timezone])

Arguments

  • scale: Tick size (precision): 10<sup>-precision</sup> seconds. Valid range: [ 0 : 9 ]. Typically, are used - 3 (default) (milliseconds), 6 (microseconds), 9 (nanoseconds).
  • timezone: Timezone name for the returned value (optional).

Returns

  • Current date and time with sub-second precision. DateTime64.

Example

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

Result:

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

nowInBlock

Returns the current date and time at the moment each data block is processed during query execution. Unlike now(), its value can vary between different blocks in a long-running query.

Syntax

nowInBlock([timezone])

Arguments

Returns

  • Current date and time at the moment of processing of each block of data. DateTime.

Example

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

Result:

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

today

Returns the current date when the query is analyzed. This is equivalent to casting the result of now() to a Date.

Syntax

today()

Aliases: curdate, current_date.

Arguments

  • None

Returns

Example

Query:

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

Result

Running the query above on the 3rd of March 2024 would have returned the following response:

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

yesterday

Returns yesterday's date based on the current date at query analysis time.

Syntax

yesterday()

Arguments

  • None

Returns

  • Yesterday's date. Date.

Example

SELECT yesterday()

Result:

┌─yesterday()─┐
│  2024-05-27 │
└─────────────┘

timeSlot

Rounds a datetime value down to the beginning of the nearest 30-minute interval.

Syntax

timeSlot(time[, time_zone])

Arguments

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

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

Returns

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

Example

Query:

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

Result:

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

toYYYYMM

Converts a date or datetime into a UInt32 integer representing the year and month in YYYYMM format. An optional timezone can be specified.

Syntax

toYYYYMM(value[, timezone])

Arguments

  • value: A date or date with time. Date or DateTime.
  • timezone: Optional Timezone for the returned value.

Returns

  • A UInt32 number containing the year and month number (YYYY * 100 + MM).

Example

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

Result:

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

toYYYYMMDD

Converts a date or datetime into a UInt32 integer representing the year, month, and day in YYYYMMDD format. An optional timezone can be specified.

Syntax

toYYYYMMDD(value[, timezone])

Arguments

  • value: A date or date with time. Date or DateTime.
  • timezone: Optional Timezone for the returned value.

Returns

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

Example

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

Result:

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

toYYYYMMDDhhmmss

Converts a date or datetime into a UInt64 integer representing the full timestamp in YYYYMMDDhhmmss format. An optional timezone can be specified.

Syntax

toYYYYMMDDhhmmss(value[, timezone])

Arguments

  • value: A date or date with time. Date or DateTime.
  • timezone: Optional Timezone for the returned value.

Returns

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

Example

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

Result:

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

YYYYMMDDToDate

Converts an integer in YYYYMMDD format into a Date value.

Syntax

YYYYMMDDToDate(yyyymmdd)

Arguments

Returns

  • A date created from the arguments. Date.

Example

SELECT YYYYMMDDToDate(20230911)

Result:

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

YYYYMMDDToDate32

Converts an integer in YYYYMMDD format into a Date32 value.

Syntax

YYYYMMDDToDate32(yyyymmdd)

Arguments

Returns

  • A date created from the arguments. Date32.

Example

SELECT YYYYMMDDToDate32(20230911)

Result:

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

YYYYMMDDhhmmssToDateTime

Converts an integer in YYYYMMDDhhmmss format into a DateTime value. An optional timezone can be specified.

Syntax

YYYYMMDDhhmmssToDateTime(yyyymmddhhmmss[, timezone])

Arguments

  • yyyymmddhhmmss: A number representing the year, month, day, hour, minute, and second. Integer, Float or Decimal.
  • timezone: Optional Timezone for the returned value.

Returns

  • A date with time created from the arguments. DateTime.

Example

SELECT YYYYMMDDhhmmssToDateTime(20230911131415)

Result:

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

YYYYMMDDhhmmssToDateTime64

Converts an integer in YYYYMMDDhhmmss format into a DateTime64 value, with optional timezone and precision.

Syntax

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

Arguments

  • yyyymmddhhmmss: A number representing the year, month, day, hour, minute, and second. Integer, Float or Decimal.
  • timezone: Optional Timezone for the returned value.
  • precision: Optional precision of the sub-second component (0-9). Integer.

Returns

  • A date with time created from the arguments. DateTime64.

Example

SELECT YYYYMMDDhhmmssToDateTime64(20230911131415, 'UTC', 3)

Result:

┌─YYYYMMDDhhmmssToDateTime64(20230911131415, 'UTC', 3)─┐
│                                2023-09-11 13:14:15.000 │
└───────────────────────────────────────────────────────┘

changeYear

Modifies the year component of a date or datetime value to a new specified year.

Syntax

changeYear(date_or_datetime, value)

Arguments

Returns

  • The same type as date_or_datetime.

Example

SELECT changeYear(toDate('1999-01-01'), 2000), changeYear(toDateTime64('1999-01-01 00:00:00.000', 3), 2000)

Result:

┌─changeYear(toDate('1999-01-01'), 2000)─┬─changeYear(toDateTime64('1999-01-01 00:00:00.000', 3), 2000)─┐
│                             2000-01-01 │                                      2000-01-01 00:00:00.000 │
└────────────────────────────────────────┴──────────────────────────────────────────────────────────────┘

changeMonth

Modifies the month component of a date or datetime value to a new specified month.

Syntax

changeMonth(date_or_datetime, value)

Arguments

Returns

  • Returns a value of same type as date_or_datetime.

Example

SELECT changeMonth(toDate('1999-01-01'), 2), changeMonth(toDateTime64('1999-01-01 00:00:00.000', 3), 2)

Result:

┌─changeMonth(toDate('1999-01-01'), 2)─┬─changeMonth(toDateTime64('1999-01-01 00:00:00.000', 3), 2)─┐
│                           1999-02-01 │                                    1999-02-01 00:00:00.000 │
└──────────────────────────────────────┴────────────────────────────────────────────────────────────┘

changeDay

Modifies the day component of a date or datetime value to a new specified day.

Syntax

changeDay(date_or_datetime, value)

Arguments

Returns

  • Returns a value of same type as date_or_datetime.

Example

SELECT changeDay(toDate('1999-01-01'), 5), changeDay(toDateTime64('1999-01-01 00:00:00.000', 3), 5)

Result:

┌─changeDay(toDate('1999-01-01'), 5)─┬─changeDay(toDateTime64('1999-01-01 00:00:00.000', 3), 5)─┐
│                         1999-01-05 │                                  1999-01-05 00:00:00.000 │
└────────────────────────────────────┴──────────────────────────────────────────────────────────┘

changeHour

Modifies the hour component of a date or datetime value to a new specified hour.

Syntax

changeHour(date_or_datetime, value)

Arguments

Returns

  • Returns a value of same type as date_or_datetime if the input is DateTime or DateTime64. If the input is a Date, returns DateTime. If the input is a Date32, returns DateTime64.

Example

SELECT changeHour(toDate('1999-01-01'), 14), changeHour(toDateTime64('1999-01-01 00:00:00.000', 3), 14)

Result:

┌─changeHour(toDate('1999-01-01'), 14)─┬─changeHour(toDateTime64('1999-01-01 00:00:00.000', 3), 14)─┐
│                  1999-01-01 14:00:00 │                                    1999-01-01 14:00:00.000 │
└──────────────────────────────────────┴────────────────────────────────────────────────────────────┘

changeMinute

Modifies the minute component of a date or datetime value to a new specified minute.

Syntax

changeMinute(date_or_datetime, value)

Arguments

Returns

  • Returns a value of same type as date_or_datetime if the input is DateTime or DateTime64. If the input is a Date, returns DateTime. If the input is a Date32, returns DateTime64.

Example

    SELECT changeMinute(toDate('1999-01-01'), 15), changeMinute(toDateTime64('1999-01-01 00:00:00.000', 3), 15)

Result:

┌─changeMinute(toDate('1999-01-01'), 15)─┬─changeMinute(toDateTime64('1999-01-01 00:00:00.000', 3), 15)─┐
│                    1999-01-01 00:15:00 │                                      1999-01-01 00:15:00.000 │
└────────────────────────────────────────┴──────────────────────────────────────────────────────────────┘

changeSecond

Modifies the second component of a date or datetime value to a new specified second.

Syntax

changeSecond(date_or_datetime, value)

Arguments

Returns

  • Returns a value of same type as date_or_datetime if the input is DateTime or DateTime64. If the input is a Date, returns DateTime. If the input is a Date32, returns DateTime64.

Example

SELECT changeSecond(toDate('1999-01-01'), 15), changeSecond(toDateTime64('1999-01-01 00:00:00.000', 3), 15)

Result:

┌─changeSecond(toDate('1999-01-01'), 15)─┬─changeSecond(toDateTime64('1999-01-01 00:00:00.000', 3), 15)─┐
│                    1999-01-01 00:00:15 │                                      1999-01-01 00:00:15.000 │
└────────────────────────────────────────┴──────────────────────────────────────────────────────────────┘

addYears

Adds a specified number of years to a date, datetime, or string representation of a date/time.

Syntax

addYears(date, num)

Arguments

Returns

Example

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

addQuarters

Adds a specified number of quarters to a date, datetime, or string representation of a date/time.

Syntax

addQuarters(date, num)

Arguments

Returns

Example

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

addMonths

Adds a specified number of months to a date, datetime, or string representation of a date/time.

Syntax

addMonths(date, num)

Arguments

Returns

Example

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

addWeeks

Adds a specified number of weeks to a date, datetime, or string representation of a date/time.

Syntax

addWeeks(date, num)

Arguments

Returns

Example

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

addDays

Adds a specified number of days to a date, datetime, or string representation of a date/time.

Syntax

addDays(date, num)

Arguments

Returns

Example

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

addHours

Adds a specified number of hours to a date, datetime, or string representation of a date/time.

Syntax

addHours(date, num)

Arguments

Returns

Example

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

addMinutes

Adds a specified number of minutes to a date, datetime, or string representation of a date/time.

Syntax

addMinutes(date, num)

Arguments

Returns

Example

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

addSeconds

Adds a specified number of seconds to a date, datetime, or string representation of a date/time.

Syntax

addSeconds(date, num)

Arguments

Returns

Example

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

addMilliseconds

Adds a specified number of milliseconds to a datetime or string representation of a datetime.

Syntax

addMilliseconds(date_time, num)

Arguments

Returns

  • Returns date_time plus num milliseconds. DateTime64.

Example

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

addMicroseconds

Adds a specified number of microseconds to a datetime or string representation of a datetime.

Syntax

addMicroseconds(date_time, num)

Arguments

Returns

  • Returns date_time plus num microseconds. DateTime64.

Example

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

addNanoseconds

Adds a specified number of nanoseconds to a datetime or string representation of a datetime.

Syntax

addNanoseconds(date_time, num)

Arguments

Returns

  • Returns date_time plus num nanoseconds. DateTime64.

Example

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

addInterval

Combines two interval values or an interval with a tuple of intervals.

Syntax

addInterval(interval_1, interval_2)

Arguments

Returns

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

Example

Query:

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

Result:

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

addTupleOfIntervals

Applies a series of interval additions to a date or datetime value.

Syntax

addTupleOfIntervals(date, intervals)

Arguments

Returns

Example

Query:

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

Result:

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

subtractYears

Subtracts a specified number of years from a date, datetime, or string representation of a date/time.

Syntax

subtractYears(date, num)

Arguments

Returns

Example

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

subtractQuarters

Subtracts a specified number of quarters from a date, datetime, or string representation of a date/time.

Syntax

subtractQuarters(date, num)

Arguments

Returns

Example

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

subtractMonths

Subtracts a specified number of months from a date, datetime, or string representation of a date/time.

Syntax

subtractMonths(date, num)

Arguments

Returns

Example

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

subtractWeeks

Subtracts a specified number of weeks from a date, datetime, or string representation of a date/time.

Syntax

subtractWeeks(date, num)

Arguments

Returns

Example

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

subtractDays

Subtracts a specified number of days from a date, datetime, or string representation of a date/time.

Syntax

subtractDays(date, num)

Arguments

Returns

Example

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

subtractHours

Subtracts a specified number of hours from a date, datetime, or string representation of a date/time.

Syntax

subtractHours(date, num)

Arguments

Returns

Example

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

subtractMinutes

Subtracts a specified number of minutes from a date, datetime, or string representation of a date/time.

Syntax

subtractMinutes(date, num)

Arguments

Returns

Example

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

subtractSeconds

Subtracts a specified number of seconds from a date, datetime, or string representation of a date/time.

Syntax

subtractSeconds(date, num)

Arguments

Returns

Example

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

subtractMilliseconds

Subtracts a specified number of milliseconds from a datetime or string representation of a datetime.

Syntax

subtractMilliseconds(date_time, num)

Arguments

Returns

  • Returns date_time minus num milliseconds. DateTime64.

Example

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

subtractMicroseconds

Subtracts a specified number of microseconds from a datetime or string representation of a datetime.

Syntax

subtractMicroseconds(date_time, num)

Arguments

Returns

  • Returns date_time minus num microseconds. DateTime64.

Example

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

subtractNanoseconds

Subtracts a specified number of nanoseconds from a datetime or string representation of a datetime.

Syntax

subtractNanoseconds(date_time, num)

Arguments

Returns

  • Returns date_time minus num nanoseconds. DateTime64.

Example

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

subtractInterval

Subtracts an interval from another interval or a tuple of intervals.

Syntax

subtractInterval(interval_1, interval_2)

Arguments

Returns

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

Example

Query:

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

Result:

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

subtractTupleOfIntervals

Applies a series of interval subtractions to a date or datetime value.

Syntax

subtractTupleOfIntervals(date, intervals)

Arguments

Returns

Example

Query:

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

Result:

┌─subtractTupleOfIntervals(date, (toIntervalDay(1), toIntervalYear(1)))─┐
│                                                            2016-12-31 │
└───────────────────────────────────────────────────────────────────────┘

timeSlots

Generates an array of datetime values representing time slots within a specified duration, starting from a given time and rounded to a defined interval size.

Syntax

timeSlots(StartTime, Duration[, Size])

Arguments

  • StartTime: The starting time for the interval. DateTime or DateTime64.
  • Duration: The length of the time interval in seconds. UInt32 or Decimal64.
  • Size: The size of each time slot in seconds (optional, default is 1800 for 30 minutes). UInt32 or Decimal64.

Returns

Example

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

Result:

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

formatDateTime

Formats a date or datetime value into a string according to a specified format pattern, using MySQL-style format specifiers.

Syntax

formatDateTime(Time, Format[, Timezone])

Alias: DATE_FORMAT.

Arguments

  • Time: The date or time value to format. Date, Date32, DateTime or DateTime64.
  • Format: A string literal defining the output format using MySQL-style specifiers. String.
  • Timezone: Optional Timezone for the returned value.

Returns

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

Replacement fields

Using replacement fields, you can define a pattern for the resulting string. "Example" column shows formatting result for 2018-01-02 22:33:44.

| Placeholder | Description

Updated