--- title: Date and Time Functions reference meta: description: Functions for manipulating dates and times. headingMaxLevels: 2 --- # 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 ```sql SELECT toDateTime('2016-06-15 23:00:00') AS time, toDate(time) AS date_local, toDate(time, 'Asia/Yekaterinburg') AS date_yekat, toString(time, 'US/Samoa') AS time_samoa ``` ```text ┌────────────────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 ```sql makeDate(year, month, day) makeDate(year, day_of_year) ``` Alias: - `MAKEDATE(year, month, day);` - `MAKEDATE(year, day_of_year);` ### Arguments - `year`: Year. [Integer](../data-types/int-uint), [Float](../data-types/float) or [Decimal](../data-types/decimal). - `month`: Month. [Integer](../data-types/int-uint), [Float](../data-types/float) or [Decimal](../data-types/decimal). - `day`: Day. [Integer](../data-types/int-uint), [Float](../data-types/float) or [Decimal](../data-types/decimal). - `day_of_year`: Day of the year. [Integer](../data-types/int-uint), [Float](../data-types/float) or [Decimal](../data-types/decimal). ### Returns - A date created from the arguments. [Date](../data-types/date). ### Example Create a Date from a year, month and day: ```sql SELECT makeDate(2023, 2, 28) AS Date ``` Result: ```text ┌───────date─┐ │ 2023-02-28 │ └────────────┘ ``` Create a Date from a year and day of year argument: ```sql SELECT makeDate(2023, 42) AS Date ``` Result: ```text ┌───────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 ```sql makeDate32(year, [month,] day) ``` ### Arguments - `year`: Year. [Integer](../data-types/int-uint), [Float](../data-types/float) or [Decimal](../data-types/decimal). - `month`: Month (optional). [Integer](../data-types/int-uint), [Float](../data-types/float) or [Decimal](../data-types/decimal). - `day`: Day. [Integer](../data-types/int-uint), [Float](../data-types/float) or [Decimal](../data-types/decimal). {% callout type="info" %} If `month` is omitted then `day` should take a value between `1` and `365`, otherwise it should take a value between `1` and `31`. {% /callout %} ### Returns - A date created from the arguments. [Date32](../data-types/date32). ### Example Create a date from a year, month, and day: ```sql SELECT makeDate32(2024, 1, 1) ``` Result: ```result 2024-01-01 ``` Create a Date from a year and day of year: ```sql SELECT makeDate32(2024, 100) ``` Result: ```result 2024-04-09 ``` ## makeDateTime Assembles a `DateTime` value from its constituent year, month, day, hour, minute, and second parts. ### Syntax ```sql makeDateTime(year, month, day, hour, minute, second[, timezone]) ``` ### Arguments - `year`: Year. [Integer](../data-types/int-uint), [Float](../data-types/float) or [Decimal](../data-types/decimal). - `month`: Month. [Integer](../data-types/int-uint), [Float](../data-types/float) or [Decimal](../data-types/decimal). - `day`: Day. [Integer](../data-types/int-uint), [Float](../data-types/float) or [Decimal](../data-types/decimal). - `hour`: Hour. [Integer](../data-types/int-uint), [Float](../data-types/float) or [Decimal](../data-types/decimal). - `minute`: Minute. [Integer](../data-types/int-uint), [Float](../data-types/float) or [Decimal](../data-types/decimal). - `second`: Second. [Integer](../data-types/int-uint), [Float](../data-types/float) or [Decimal](../data-types/decimal). - `timezone`: [Timezone](../data-types/string) for the returned value (optional). ### Returns - A date with time created from the arguments. [DateTime](../data-types/datetime). ### Example ```sql SELECT makeDateTime(2023, 2, 28, 17, 12, 33) AS DateTime ``` Result: ```text ┌────────────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 ```sql makeDateTime64(year, month, day, hour, minute, second[, precision]) ``` ### Arguments - `year`: Year (0-9999). [Integer](../data-types/int-uint), [Float](../data-types/float) or [Decimal](../data-types/decimal). - `month`: Month (1-12). [Integer](../data-types/int-uint), [Float](../data-types/float) or [Decimal](../data-types/decimal). - `day`: Day (1-31). [Integer](../data-types/int-uint), [Float](../data-types/float) or [Decimal](../data-types/decimal). - `hour`: Hour (0-23). [Integer](../data-types/int-uint), [Float](../data-types/float) or [Decimal](../data-types/decimal). - `minute`: Minute (0-59). [Integer](../data-types/int-uint), [Float](../data-types/float) or [Decimal](../data-types/decimal). - `second`: Second (0-59). [Integer](../data-types/int-uint), [Float](../data-types/float) or [Decimal](../data-types/decimal). - `precision`: Optional precision of the sub-second component (0-9). [Integer](../data-types/int-uint). ### Returns - A date and time created from the supplied arguments. [DateTime64](../data-types/datetime64). ### Example ```sql SELECT makeDateTime64(2023, 5, 15, 10, 30, 45, 779, 5) ``` ```result ┌─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 ```sql timestamp(expr[, expr_time]) ``` Alias: `TIMESTAMP` ### Arguments - `expr`: Date or date with time. [String](../data-types/string). - `expr_time`: Optional parameter. Time to add. [String](../data-types/string). ### Returns - [DateTime64](../data-types/datetime64)(6) ### Example ```sql SELECT timestamp('2023-12-31') as ts ``` Result: ```text ┌─────────────────────────ts─┐ │ 2023-12-31 00:00:00.000000 │ └────────────────────────────┘ ``` ```sql SELECT timestamp('2023-12-31 12:00:00', '12:00:00.11') as ts ``` Result: ```text ┌─────────────────────────ts─┐ │ 2024-01-01 00:00:00.110000 │ └────────────────────────────┘ ``` ## timeZone Retrieves the timezone configured for the current session. ### Syntax ```sql timeZone() ``` Alias: `timezone`. ### Returns - Timezone. [String](../data-types/string). ### Example ```sql SELECT timezone() ``` Result: ```result ┌─timezone()─────┐ │ America/Denver │ └────────────────┘ ``` ## serverTimeZone Provides the timezone configured for the server. ### Syntax ```sql serverTimeZone() ``` Alias: `serverTimezone`. ### Returns - Timezone. [String](../data-types/string). ### Example ```sql SELECT serverTimeZone() ``` Result: ```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 ```sql toTimezone(value, timezone) ``` Alias: `toTimezone`. ### Arguments - `value`: Time or date and time. [DateTime64](../data-types/datetime64). - `timezone`: Timezone for the returned value. [String](../data-types/string). This argument is a constant, because `toTimezone` changes the timezone of a column (timezone is an attribute of `DateTime*` types). ### Returns - Date and time. [DateTime](../data-types/datetime). ### Example ```sql 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: ```text 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 ```sql timeZoneOf(value) ``` Alias: `timezoneOf`. ### Arguments - `value`: Date and time. [DateTime](../data-types/datetime) or [DateTime64](../data-types/datetime64). ### Returns - Timezone name. [String](../data-types/string). ### Example ```sql SELECT timezoneOf(now()) ``` Result: ```text ┌─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 ```sql timeZoneOffset(value) ``` Alias: `timezoneOffset`. ### Arguments - `value`: Date and time. [DateTime](../data-types/datetime) or [DateTime64](../data-types/datetime64). ### Returns - Offset from UTC in seconds. [Int32](../data-types/int-uint). ### Example ```sql 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: ```text ┌────────────────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 ```sql toYear(value) ``` Alias: `YEAR` ### Arguments - `value`: a [Date](../data-types/date), [Date32](../data-types/date32), [DateTime](../data-types/datetime) or [DateTime64](../data-types/datetime64). ### Returns - The year of the given date/time. [UInt16](../data-types/int-uint). ### Example ```sql SELECT toYear(toDateTime('2023-04-21 10:20:30')) ``` Result: ```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 ```sql toQuarter(value) ``` Alias: `QUARTER` ### Arguments - `value`: a [Date](../data-types/date), [Date32](../data-types/date32), [DateTime](../data-types/datetime) or [DateTime64](../data-types/datetime64). ### Returns - The quarter of the year (1, 2, 3 or 4) of the given date/time. [UInt8](../data-types/int-uint). ### Example ```sql SELECT toQuarter(toDateTime('2023-04-21 10:20:30')) ``` Result: ```result ┌─toQuarter(toDateTime('2023-04-21 10:20:30'))─┐ │ 2 │ └──────────────────────────────────────────────┘ ``` ## toMonth Extracts the month number (1-12) from a date or datetime value. ### Syntax ```sql toMonth(value) ``` Alias: `MONTH` ### Arguments - `value`: a [Date](../data-types/date), [Date32](../data-types/date32), [DateTime](../data-types/datetime) or [DateTime64](../data-types/datetime64). ### Returns - The month of the year (1 - 12) of the given date/time. [UInt8](../data-types/int-uint). ### Example ```sql SELECT toMonth(toDateTime('2023-04-21 10:20:30')) ``` Result: ```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 ```sql toDayOfYear(value) ``` Alias: `DAYOFYEAR` ### Arguments - `value`: a [Date](../data-types/date), [Date32](../data-types/date32), [DateTime](../data-types/datetime) or [DateTime64](../data-types/datetime64). ### Returns - The day of the year (1 - 366) of the given date/time. [UInt16](../data-types/int-uint). ### Example ```sql SELECT toDayOfYear(toDateTime('2023-04-21 10:20:30')) ``` Result: ```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 ```sql toDayOfMonth(value) ``` Aliases: `DAYOFMONTH`, `DAY` ### Arguments - `value`: a [Date](../data-types/date), [Date32](../data-types/date32), [DateTime](../data-types/datetime) or [DateTime64](../data-types/datetime64). ### Returns - The day of the month (1 - 31) of the given date/time. [UInt8](../data-types/int-uint). ### Example ```sql SELECT toDayOfMonth(toDateTime('2023-04-21 10:20:30')) ``` Result: ```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 ```sql toDayOfWeek(t[, mode[, timezone]]) ``` Alias: `DAYOFWEEK`. ### Arguments - `t`: a [Date](../data-types/date), [Date32](../data-types/date32), [DateTime](../data-types/datetime) or [DateTime64](../data-types/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. | 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 | ### Returns - The day of the week (1-7), depending on the chosen mode, of the given date/time. [UInt8](../data-types/int-uint). ### Example The following date is April 21, 2023, which was a Friday: ```sql SELECT toDayOfWeek(toDateTime('2023-04-21')), toDayOfWeek(toDateTime('2023-04-21'), 1) ``` Result: ```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 ```sql toHour(value) ``` Alias: `HOUR` ### Arguments - `value`: a [DateTime](../data-types/datetime) or [DateTime64](../data-types/datetime64). ### Returns - The hour of the day (0 - 23) of the given date/time. [UInt8](../data-types/int-uint). ### Example ```sql SELECT toHour(toDateTime('2023-04-21 10:20:30')) ``` Result: ```result ┌─toHour(toDateTime('2023-04-21 10:20:30'))─┐ │ 10 │ └───────────────────────────────────────────┘ ``` ## toMinute Extracts the minute component (0-59) from a datetime value. ### Syntax ```sql toMinute(value) ``` Alias: `MINUTE` ### Arguments - `value`: a [DateTime](../data-types/datetime) or [DateTime64](../data-types/datetime64). ### Returns - The minute of the hour (0 - 59) of the given date/time. [UInt8](../data-types/int-uint). ### Example ```sql SELECT toMinute(toDateTime('2023-04-21 10:20:30')) ``` Result: ```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 ```sql toSecond(value) ``` Alias: `SECOND` ### Arguments - `value`: a [DateTime](../data-types/datetime) or [DateTime64](../data-types/datetime64). ### Returns - The second in the minute (0 - 59) of the given date/time. [UInt8](../data-types/int-uint). ### Example ```sql SELECT toSecond(toDateTime('2023-04-21 10:20:30')) ``` Result: ```result ┌─toSecond(toDateTime('2023-04-21 10:20:30'))─┐ │ 30 │ └─────────────────────────────────────────────┘ ``` ## toMillisecond Extracts the millisecond component (0-999) from a datetime value. ### Syntax ```sql toMillisecond(value) ``` Alias: `MILLISECOND` ### Arguments - `value`: [DateTime](../data-types/datetime) or [DateTime64](../data-types/datetime64). ### Returns - The millisecond in the minute (0 - 59) of the given date/time. [UInt16](../data-types/int-uint). ### Example ```sql SELECT toMillisecond(toDateTime64('2023-04-21 10:20:30.456', 3)) ``` Result: ```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 ```sql toUnixTimestamp(date) toUnixTimestamp(str, [timezone]) ``` ### Returns - Returns the unix timestamp. [UInt32](../data-types/int-uint). ### Example ```sql 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: ```text 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 ``` {% callout type="info" %} 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`. {% /callout %} ## toStartOfYear Truncates a date or datetime value to the beginning of its respective year. ### Syntax ```sql toStartOfYear(value) ``` ### Arguments - `value`: a [Date](../data-types/date), [Date32](../data-types/date32), [DateTime](../data-types/datetime) or [DateTime64](../data-types/datetime64). ### Returns - The first day of the year of the input date/time. [Date](../data-types/date). ### Example ```sql SELECT toStartOfYear(toDateTime('2023-04-21 10:20:30')) ``` Result: ```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 ```sql toStartOfISOYear(value) ``` ### Arguments - `value`: a [Date](../data-types/date), [Date32](../data-types/date32), [DateTime](../data-types/datetime) or [DateTime64](../data-types/datetime64). ### Returns - The first day of the year of the input date/time. [Date](../data-types/date). ### Example ```sql SELECT toStartOfISOYear(toDateTime('2023-04-21 10:20:30')) ``` Result: ```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 ```sql toStartOfQuarter(value) ``` ### Arguments - `value`: a [Date](../data-types/date), [Date32](../data-types/date32), [DateTime](../data-types/datetime) or [DateTime64](../data-types/datetime64). ### Returns - The first day of the quarter of the given date/time. [Date](../data-types/date). ### Example ```sql SELECT toStartOfQuarter(toDateTime('2023-04-21 10:20:30')) ``` Result: ```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 ```sql toStartOfMonth(value) ``` ### Arguments - `value`: a [Date](../data-types/date), [Date32](../data-types/date32), [DateTime](../data-types/datetime) or [DateTime64](../data-types/datetime64). ### Returns - The first day of the month of the given date/time. [Date](../data-types/date). ### Example ```sql SELECT toStartOfMonth(toDateTime('2023-04-21 10:20:30')) ``` Result: ```result ┌─toStartOfMonth(toDateTime('2023-04-21 10:20:30'))─┐ │ 2023-04-01 │ └───────────────────────────────────────────────────┘ ``` {% callout type="info" %} The behavior of parsing incorrect dates is implementation specific. The implementation may return zero date, throw an exception, or do "natural" overflow. {% /callout %} ## toLastDayOfMonth Adjusts a date or datetime value to the last day of its month. ### Syntax ```sql toLastDayOfMonth(value) ``` Alias: `LAST_DAY` ### Arguments - `value`: a [Date](../data-types/date), [Date32](../data-types/date32), [DateTime](../data-types/datetime) or [DateTime64](../data-types/datetime64). ### Returns - The last day of the month of the given date/time. [Date](../data-types/date). ### Example ```sql SELECT toLastDayOfMonth(toDateTime('2023-04-21 10:20:30')) ``` Result: ```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 ```sql toMonday(value) ``` ### Arguments - `value`: a [Date](../data-types/date), [Date32](../data-types/date32), [DateTime](../data-types/datetime) or [DateTime64](../data-types/datetime64). ### Returns - The date of the nearest Monday on or prior to the given date. [Date](../data-types/date). ### Example ```sql SELECT toMonday(toDateTime('2023-04-21 10:20:30')), /* a Friday */ toMonday(toDate('2023-04-24')) /* already a Monday */ ``` Result: ```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 ```sql toStartOfWeek(t[, mode[, timezone]]) ``` ### Arguments - `t`: a [Date](../data-types/date), [Date32](../data-types/date32), [DateTime](../data-types/datetime) or [DateTime64](../data-types/datetime64). - `mode`: determines the first day of the week as described in the [toWeek()](#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](../data-types/date). ### Example ```sql 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: ```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 ```sql toLastDayOfWeek(t[, mode[, timezone]]) ``` ### Arguments - `t`: a [Date](../data-types/date), [Date32](../data-types/date32), [DateTime](../data-types/datetime) or [DateTime64](../data-types/datetime64). - `mode`: determines the last day of the week as described in the [toWeek](#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](../data-types/date). ### Example ```sql 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: ```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 ```sql toStartOfDay(value) ``` ### Arguments - `value`: a [Date](../data-types/date), [Date32](../data-types/date32), [DateTime](../data-types/datetime) or [DateTime64](../data-types/datetime64). ### Returns - The start of the day of the given date/time. [DateTime](../data-types/datetime). ### Example ```sql SELECT toStartOfDay(toDateTime('2023-04-21 10:20:30')) ``` Result: ```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 ```sql toStartOfHour(value) ``` ### Arguments - `value`: a [DateTime](../data-types/datetime) or [DateTime64](../data-types/datetime64). ### Returns - The start of the hour of the given date/time. [DateTime](../data-types/datetime). ### Example ```sql SELECT toStartOfHour(toDateTime('2023-04-21 10:20:30')), toStartOfHour(toDateTime64('2023-04-21', 6)) ``` Result: ```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 ```sql toStartOfMinute(value) ``` ### Arguments - `value`: a [DateTime](../data-types/datetime) or [DateTime64](../data-types/datetime64). ### Returns - The start of the minute of the given date/time. [DateTime](../data-types/datetime). ### Example ```sql SELECT toStartOfMinute(toDateTime('2023-04-21 10:20:30')), toStartOfMinute(toDateTime64('2023-04-21 10:20:30.5300', 8)) FORMAT Vertical ``` Result: ```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 ```sql toStartOfSecond(value, [timezone]) ``` ### Arguments - `value`: Date and time. [DateTime64](../data-types/datetime64). - `timezone`: Timezone for the returned value (optional). If not specified, the function uses the timezone of the `value` parameter. [String](../data-types/string). ### Returns - Input value without sub-seconds. [DateTime64](../data-types/datetime64). ### Example Query without timezone: ```sql WITH toDateTime64('2020-01-01 10:20:30.999', 3) AS dt64 SELECT toStartOfSecond(dt64) ``` Result: ```text ┌───toStartOfSecond(dt64)─┐ │ 2020-01-01 10:20:30.000 │ └─────────────────────────┘ ``` Query with timezone: ```sql WITH toDateTime64('2020-01-01 10:20:30.999', 3) AS dt64 SELECT toStartOfSecond(dt64, 'Asia/Istanbul') ``` Result: ```text ┌─toStartOfSecond(dt64, 'Asia/Istanbul')─┐ │ 2020-01-01 13:20:30.000 │ └────────────────────────────────────────┘ ``` ## toStartOfMillisecond Truncates a `DateTime64` value to the beginning of its millisecond. ### Syntax ```sql toStartOfMillisecond(value, [timezone]) ``` ### Arguments - `value`: Date and time. [DateTime64](../data-types/datetime64). - `timezone`: Timezone for the returned value (optional). If not specified, the function uses the timezone of the `value` parameter. [String](../data-types/string). ### Returns - Input value with sub-milliseconds. [DateTime64](../data-types/datetime64). ### Example Query without timezone: ```sql WITH toDateTime64('2020-01-01 10:20:30.999999999', 9) AS dt64 SELECT toStartOfMillisecond(dt64) ``` Result: ```text ┌────toStartOfMillisecond(dt64)─┐ │ 2020-01-01 10:20:30.999000000 │ └───────────────────────────────┘ ``` Query with timezone: ```sql WITH toDateTime64('2020-01-01 10:20:30.999999999', 9) AS dt64 SELECT toStartOfMillisecond(dt64, 'Asia/Istanbul') ``` Result: ```text ┌─toStartOfMillisecond(dt64, 'Asia/Istanbul')─┐ │ 2020-01-01 12:20:30.999000000 │ └─────────────────────────────────────────────┘ ``` ## toStartOfMicrosecond Truncates a `DateTime64` value to the beginning of its microsecond. ### Syntax ```sql toStartOfMicrosecond(value, [timezone]) ``` ### Arguments - `value`: Date and time. [DateTime64](../data-types/datetime64). - `timezone`: Timezone for the returned value (optional). If not specified, the function uses the timezone of the `value` parameter. [String](../data-types/string). ### Returns - Input value with sub-microseconds. [DateTime64](../data-types/datetime64). ### Example Query without timezone: ```sql WITH toDateTime64('2020-01-01 10:20:30.999999999', 9) AS dt64 SELECT toStartOfMicrosecond(dt64) ``` Result: ```text ┌────toStartOfMicrosecond(dt64)─┐ │ 2020-01-01 10:20:30.999999000 │ └───────────────────────────────┘ ``` Query with timezone: ```sql WITH toDateTime64('2020-01-01 10:20:30.999999999', 9) AS dt64 SELECT toStartOfMicrosecond(dt64, 'Asia/Istanbul') ``` Result: ```text ┌─toStartOfMicrosecond(dt64, 'Asia/Istanbul')─┐ │ 2020-01-01 12:20:30.999999000 │ └─────────────────────────────────────────────┘ ``` ## toStartOfNanosecond Truncates a `DateTime64` value to the beginning of its nanosecond. ### Syntax ```sql toStartOfNanosecond(value, [timezone]) ``` ### Arguments - `value`: Date and time. [DateTime64](../data-types/datetime64). - `timezone`: Timezone for the returned value (optional). If not specified, the function uses the timezone of the `value` parameter. [String](../data-types/string). ### Returns - Input value with nanoseconds. [DateTime64](../data-types/datetime64). ### Example Query without timezone: ```sql WITH toDateTime64('2020-01-01 10:20:30.999999999', 9) AS dt64 SELECT toStartOfNanosecond(dt64) ``` Result: ```text ┌─────toStartOfNanosecond(dt64)─┐ │ 2020-01-01 10:20:30.999999999 │ └───────────────────────────────┘ ``` Query with timezone: ```sql WITH toDateTime64('2020-01-01 10:20:30.999999999', 9) AS dt64 SELECT toStartOfNanosecond(dt64, 'Asia/Istanbul') ``` Result: ```text ┌─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 ```sql toStartOfFiveMinutes(value) ``` ### Arguments - `value`: a [DateTime](../data-types/datetime) or [DateTime64](../data-types/datetime64). ### Returns - The start of the five-minute interval of the given date/time. [DateTime](../data-types/datetime). ### Example ```sql 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: ```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 ```sql toStartOfTenMinutes(value) ``` ### Arguments - `value`: a [DateTime](../data-types/datetime) or [DateTime64](../data-types/datetime64). ### Returns - The start of the ten-minute interval of the given date/time. [DateTime](../data-types/datetime). ### Example ```sql 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: ```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 ```sql toStartOfFifteenMinutes(value) ``` ### Arguments - `value`: a [DateTime](../data-types/datetime) or [DateTime64](../data-types/datetime64). ### Returns - The start of the fifteen-minute interval of the given date/time. [DateTime](../data-types/datetime). ### Example ```sql 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: ```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 ```sql toStartOfInterval(value, INTERVAL x unit[, time_zone]) toStartOfInterval(value, INTERVAL x unit[, origin[, time_zone]]) ``` Aliases: `time_bucket`, `date_bin`. ### Arguments - `value`: a [Date](../data-types/date), [Date32](../data-types/date32), [DateTime](../data-types/datetime) or [DateTime64](../data-types/datetime64). - `x`: The number of `unit` intervals. [Integer](../data-types/int-uint). - `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](../data-types/datetime). - `time_zone`: Optional [Timezone](../data-types/string) for the returned value. ### Returns - The start of the specified interval. [Date](../data-types/date), [Date32](../data-types/date32), [DateTime](../data-types/datetime) or [DateTime64](../data-types/datetime64). ### Example ```sql SELECT toStartOfInterval(toDateTime('2023-01-01 14:45:00'), INTERVAL 1 MINUTE, toDateTime('2023-01-01 14:35:30')) ``` Result: ```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 ```sql toTime(date[,timezone]) ``` ### Arguments - `date`: Date to convert to a time. [Date](../data-types/date)/[DateTime](../data-types/datetime)/[DateTime64](../data-types/datetime64). - `timezone` (optional): Timezone for the returned value. [String](../data-types/string). ### Returns - [DateTime](../data-types/datetime) with date equated to `1970-01-02` while preserving the time. {% callout type="info" %} If the `date` input argument contained sub-second components, they will be dropped in the returned `DateTime` value with second-accuracy. {% /callout %} ### Example Query: ```sql SELECT toTime(toDateTime64('1970-12-10 01:20:30.3000',3)) AS result, toTypeName(result) ``` 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 ```sql toRelativeYearNum(date) ``` ### Arguments - `date`: Date or date with time. [Date](../data-types/date)/[DateTime](../data-types/datetime)/[DateTime64](../data-types/datetime64). ### Returns - The number of years from a fixed reference point in the past. [UInt16](../data-types/int-uint). ### Example Query: ```sql SELECT toRelativeYearNum(toDate('2002-12-08')) AS y1, toRelativeYearNum(toDate('2010-10-26')) AS y2 ``` Result: ```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 ```sql toRelativeQuarterNum(date) ``` ### Arguments - `date`: Date or date with time. [Date](../data-types/date)/[DateTime](../data-types/datetime)/[DateTime64](../data-types/datetime64). ### Returns - The number of quarters from a fixed reference point in the past. [UInt32](../data-types/int-uint). ### Example Query: ```sql SELECT toRelativeQuarterNum(toDate('1993-11-25')) AS q1, toRelativeQuarterNum(toDate('2005-01-05')) AS q2 ``` Result: ```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 ```sql toRelativeMonthNum(date) ``` ### Arguments - `date`: Date or date with time. [Date](../data-types/date)/[DateTime](../data-types/datetime)/[DateTime64](../data-types/datetime64). ### Returns - The number of months from a fixed reference point in the past. [UInt32](../data-types/int-uint). ### Example Query: ```sql SELECT toRelativeMonthNum(toDate('2001-04-25')) AS m1, toRelativeMonthNum(toDate('2009-07-08')) AS m2 ``` Result: ```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 ```sql toRelativeWeekNum(date) ``` ### Arguments - `date`: Date or date with time. [Date](../data-types/date)/[DateTime](../data-types/datetime)/[DateTime64](../data-types/datetime64). ### Returns - The number of weeks from a fixed reference point in the past. [UInt32](../data-types/int-uint). ### Example Query: ```sql SELECT toRelativeWeekNum(toDate('2000-02-29')) AS w1, toRelativeWeekNum(toDate('2001-01-12')) AS w2 ``` Result: ```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 ```sql toRelativeDayNum(date) ``` ### Arguments - `date`: Date or date with time. [Date](../data-types/date)/[DateTime](../data-types/datetime)/[DateTime64](../data-types/datetime64). ### Returns - The number of days from a fixed reference point in the past. [UInt32](../data-types/int-uint). ### Example Query: ```sql SELECT toRelativeDayNum(toDate('1993-10-05')) AS d1, toRelativeDayNum(toDate('2000-09-20')) AS d2 ``` Result: ```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 ```sql toRelativeHourNum(date) ``` ### Arguments - `date`: Date or date with time. [Date](../data-types/date)/[DateTime](../data-types/datetime)/[DateTime64](../data-types/datetime64). ### Returns - The number of hours from a fixed reference point in the past. [UInt32](../data-types/int-uint). ### Example Query: ```sql SELECT toRelativeHourNum(toDateTime('1993-10-05 05:20:36')) AS h1, toRelativeHourNum(toDateTime('2000-09-20 14:11:29')) AS h2 ``` Result: ```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 ```sql toRelativeMinuteNum(date) ``` ### Arguments - `date`: Date or date with time. [Date](../data-types/date)/[DateTime](../data-types/datetime)/[DateTime64](../data-types/datetime64). ### Returns - The number of minutes from a fixed reference point in the past. [UInt32](../data-types/int-uint). ### Example Query: ```sql SELECT toRelativeMinuteNum(toDateTime('1993-10-05 05:20:36')) AS m1, toRelativeMinuteNum(toDateTime('2000-09-20 14:11:29')) AS m2 ``` Result: ```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 ```sql toRelativeSecondNum(date) ``` ### Arguments - `date`: Date or date with time. [Date](../data-types/date)/[DateTime](../data-types/datetime)/[DateTime64](../data-types/datetime64). ### Returns - The number of seconds from a fixed reference point in the past. [UInt32](../data-types/int-uint). ### Example Query: ```sql SELECT toRelativeSecondNum(toDateTime('1993-10-05 05:20:36')) AS s1, toRelativeSecondNum(toDateTime('2000-09-20 14:11:29')) AS s2 ``` Result: ```result ┌────────s1─┬────────s2─┐ │ 749794836 │ 969451889 │ └───────────┴───────────┘ ``` ## toISOYear Extracts the ISO year number from a date or datetime value. ### Syntax ```sql toISOYear(value) ``` ### Arguments - `value`: The value with date or date with time. [Date](../data-types/date), [Date32](../data-types/date32), [DateTime](../data-types/datetime) or [DateTime64](../data-types/datetime64). ### Returns - The input value converted to a ISO year number. [UInt16](../data-types/int-uint). ### Example Query: ```sql SELECT toISOYear(toDate('2024/10/02')) as year1, toISOYear(toDateTime('2024-10-02 01:30:00')) as year2 ``` Result: ```result ┌─year1─┬─year2─┐ │ 2024 │ 2024 │ └───────┴───────┘ ``` ## toISOWeek Extracts the ISO week number (1-53) from a date or datetime value. ### Syntax ```sql toISOWeek(value) ``` ### Arguments - `value`: The value with date or date with time. [Date](../data-types/date), [Date32](../data-types/date32), [DateTime](../data-types/datetime) or [DateTime64](../data-types/datetime64). ### Returns - `value` converted to the current ISO week number. [UInt8](../data-types/int-uint). ### Example Query: ```sql SELECT toISOWeek(toDate('2024/10/02')) AS week1, toISOWeek(toDateTime('2024/10/02 01:30:00')) AS week2 ``` Response: ```result ┌─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 ```sql 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. | 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 | ### Returns - The week number (0-53 or 1-53), depending on the chosen mode, of the given date/time. [UInt8](../data-types/int-uint). ### Example ```sql SELECT toDate('2016-12-27') AS date, toWeek(date) AS week0, toWeek(date,1) AS week1, toWeek(date,9) AS week9 ``` ```text ┌───────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 ```sql 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](../data-types/int-uint). {% callout type="caution" %} 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. {% /callout %} ### Example ```sql 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 ``` ```text ┌───────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 ```sql toDaysSinceYearZero(date[, time_zone]) ``` Alias: `TO_DAYS` ### Arguments - `date`: The date to calculate the number of days passed since year zero from. [Date](../data-types/date), [Date32](../data-types/date32), [DateTime](../data-types/datetime) or [DateTime64](../data-types/datetime64). - `time_zone`: A [String](../data-types/string) type constant value or an expression representing the time zone. ### Returns The number of days passed since date 0000-01-01. [UInt32](../data-types/int-uint). ### Example ```sql SELECT toDaysSinceYearZero(toDate('2023-09-08')) ``` Result: ```text ┌─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 ```sql fromDaysSinceYearZero(days) ``` Alias: `FROM_DAYS` ### Arguments - `days`: The number of days passed since year zero. [Integer](../data-types/int-uint). ### Returns The date corresponding to the number of days passed since year zero. [Date](../data-types/date). ### Example ```sql SELECT fromDaysSinceYearZero(739136), fromDaysSinceYearZero(toDaysSinceYearZero(toDate('2023-09-08'))) ``` Result: ```text ┌─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 ```sql fromDaysSinceYearZero32(days) ``` ### Arguments - `days`: The number of days passed since year zero. [Integer](../data-types/int-uint). ### Returns The date corresponding to the number of days passed since year zero. [Date32](../data-types/date32). ### Example ```sql SELECT fromDaysSinceYearZero32(739136) ``` Result: ```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 ```sql age('unit', startdate, enddate, [timezone]) ``` ### Arguments - `unit`: The type of interval for result. [String](../data-types/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](../data-types/date), [Date32](../data-types/date32), [DateTime](../data-types/datetime) or [DateTime64](../data-types/datetime64). - `enddate`: The second time value to subtract from (the minuend). [Date](../data-types/date), [Date32](../data-types/date32), [DateTime](../data-types/datetime) or [DateTime64](../data-types/datetime64). - `timezone`: [Timezone name](../data-types/string) (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](../data-types/int-uint). ### Example ```sql SELECT age('hour', toDateTime('2018-01-01 22:30:00'), toDateTime('2018-01-02 23:00:00')) ``` Result: ```text ┌─age('hour', toDateTime('2018-01-01 22:30:00'), toDateTime('2018-01-02 23:00:00'))─┐ │ 24 │ └───────────────────────────────────────────────────────────────────────────────────┘ ``` ```sql 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: ```text ┌──────────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 ```sql date_diff('unit', startdate, enddate, [timezone]) ``` Aliases: `dateDiff`, `DATE_DIFF`, `timestampDiff`, `timestamp_diff`, `TIMESTAMP_DIFF`. ### Arguments - `unit`: The type of interval for result. [String](../data-types/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](../data-types/date), [Date32](../data-types/date32), [DateTime](../data-types/datetime) or [DateTime64](../data-types/datetime64). - `enddate`: The second time value to subtract from (the minuend). [Date](../data-types/date), [Date32](../data-types/date32), [DateTime](../data-types/datetime) or [DateTime64](../data-types/datetime64). - `timezone`: [Timezone name](../data-types/string) (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](../data-types/int-uint). ### Example ```sql SELECT dateDiff('hour', toDateTime('2018-01-01 22:00:00'), toDateTime('2018-01-02 23:00:00')) ``` Result: ```text ┌─dateDiff('hour', toDateTime('2018-01-01 22:00:00'), toDateTime('2018-01-02 23:00:00'))─┐ │ 25 │ └────────────────────────────────────────────────────────────────────────────────────────┘ ``` ```sql 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: ```text ┌──────────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 ```sql date_trunc(unit, value[, timezone]) ``` Alias: `dateTrunc`. ### Arguments - `unit`: The type of interval to truncate the result. [String](../data-types/string). 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](../data-types/date), [Date32](../data-types/date32), [DateTime](../data-types/datetime) or [DateTime64](../data-types/datetime64). - `timezone`: [Timezone name](../data-types/string) 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](../data-types/datetime). ### Example Query without timezone: ```sql SELECT now(), date_trunc('hour', now()) ``` Result: ```text ┌───────────────now()─┬─date_trunc('hour', now())─┐ │ 2020-09-28 10:40:45 │ 2020-09-28 10:00:00 │ └─────────────────────┴───────────────────────────┘ ``` Query with the specified timezone: ```sql SELECT now(), date_trunc('hour', now(), 'Asia/Istanbul') ``` Result: ```text ┌───────────────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 ```sql date_add(unit, value, date) ``` Alternative syntax: ```sql date_add(date, INTERVAL value unit) ``` Aliases: `dateAdd`, `DATE_ADD`. ### Arguments - `unit`: The type of interval to add. Note: This isn't a [String](../data-types/string) and must therefore not be quoted. Possible values: - `second` - `minute` - `hour` - `day` - `week` - `month` - `quarter` - `year` - `value`: Value of interval to add. [Int](../data-types/int-uint). - `date`: The date or date with time to which `value` is added. [Date](../data-types/date), [Date32](../data-types/date32), [DateTime](../data-types/datetime) or [DateTime64](../data-types/datetime64). ### Returns Date or date with time obtained by adding `value`, expressed in `unit`, to `date`. [Date](../data-types/date), [Date32](../data-types/date32), [DateTime](../data-types/datetime) or [DateTime64](../data-types/datetime64). ### Example ```sql SELECT date_add(YEAR, 3, toDate('2018-01-01')) ``` Result: ```text ┌─plus(toDate('2018-01-01'), toIntervalYear(3))─┐ │ 2021-01-01 │ └───────────────────────────────────────────────┘ ``` ```sql SELECT date_add(toDate('2018-01-01'), INTERVAL 3 YEAR) ``` Result: ```text ┌─plus(toDate('2018-01-01'), toIntervalYear(3))─┐ │ 2021-01-01 │ └───────────────────────────────────────────────┘ ``` ## date_sub Subtracts a specified time interval from a date or datetime value. ### Syntax ```sql date_sub(unit, value, date) ``` Alternative syntax: ```sql date_sub(date, INTERVAL value unit) ``` Aliases: `dateSub`, `DATE_SUB`. ### Arguments - `unit`: The type of interval to subtract. Note: This isn't a [String](../data-types/string) and must therefore not be quoted. Possible values: - `second` - `minute` - `hour` - `day` - `week` - `month` - `quarter` - `year` - `value`: Value of interval to subtract. [Int](../data-types/int-uint). - `date`: The date or date with time from which `value` is subtracted. [Date](../data-types/date), [Date32](../data-types/date32), [DateTime](../data-types/datetime) or [DateTime64](../data-types/datetime64). ### Returns Date or date with time obtained by subtracting `value`, expressed in `unit`, from `date`. [Date](../data-types/date), [Date32](../data-types/date32), [DateTime](../data-types/datetime) or [DateTime64](../data-types/datetime64). ### Example ```sql SELECT date_sub(YEAR, 3, toDate('2018-01-01')) ``` Result: ```text ┌─minus(toDate('2018-01-01'), toIntervalYear(3))─┐ │ 2015-01-01 │ └────────────────────────────────────────────────┘ ``` ```sql SELECT date_sub(toDate('2018-01-01'), INTERVAL 3 YEAR) ``` Result: ```text ┌─minus(toDate('2018-01-01'), toIntervalYear(3))─┐ │ 2015-01-01 │ └────────────────────────────────────────────────┘ ``` ## timestamp_add Adds a specified time interval to a date or datetime value. ### Syntax ```sql timestamp_add(date, INTERVAL value unit) ``` Aliases: `timeStampAdd`, `TIMESTAMP_ADD`. ### Arguments - `date`: Date or date with time. [Date](../data-types/date), [Date32](../data-types/date32), [DateTime](../data-types/datetime) or [DateTime64](../data-types/datetime64). - `value`: Value of interval to add. [Int](../data-types/int-uint). - `unit`: The type of interval to add. [String](../data-types/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](../data-types/date), [Date32](../data-types/date32), [DateTime](../data-types/datetime) or [DateTime64](../data-types/datetime64). ### Example ```sql select timestamp_add(toDate('2018-01-01'), INTERVAL 3 MONTH) ``` Result: ```text ┌─plus(toDate('2018-01-01'), toIntervalMonth(3))─┐ │ 2018-04-01 │ └────────────────────────────────────────────────┘ ``` ## timestamp_sub Subtracts a specified time interval from a date or datetime value. ### Syntax ```sql timestamp_sub(unit, value, date) ``` Aliases: `timeStampSub`, `TIMESTAMP_SUB`. ### Arguments - `unit`: The type of interval to subtract. [String](../data-types/string). Possible values: - `second` - `minute` - `hour` - `day` - `week` - `month` - `quarter` - `year` - `value`: Value of interval to subtract. [Int](../data-types/int-uint). - `date`: Date or date with time. [Date](../data-types/date), [Date32](../data-types/date32), [DateTime](../data-types/datetime) or [DateTime64](../data-types/datetime64). ### Returns Date or date with time obtained by subtracting `value`, expressed in `unit`, from `date`. [Date](../data-types/date), [Date32](../data-types/date32), [DateTime](../data-types/datetime) or [DateTime64](../data-types/datetime64). ### Example ```sql select timestamp_sub(MONTH, 5, toDateTime('2018-12-18 01:02:03')) ``` Result: ```text ┌─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 ```sql addDate(date, interval) ``` Alias: `ADDDATE` ### Arguments - `date`: The date or date with time to which `interval` is added. [Date](../data-types/date), [Date32](../data-types/date32), [DateTime](../data-types/datetime), [DateTime64](../data-types/datetime64), or [String](../data-types/string). - `interval`: Interval to add. [Interval](../data-types/interval). ### Returns Date or date with time obtained by adding `interval` to `date`. [Date](../data-types/date), [Date32](../data-types/date32), [DateTime](../data-types/datetime) or [DateTime64](../data-types/datetime64). ### Example ```sql SELECT addDate(toDate('2018-01-01'), INTERVAL 3 YEAR) ``` Result: ```text ┌─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 ```sql subDate(date, interval) ``` Alias: `SUBDATE` ### Arguments - `date`: The date or date with time from which `interval` is subtracted. [Date](../data-types/date), [Date32](../data-types/date32), [DateTime](../data-types/datetime), [DateTime64](../data-types/datetime64), or [String](../data-types/string). - `interval`: Interval to subtract. [Interval](../data-types/interval). ### Returns Date or date with time obtained by subtracting `interval` from `date`. [Date](../data-types/date), [Date32](../data-types/date32), [DateTime](../data-types/datetime) or [DateTime64](../data-types/datetime64). ### Example ```sql SELECT subDate(toDate('2018-01-01'), INTERVAL 3 YEAR) ``` Result: ```text ┌─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 ```sql now([timezone]) ``` Alias: `current_timestamp`. ### Arguments - `timezone`: [Timezone name](../data-types/string) for the returned value (optional). ### Returns - Current date and time. [DateTime](../data-types/datetime). ### Example Query without timezone: ```sql SELECT now() ``` Result: ```text ┌───────────────now()─┐ │ 2020-10-17 07:42:09 │ └─────────────────────┘ ``` Query with the specified timezone: ```sql SELECT now('Asia/Istanbul') ``` Result: ```text ┌─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 ```sql now64([scale], [timezone]) ``` ### Arguments - `scale`: Tick size (precision): 10-precision seconds. Valid range: [ 0 : 9 ]. Typically, are used - 3 (default) (milliseconds), 6 (microseconds), 9 (nanoseconds). - `timezone`: [Timezone name](../data-types/string) for the returned value (optional). ### Returns - Current date and time with sub-second precision. [DateTime64](../data-types/datetime64). ### Example ```sql SELECT now64(), now64(9, 'Asia/Istanbul') ``` Result: ```text ┌─────────────────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 ```sql nowInBlock([timezone]) ``` ### Arguments - `timezone`: [Timezone name](../data-types/string) for the returned value (optional). ### Returns - Current date and time at the moment of processing of each block of data. [DateTime](../data-types/datetime). ### Example ```sql SELECT now(), nowInBlock(), sleep(1) FROM numbers(3) SETTINGS max_block_size = 1 FORMAT PrettyCompactMonoBlock ``` Result: ```text ┌───────────────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 ```sql today() ``` Aliases: `curdate`, `current_date`. ### Arguments - None ### Returns - Current date. [Date](../data-types/date). ### Example Query: ```sql 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: ```result ┏━━━━━━━━━━━━┳━━━━━━━━━━━━┳━━━━━━━━━━━━━━┓ ┃ 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 ```sql yesterday() ``` ### Arguments - None ### Returns - Yesterday's date. [Date](../data-types/date). ### Example ```sql SELECT yesterday() ``` Result: ```result ┌─yesterday()─┐ │ 2024-05-27 │ └─────────────┘ ``` ## timeSlot Rounds a datetime value down to the beginning of the nearest 30-minute interval. ### Syntax ```sql timeSlot(time[, time_zone]) ``` ### Arguments - `time`: Time to round to the start of a half-an-hour length interval. [DateTime](../data-types/datetime)/[Date32](../data-types/date32)/[DateTime64](../data-types/datetime64). - `time_zone`: A [String](../data-types/string) type constant value or an expression representing the time zone. {% callout type="info" %} 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. {% /callout %} ### Returns - Returns the time rounded to the start of a half-an-hour length interval. [DateTime](../data-types/datetime). ### Example Query: ```sql SELECT timeSlot(toDateTime('2000-01-02 03:04:05', 'UTC')) ``` Result: ```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 ```sql toYYYYMM(value[, timezone]) ``` ### Arguments - `value`: A date or date with time. [Date](../data-types/date) or [DateTime](../data-types/datetime). - `timezone`: Optional [Timezone](../data-types/string) for the returned value. ### Returns - A [UInt32](../data-types/int-uint) number containing the year and month number (YYYY \* 100 + MM). ### Example ```sql SELECT toYYYYMM(now(), 'US/Eastern') ``` Result: ```text ┌─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 ```sql toYYYYMMDD(value[, timezone]) ``` ### Arguments - `value`: A date or date with time. [Date](../data-types/date) or [DateTime](../data-types/datetime). - `timezone`: Optional [Timezone](../data-types/string) for the returned value. ### Returns - A [UInt32](../data-types/int-uint) number containing the year, month, and day number (YYYY \* 10000 + MM \* 100 + DD). ### Example ```sql SELECT toYYYYMMDD(now(), 'US/Eastern') ``` Result: ```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 ```sql toYYYYMMDDhhmmss(value[, timezone]) ``` ### Arguments - `value`: A date or date with time. [Date](../data-types/date) or [DateTime](../data-types/datetime). - `timezone`: Optional [Timezone](../data-types/string) for the returned value. ### Returns - A [UInt64](../data-types/int-uint) number containing the year, month, day, hour, minute, and second number (YYYY \* 10000000000 + MM \* 100000000 + DD \* 1000000 + hh \* 10000 + mm \* 100 + ss). ### Example ```sql SELECT toYYYYMMDDhhmmss(now(), 'US/Eastern') ``` Result: ```result ┌─toYYYYMMDDhhmmss(now(), 'US/Eastern')─┐ │ 20230302112209 │ └───────────────────────────────────────┘ ``` ## YYYYMMDDToDate Converts an integer in `YYYYMMDD` format into a `Date` value. ### Syntax ```sql YYYYMMDDToDate(yyyymmdd) ``` ### Arguments - `yyyymmdd`: A number representing the year, month and day. [Integer](../data-types/int-uint), [Float](../data-types/float) or [Decimal](../data-types/decimal). ### Returns - A date created from the arguments. [Date](../data-types/date). ### Example ```sql SELECT YYYYMMDDToDate(20230911) ``` Result: ```result ┌─YYYYMMDDToDate(20230911)─┐ │ 2023-09-11 │ └──────────────────────────┘ ``` ## YYYYMMDDToDate32 Converts an integer in `YYYYMMDD` format into a `Date32` value. ### Syntax ```sql YYYYMMDDToDate32(yyyymmdd) ``` ### Arguments - `yyyymmdd`: A number representing the year, month and day. [Integer](../data-types/int-uint), [Float](../data-types/float) or [Decimal](../data-types/decimal). ### Returns - A date created from the arguments. [Date32](../data-types/date32). ### Example ```sql SELECT YYYYMMDDToDate32(20230911) ``` Result: ```result ┌─YYYYMMDDToDate32(20230911)─┐ │ 2023-09-11 │ └────────────────────────────┘ ``` ## YYYYMMDDhhmmssToDateTime Converts an integer in `YYYYMMDDhhmmss` format into a `DateTime` value. An optional timezone can be specified. ### Syntax ```sql YYYYMMDDhhmmssToDateTime(yyyymmddhhmmss[, timezone]) ``` ### Arguments - `yyyymmddhhmmss`: A number representing the year, month, day, hour, minute, and second. [Integer](../data-types/int-uint), [Float](../data-types/float) or [Decimal](../data-types/decimal). - `timezone`: Optional [Timezone](../data-types/string) for the returned value. ### Returns - A date with time created from the arguments. [DateTime](../data-types/datetime). ### Example ```sql SELECT YYYYMMDDhhmmssToDateTime(20230911131415) ``` Result: ```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 ```sql YYYYMMDDhhmmssToDateTime64(yyyymmddhhmmss[, timezone[, precision]]) ``` ### Arguments - `yyyymmddhhmmss`: A number representing the year, month, day, hour, minute, and second. [Integer](../data-types/int-uint), [Float](../data-types/float) or [Decimal](../data-types/decimal). - `timezone`: Optional [Timezone](../data-types/string) for the returned value. - `precision`: Optional precision of the sub-second component (0-9). [Integer](../data-types/int-uint). ### Returns - A date with time created from the arguments. [DateTime64](../data-types/datetime64). ### Example ```sql SELECT YYYYMMDDhhmmssToDateTime64(20230911131415, 'UTC', 3) ``` Result: ```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 ```sql changeYear(date_or_datetime, value) ``` ### Arguments - `date_or_datetime`: a [Date](../data-types/date), [Date32](../data-types/date32), [DateTime](../data-types/datetime) or [DateTime64](../data-types/datetime64). - `value`: a new value of the year. [Integer](../data-types/int-uint). ### Returns - The same type as `date_or_datetime`. ### Example ```sql SELECT changeYear(toDate('1999-01-01'), 2000), changeYear(toDateTime64('1999-01-01 00:00:00.000', 3), 2000) ``` Result: ```sql ┌─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 ```sql changeMonth(date_or_datetime, value) ``` ### Arguments - `date_or_datetime`: a [Date](../data-types/date), [Date32](../data-types/date32), [DateTime](../data-types/datetime) or [DateTime64](../data-types/datetime64). - `value`: a new value of the month. [Integer](../data-types/int-uint). ### Returns - Returns a value of same type as `date_or_datetime`. ### Example ```sql SELECT changeMonth(toDate('1999-01-01'), 2), changeMonth(toDateTime64('1999-01-01 00:00:00.000', 3), 2) ``` Result: ```sql ┌─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 ```sql changeDay(date_or_datetime, value) ``` ### Arguments - `date_or_datetime`: a [Date](../data-types/date), [Date32](../data-types/date32), [DateTime](../data-types/datetime) or [DateTime64](../data-types/datetime64). - `value`: a new value of the day. [Integer](../data-types/int-uint). ### Returns - Returns a value of same type as `date_or_datetime`. ### Example ```sql SELECT changeDay(toDate('1999-01-01'), 5), changeDay(toDateTime64('1999-01-01 00:00:00.000', 3), 5) ``` Result: ```sql ┌─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 ```sql changeHour(date_or_datetime, value) ``` ### Arguments - `date_or_datetime`: a [Date](../data-types/date), [Date32](../data-types/date32), [DateTime](../data-types/datetime) or [DateTime64](../data-types/datetime64). - `value`: a new value of the hour. [Integer](../data-types/int-uint). ### Returns - Returns a value of same type as `date_or_datetime` if the input is `DateTime` or `DateTime64`. If the input is a [Date](../data-types/date), returns [DateTime](../data-types/datetime). If the input is a [Date32](../data-types/date32), returns [DateTime64](../data-types/datetime64). ### Example ```sql SELECT changeHour(toDate('1999-01-01'), 14), changeHour(toDateTime64('1999-01-01 00:00:00.000', 3), 14) ``` Result: ```sql ┌─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 ```sql changeMinute(date_or_datetime, value) ``` ### Arguments - `date_or_datetime`: a [Date](../data-types/date), [Date32](../data-types/date32), [DateTime](../data-types/datetime) or [DateTime64](../data-types/datetime64). - `value`: a new value of the minute. [Integer](../data-types/int-uint). ### Returns - Returns a value of same type as `date_or_datetime` if the input is `DateTime` or `DateTime64`. If the input is a [Date](../data-types/date), returns [DateTime](../data-types/datetime). If the input is a [Date32](../data-types/date32), returns [DateTime64](../data-types/datetime64). ### Example ```sql SELECT changeMinute(toDate('1999-01-01'), 15), changeMinute(toDateTime64('1999-01-01 00:00:00.000', 3), 15) ``` Result: ```sql ┌─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 ```sql changeSecond(date_or_datetime, value) ``` ### Arguments - `date_or_datetime`: a [Date](../data-types/date), [Date32](../data-types/date32), [DateTime](../data-types/datetime) or [DateTime64](../data-types/datetime64). - `value`: a new value of the second. [Integer](../data-types/int-uint). ### Returns - Returns a value of same type as `date_or_datetime` if the input is `DateTime` or `DateTime64`. If the input is a [Date](../data-types/date), returns [DateTime](../data-types/datetime). If the input is a [Date32](../data-types/date32), returns [DateTime64](../data-types/datetime64). ### Example ```sql SELECT changeSecond(toDate('1999-01-01'), 15), changeSecond(toDateTime64('1999-01-01 00:00:00.000', 3), 15) ``` Result: ```sql ┌─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 ```sql addYears(date, num) ``` ### Arguments - `date`: Date / date with time to add specified number of years to. [Date](../data-types/date)/[Date32](../data-types/date32)/[DateTime](../data-types/datetime)/[DateTime64](../data-types/datetime64), [String](../data-types/string). - `num`: Number of years to add. [(U)Int*](../data-types/int-uint), [Float*](../data-types/float). ### Returns - Returns `date` plus `num` years. [Date](../data-types/date)/[Date32](../data-types/date32)/[DateTime](../data-types/datetime)/[DateTime64](../data-types/datetime64). ### Example ```sql 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 ``` ```result ┌─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 ```sql addQuarters(date, num) ``` ### Arguments - `date`: Date / date with time to add specified number of quarters to. [Date](../data-types/date)/[Date32](../data-types/date32)/[DateTime](../data-types/datetime)/[DateTime64](../data-types/datetime64), [String](../data-types/string). - `num`: Number of quarters to add. [(U)Int*](../data-types/int-uint), [Float*](../data-types/float). ### Returns - Returns `date` plus `num` quarters. [Date](../data-types/date)/[Date32](../data-types/date32)/[DateTime](../data-types/datetime)/[DateTime64](../data-types/datetime64). ### Example ```sql 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 ``` ```result ┌─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 ```sql addMonths(date, num) ``` ### Arguments - `date`: Date / date with time to add specified number of months to. [Date](../data-types/date)/[Date32](../data-types/date32)/[DateTime](../data-types/datetime)/[DateTime64](../data-types/datetime64), [String](../data-types/string). - `num`: Number of months to add. [(U)Int*](../data-types/int-uint), [Float*](../data-types/float). ### Returns - Returns `date` plus `num` months. [Date](../data-types/date)/[Date32](../data-types/date32)/[DateTime](../data-types/datetime)/[DateTime64](../data-types/datetime64). ### Example ```sql 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 ``` ```result ┌─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 ```sql addWeeks(date, num) ``` ### Arguments - `date`: Date / date with time to add specified number of weeks to. [Date](../data-types/date)/[Date32](../data-types/date32)/[DateTime](../data-types/datetime)/[DateTime64](../data-types/datetime64), [String](../data-types/string). - `num`: Number of weeks to add. [(U)Int*](../data-types/int-uint), [Float*](../data-types/float). ### Returns - Returns `date` plus `num` weeks. [Date](../data-types/date)/[Date32](../data-types/date32)/[DateTime](../data-types/datetime)/[DateTime64](../data-types/datetime64). ### Example ```sql 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 ``` ```result ┌─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 ```sql addDays(date, num) ``` ### Arguments - `date`: Date / date with time to add specified number of days to. [Date](../data-types/date)/[Date32](../data-types/date32)/[DateTime](../data-types/datetime)/[DateTime64](../data-types/datetime64), [String](../data-types/string). - `num`: Number of days to add. [(U)Int*](../data-types/int-uint), [Float*](../data-types/float). ### Returns - Returns `date` plus `num` days. [Date](../data-types/date)/[Date32](../data-types/date32)/[DateTime](../data-types/datetime)/[DateTime64](../data-types/datetime64). ### Example ```sql 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 ``` ```result ┌─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 ```sql addHours(date, num) ``` ### Arguments - `date`: Date / date with time to add specified number of hours to. [Date](../data-types/date)/[Date32](../data-types/date32)/[DateTime](../data-types/datetime)/[DateTime64](../data-types/datetime64), [String](../data-types/string). - `num`: Number of hours to add. [(U)Int*](../data-types/int-uint), [Float*](../data-types/float). ### Returns - Returns `date` plus `num` hours. [Date](../data-types/date)/[Date32](../data-types/date32)/[DateTime](../data-types/datetime)/[DateTime64](../data-types/datetime64). ### Example ```sql 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 ``` ```result ┌─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 ```sql addMinutes(date, num) ``` ### Arguments - `date`: Date / date with time to add specified number of minutes to. [Date](../data-types/date)/[Date32](../data-types/date32)/[DateTime](../data-types/datetime)/[DateTime64](../data-types/datetime64), [String](../data-types/string). - `num`: Number of minutes to add. [(U)Int*](../data-types/int-uint), [Float*](../data-types/float). ### Returns - Returns `date` plus `num` minutes. [Date](../data-types/date)/[Date32](../data-types/date32)/[DateTime](../data-types/datetime)/[DateTime64](../data-types/datetime64). ### Example ```sql 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 ``` ```result ┌─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 ```sql addSeconds(date, num) ``` ### Arguments - `date`: Date / date with time to add specified number of seconds to. [Date](../data-types/date)/[Date32](../data-types/date32)/[DateTime](../data-types/datetime)/[DateTime64](../data-types/datetime64), [String](../data-types/string). - `num`: Number of seconds to add. [(U)Int*](../data-types/int-uint), [Float*](../data-types/float). ### Returns - Returns `date` plus `num` seconds. [Date](../data-types/date)/[Date32](../data-types/date32)/[DateTime](../data-types/datetime)/[DateTime64](../data-types/datetime64). ### Example ```sql 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 ``` ```result ┌─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 ```sql addMilliseconds(date_time, num) ``` ### Arguments - `date_time`: Date with time to add specified number of milliseconds to. [DateTime](../data-types/datetime)/[DateTime64](../data-types/datetime64), [String](../data-types/string). - `num`: Number of milliseconds to add. [(U)Int*](../data-types/int-uint), [Float*](../data-types/float). ### Returns - Returns `date_time` plus `num` milliseconds. [DateTime64](../data-types/datetime64). ### Example ```sql 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 ``` ```result ┌─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 ```sql addMicroseconds(date_time, num) ``` ### Arguments - `date_time`: Date with time to add specified number of microseconds to. [DateTime](../data-types/datetime)/[DateTime64](../data-types/datetime64), [String](../data-types/string). - `num`: Number of microseconds to add. [(U)Int*](../data-types/int-uint), [Float*](../data-types/float). ### Returns - Returns `date_time` plus `num` microseconds. [DateTime64](../data-types/datetime64). ### Example ```sql 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 ``` ```result ┌─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 ```sql addNanoseconds(date_time, num) ``` ### Arguments - `date_time`: Date with time to add specified number of nanoseconds to. [DateTime](../data-types/datetime)/[DateTime64](../data-types/datetime64), [String](../data-types/string). - `num`: Number of nanoseconds to add. [(U)Int*](../data-types/int-uint), [Float*](../data-types/float). ### Returns - Returns `date_time` plus `num` nanoseconds. [DateTime64](../data-types/datetime64). ### Example ```sql 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 ``` ```result ┌─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 ```sql addInterval(interval_1, interval_2) ``` ### Arguments - `interval_1`: First interval or tuple of intervals. [Interval](../data-types/interval), [Tuple](../data-types/tuple)([Interval](../data-types/interval)). - `interval_2`: Second interval to be added. [Interval](../data-types/interval). ### Returns - Returns a tuple of intervals. [Tuple](../data-types/tuple)([Interval](../data-types/interval)). {% callout type="info" %} 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)`. {% /callout %} ### Example Query: ```sql 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: ```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 ```sql addTupleOfIntervals(date, intervals) ``` ### Arguments - `date`: Date or date with time. [Date](../data-types/date)/[Date32](../data-types/date32)/[DateTime](../data-types/datetime)/[DateTime64](../data-types/datetime64). - `intervals`: Tuple of intervals to add to `date`. [Tuple](../data-types/tuple)([Interval](../data-types/interval)). ### Returns - Returns `date` with added `intervals`. [Date](../data-types/date)/[Date32](../data-types/date32)/[DateTime](../data-types/datetime)/[DateTime64](../data-types/datetime64). ### Example Query: ```sql WITH toDate('2018-01-01') AS date SELECT addTupleOfIntervals(date, (INTERVAL 1 DAY, INTERVAL 1 MONTH, INTERVAL 1 YEAR)) ``` Result: ```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 ```sql subtractYears(date, num) ``` ### Arguments - `date`: Date / date with time to subtract specified number of years from. [Date](../data-types/date)/[Date32](../data-types/date32)/[DateTime](../data-types/datetime)/[DateTime64](../data-types/datetime64), [String](../data-types/string). - `num`: Number of years to subtract. [(U)Int*](../data-types/int-uint), [Float*](../data-types/float). ### Returns - Returns `date` minus `num` years. [Date](../data-types/date)/[Date32](../data-types/date32)/[DateTime](../data-types/datetime)/[DateTime64](../data-types/datetime64). ### Example ```sql 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 ``` ```result ┌─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 ```sql subtractQuarters(date, num) ``` ### Arguments - `date`: Date / date with time to subtract specified number of quarters from. [Date](../data-types/date)/[Date32](../data-types/date32)/[DateTime](../data-types/datetime)/[DateTime64](../data-types/datetime64), [String](../data-types/string). - `num`: Number of quarters to subtract. [(U)Int*](../data-types/int-uint), [Float*](../data-types/float). ### Returns - Returns `date` minus `num` quarters. [Date](../data-types/date)/[Date32](../data-types/date32)/[DateTime](../data-types/datetime)/[DateTime64](../data-types/datetime64). ### Example ```sql 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 ``` ```result ┌─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 ```sql subtractMonths(date, num) ``` ### Arguments - `date`: Date / date with time to subtract specified number of months from. [Date](../data-types/date)/[Date32](../data-types/date32)/[DateTime](../data-types/datetime)/[DateTime64](../data-types/datetime64), [String](../data-types/string). - `num`: Number of months to subtract. [(U)Int*](../data-types/int-uint), [Float*](../data-types/float). ### Returns - Returns `date` minus `num` months. [Date](../data-types/date)/[Date32](../data-types/date32)/[DateTime](../data-types/datetime)/[DateTime64](../data-types/datetime64). ### Example ```sql 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 ``` ```result ┌─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 ```sql subtractWeeks(date, num) ``` ### Arguments - `date`: Date / date with time to subtract specified number of weeks from. [Date](../data-types/date)/[Date32](../data-types/date32)/[DateTime](../data-types/datetime)/[DateTime64](../data-types/datetime64), [String](../data-types/string). - `num`: Number of weeks to subtract. [(U)Int*](../data-types/int-uint), [Float*](../data-types/float). ### Returns - Returns `date` minus `num` weeks. [Date](../data-types/date)/[Date32](../data-types/date32)/[DateTime](../data-types/datetime)/[DateTime64](../data-types/datetime64). ### Example ```sql 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 ``` ```result ┌─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 ```sql subtractDays(date, num) ``` ### Arguments - `date`: Date / date with time to subtract specified number of days from. [Date](../data-types/date)/[Date32](../data-types/date32)/[DateTime](../data-types/datetime)/[DateTime64](../data-types/datetime64), [String](../data-types/string). - `num`: Number of days to subtract. [(U)Int*](../data-types/int-uint), [Float*](../data-types/float). ### Returns - Returns `date` minus `num` days. [Date](../data-types/date)/[Date32](../data-types/date32)/[DateTime](../data-types/datetime)/[DateTime64](../data-types/datetime64). ### Example ```sql 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 ``` ```result ┌─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 ```sql subtractHours(date, num) ``` ### Arguments - `date`: Date / date with time to subtract specified number of hours from. [Date](../data-types/date)/[Date32](../data-types/date32)/[DateTime](../data-types/datetime)/[DateTime64](../data-types/datetime64), [String](../data-types/string). - `num`: Number of hours to subtract. [(U)Int*](../data-types/int-uint), [Float*](../data-types/float). ### Returns - Returns `date` minus `num` hours. [Date](../data-types/date)/[Date32](../data-types/date32)/[DateTime](../data-types/datetime)/[DateTime64](../data-types/datetime64). ### Example ```sql 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 ``` ```result ┌─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 ```sql subtractMinutes(date, num) ``` ### Arguments - `date`: Date / date with time to subtract specified number of minutes from. [Date](../data-types/date)/[Date32](../data-types/date32)/[DateTime](../data-types/datetime)/[DateTime64](../data-types/datetime64), [String](../data-types/string). - `num`: Number of minutes to subtract. [(U)Int*](../data-types/int-uint), [Float*](../data-types/float). ### Returns - Returns `date` minus `num` minutes. [Date](../data-types/date)/[Date32](../data-types/date32)/[DateTime](../data-types/datetime)/[DateTime64](../data-types/datetime64). ### Example ```sql 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 ``` ```result ┌─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 ```sql subtractSeconds(date, num) ``` ### Arguments - `date`: Date / date with time to subtract specified number of seconds from. [Date](../data-types/date)/[Date32](../data-types/date32)/[DateTime](../data-types/datetime)/[DateTime64](../data-types/datetime64), [String](../data-types/string). - `num`: Number of seconds to subtract. [(U)Int*](../data-types/int-uint), [Float*](../data-types/float). ### Returns - Returns `date` minus `num` seconds. [Date](../data-types/date)/[Date32](../data-types/date32)/[DateTime](../data-types/datetime)/[DateTime64](../data-types/datetime64). ### Example ```sql 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 ``` ```result ┌─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 ```sql subtractMilliseconds(date_time, num) ``` ### Arguments - `date_time`: Date with time to subtract specified number of milliseconds from. [DateTime](../data-types/datetime)/[DateTime64](../data-types/datetime64), [String](../data-types/string). - `num`: Number of milliseconds to subtract. [(U)Int*](../data-types/int-uint), [Float*](../data-types/float). ### Returns - Returns `date_time` minus `num` milliseconds. [DateTime64](../data-types/datetime64). ### Example ```sql 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 ``` ```result ┌─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 ```sql subtractMicroseconds(date_time, num) ``` ### Arguments - `date_time`: Date with time to subtract specified number of microseconds from. [DateTime](../data-types/datetime)/[DateTime64](../data-types/datetime64), [String](../data-types/string). - `num`: Number of microseconds to subtract. [(U)Int*](../data-types/int-uint), [Float*](../data-types/float). ### Returns - Returns `date_time` minus `num` microseconds. [DateTime64](../data-types/datetime64). ### Example ```sql 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 ``` ```result ┌─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 ```sql subtractNanoseconds(date_time, num) ``` ### Arguments - `date_time`: Date with time to subtract specified number of nanoseconds from. [DateTime](../data-types/datetime)/[DateTime64](../data-types/datetime64), [String](../data-types/string). - `num`: Number of nanoseconds to subtract. [(U)Int*](../data-types/int-uint), [Float*](../data-types/float). ### Returns - Returns `date_time` minus `num` nanoseconds. [DateTime64](../data-types/datetime64). ### Example ```sql 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 ``` ```result ┌─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 ```sql subtractInterval(interval_1, interval_2) ``` ### Arguments - `interval_1`: First interval or tuple of intervals. [Interval](../data-types/interval), [Tuple](../data-types/tuple)([Interval](../data-types/interval)). - `interval_2`: Second interval to be negated. [Interval](../data-types/interval). ### Returns - Returns a tuple of intervals. [Tuple](../data-types/tuple)([Interval](../data-types/interval)). {% callout type="info" %} 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)`. {% /callout %} ### Example Query: ```sql 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: ```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 ```sql subtractTupleOfIntervals(date, intervals) ``` ### Arguments - `date`: Date or date with time. [Date](../data-types/date)/[Date32](../data-types/date32)/[DateTime](../data-types/datetime)/[DateTime64](../data-types/datetime64). - `intervals`: Tuple of intervals to subtract from `date`. [Tuple](../data-types/tuple)([Interval](../data-types/interval)). ### Returns - Returns `date` with subtracted `intervals`. [Date](../data-types/date)/[Date32](../data-types/date32)/[DateTime](../data-types/datetime)/[DateTime64](../data-types/datetime64). ### Example Query: ```sql WITH toDate('2018-01-01') AS date SELECT subtractTupleOfIntervals(date, (INTERVAL 1 DAY, INTERVAL 1 YEAR)) ``` Result: ```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 ```sql timeSlots(StartTime, Duration[, Size]) ``` ### Arguments - `StartTime`: The starting time for the interval. [DateTime](../data-types/datetime) or [DateTime64](../data-types/datetime64). - `Duration`: The length of the time interval in seconds. [UInt32](../data-types/int-uint) or [Decimal64](../data-types/decimal). - `Size`: The size of each time slot in seconds (optional, default is 1800 for 30 minutes). [UInt32](../data-types/int-uint) or [Decimal64](../data-types/decimal). ### Returns - An array of [DateTime](../data-types/datetime) or [DateTime64](../data-types/datetime64) values. ### Example ```sql 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: ```text ┌─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 ```sql formatDateTime(Time, Format[, Timezone]) ``` Alias: `DATE_FORMAT`. ### Arguments - `Time`: The date or time value to format. [Date](../data-types/date), [Date32](../data-types/date32), [DateTime](../data-types/datetime) or [DateTime64](../data-types/datetime64). - `Format`: A string literal defining the output format using MySQL-style specifiers. [String](../data-types/string). - `Timezone`: Optional [Timezone](../data-types/string) for the returned value. ### Returns - Returns time and date values according to the determined format. [String](../data-types/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