Skip to main content
Date and time functions provide a broad set of tools for manipulating timestamps. These functions enable you to:
  1. Create date and time values.
  2. Parse and format date and time strings
  3. Extract specific components (year, month, day, hour, etc.)
  4. Convert between time zones
  5. Perform date and time calculations

​
Working with time zones

Most functions accept an optional time zone argument (e.g., ‘Europe/Amsterdam’ or ‘US/Pacific’). This example shows how a timestamp is interpreted in different time zones: 
SELECT
  toDateTime('2024-06-10 20:00:00') AS order_time,
  toDate(order_time) AS local_date,
  toDate(toTimeZone(order_time, 'America/Mexico_City')) AS mexico_city_date,
  toString(toTimeZone(order_time, 'US/Hawaii')) AS hawaii_time
FROM (
  SELECT toDateTime('2024-06-10 20:00:00') AS order_time
) AS taco_orders;
Result: 
| order_time          | local_date | mexico_city_date | hawaii_time         |
|---------------------|------------|------------------|---------------------|
| 2024-06-10 20:00:00 | 2024-06-10 | 2024-06-10       | 2024-06-10 14:00:00 |
In this example:
  • order_time is the original timestamp
  • local_date is the date in the local time zone
  • mexico_city_date is the date in Mexico City
  • hawaii_time is the full timestamp in Hawaii
Note how the time changes to 14:00:00 (2:00 PM) in Hawaii due to the time zone difference.

​
ClickHouse function reference

​
makeDate

Creates a Date from:
  • a year, month and day argument, or
  • a year and day of year argument.
Syntax: 
makeDate(year, month, day);
makeDate(year, day_of_year);
Alias:
  • MAKEDATE(year, month, day)
  • MAKEDATE(year, day_of_year)
Arguments:
  • year (numeric): Year.
  • month (numeric): Month.
  • day (numeric): Day.
  • day_of_year (numeric): Day of the year.
Returns:
  • A date created from the arguments. [Date].
Example: Create a Date from a year, month and day: 
SELECT
	makeDate(2024, 2, 28) AS Date;
Result: 
| date       |
|------------|
| 2024-02-28 |
Create a Date from a year and day of year argument: 
SELECT
	makeDate(2024, 15) AS Date;
Result: 
| date       |
|------------|
| 2024-01-15 |
Arguments:
  • year (numeric): Year.
  • month (numeric): Month.
  • day (numeric): Day.
  • day_of_year (numeric): Day of the year.
Returns:
  • A date created from the arguments. [Date32].
Example: Create a Date32 from a year, month and day: 
SELECT makeDate32(2024, 2, 28) AS Date;
Result: 
| Date       |
|------------|
| 2024-02-28 |
Create a Date32 from a year and day of year argument: 
SELECT makeDate32(2024, 15) AS Date;
Result: 
| Date       |
|------------|
| 2024-01-15 |
This function is similar to makeDate, but returns a Date32 type which supports a wider range of dates (from year 1900 to 2299).

​
makeDateTime

Creates a DateTime from year, month, day, hour, minute and second arguments. Syntax: 
makeDateTime(year, month, day, hour, minute, second[, timezone])
Arguments:
  • year (numeric): Year.
  • month (numeric): Month.
  • day (numeric): Day.
  • hour (numeric): Hour.
  • minute (numeric): Minute.
  • second (numeric): Second.
  • timezone (String, optional): Timezone for the returned value.
Returns:
  • A date with time created from the arguments. DateTime.
Example: 
SELECT
	makeDateTime(2024, 2, 28, 17, 12, 33) AS TacoTime;
Result: 
| TacoTime            |
|---------------------|
| 2024-02-28 17:12:33 |
This example creates a DateTime representing the moment when a special taco promotion ends.

​
makeDateTime64

Creates a DateTime64 value from year, month, day, hour, minute, second and optional fractional seconds components. Syntax: 
makeDateTime64(year, month, day, hour, minute, second[, fraction[, precision[, timezone]]])
Arguments:
  • year (numeric): Year.
  • month (numeric): Month (1-12).
  • day (numeric): Day (1-31).
  • hour (numeric): Hour (0-23).
  • minute (numeric): Minute (0-59).
  • second (numeric): Second (0-59).
  • fraction (numeric, optional): Fractional seconds.
  • precision (UInt8, optional): Precision of the fractional seconds (0-9).
  • timezone (String, optional): Timezone name.
Returns: A DateTime64 value created from the supplied arguments. Example: 
SELECT makeDateTime64(2024, 5, 15, 10, 30, 45, 779, 3);
Result: 
| makeDateTime64(2024, 5, 15, 10, 30, 45, 779, 3)  |
|--------------------------------------------------|
| 2024-05-15 10:30:45.779                          |
This example creates a DateTime64 value for May 15, 2024 at 10:30:45.779 AM with millisecond precision. Syntax: 
timestamp(expr[, expr_time])
Alias:
  • TIMESTAMP
Arguments:
  • expr (String) — Date or date with time.
  • expr_time (String, optional) — Time to add.
Returns:
  • A DateTime64(6) value.
Examples: 
SELECT
	timestamp('2023-12-31') AS ts;
Result: 
| ts                         |
|----------------------------|
| 2023-12-31 00:00:00.000000 |

SELECT
	timestamp('2023-12-31 12:00:00', '12:00:00.11') AS ts;
Result: 
| ts                         |
|----------------------------|
| 2024-01-01 00:00:00.110000 |
In this example:
  • The first query converts a date string to a DateTime64 value.
  • The second query adds 12 hours and 0.11 seconds to the initial date and time.
This function is useful for converting string representations of dates and times into DateTime64 values, which can be used for precise timestamp calculations and comparisons in ClickHouse.

​
timeZone

Returns the timezone of the current session. Syntax: 
timeZone()
Alias:
  • timezone
Returns:
  • The timezone of the current session as a String.
Example: 
SELECT timeZone();
Result: 
| timeZone() |
|------------|
| Taco/Time  |
This function returns the value of the session_timezone setting. If executed in the context of a distributed table, it generates a column with values relevant to each shard. Otherwise, it produces a constant value. See Also:

​
serverTimeZone

Returns the timezone of the server, i.e. the value of setting timezone. If the function is executed in the context of a distributed table, then it generates a normal column with values relevant to each shard. Otherwise, it produces a constant value. Syntax: 
serverTimeZone()
Alias:
  • serverTimezone
Returns:
  • Timezone name. (String)
Example: 
SELECT serverTimeZone();
Result: 
| serverTimeZone() |
|------------------|
| UTC              |
This example shows that the server’s timezone is set to UTC. See also:
  • timeZone() function for getting the current session’s timezone
Propel always uses UTC.

​
toTimeZone

Converts a date or date with time to the specified time zone. The function changes the timezone attribute of the value and adjusts the value’s string representation accordingly, but does not modify the underlying timestamp. Syntax 
toTimeZone(value, timezone)
Arguments
  • value (DateTime64): Date and time.
  • timezone (String): Timezone for the returned value.
Returns
  • Date and time in the specified timezone. (DateTime)
Example 
SELECT
	toDateTime('2019-01-01 00:00:00', 'UTC') AS time_utc,
	toTypeName(time_utc) AS type_utc,
	toInt32(time_utc) AS int32utc,
	toTimeZone(time_utc, 'America/Los_Angeles') AS time_la,
	toTypeName(time_la) AS type_la,
	toInt32(time_la) AS int32la
Result: 
| time_utc            | type_utc        | int32utc  | time_la             | type_la                         | int32la   |
|---------------------|-----------------|-----------|---------------------|---------------------------------|-----------|
| 2019-01-01 00:00:00 | DateTime('UTC') | 1546300800| 2018-12-31 16:00:00 | DateTime('America/Los_Angeles') | 1546300800|
In this example:
  • We start with a UTC timestamp for January 1, 2019, at midnight.
  • We convert it to the Los Angeles timezone, which is 8 hours behind UTC.
  • Note that the integer representation (seconds since epoch) remains the same, but the string representation changes to reflect the new timezone.
This function is particularly useful when dealing with data from different timezones or when you need to present data in a specific timezone regardless of the server’s local time.
The toTimeZone function only changes the timezone attribute and the string representation. It does not perform any time arithmetic. If you need to adjust the actual time value, consider using functions like addHours or subtractHours in combination with toTimeZone.
See Also:

​
timeZoneOf

Returns the timezone name of a DateTime or DateTime64 value. Syntax: 
timeZoneOf(value)
Arguments:
  • value (DateTime or DateTime64): Date and time.
Returns:
  • Timezone name. String.
Example: 
SELECT
	timeZoneOf(toDateTime('2024-03-03 12:30:00', 'America/Los_Angeles')) AS taco_time_zone;
Result: 
| taco_time_zone      |
|---------------------|
| America/Los_Angeles |
In this example, we create a DateTime value with the ‘America/Los_Angeles’ timezone and then extract the timezone name using timeZoneOf(). This could be useful when working with taco delivery timestamps from different regions.

​
timeZoneOffset

Returns the timezone offset in seconds from UTC for the specified date and time. Syntax: 
timeZoneOffset(value)
Arguments:
  • value (DateTime or DateTime64): Date and time.
Returns:
  • Offset from UTC in seconds. Int32.
This function takes into account daylight saving time and historical timezone changes for the specified date and time. The IANA timezone database is used to calculate the offset. Example: 
SELECT
	toDateTime('2023-04-21 10:20:30', 'America/Los_Angeles') AS Time,
	toTypeName(Time) AS Type,
	timeZoneOffset(Time) AS Offset_in_seconds,
	(Offset_in_seconds / 3600) AS Offset_in_hours;
Result: 
| Time                | Type                              | Offset_in_seconds | Offset_in_hours |
|---------------------|-----------------------------------|-------------------|-----------------|
| 2023-04-21 10:20:30 | DateTime('America/Los_Angeles')   | -25200            | -7              |
In this example:
  • We create a DateTime value for April 21, 2023, at 10:20:30 AM in the ‘America/Los_Angeles’ timezone.
  • The timeZoneOffset function returns -25200 seconds, which is equivalent to -7 hours.
  • This indicates that Los Angeles was 7 hours behind UTC at that specific date and time.

​
toYear

Extracts the year from a date or date with time value. Syntax 
toYear(date)
Alias:
  • YEAR
Arguments
  • date (Date, Date32, DateTime, or DateTime64): Date or date with time.
Returns:
  • The year number (AD).
  • Type: UInt16
Example 
SELECT
	toYear(toDateTime('2023-04-21 10:20:30')) AS year_extracted;
Result: 
| year_extracted |
|----------------|
| 2023           |
In this example, toYear extracts the year (2023) from the given date and time. This function is particularly useful for grouping data by year or filtering records based on specific years.

​
toQuarter

Returns the quarter (1-4) of a date or date with time. Syntax: 
toQuarter(value)
Alias:
  • QUARTER
Arguments:
  • value (Date, Date32, DateTime, or DateTime64): A date or date with time.
Returns:
  • The quarter of the year (1, 2, 3 or 4) of the given date/time. UInt8.
Example: 
SELECT
	toQuarter(toDateTime('2023-04-21 10:20:30')) AS quarter_num;
Result: 
| quarter_num |
|-------------|
| 2           |
In this example, April 21st falls in the second quarter of the year, so the function returns 2.
This function is useful for grouping data by quarters or for time-based analysis where quarterly information is needed.

​
toMonth

Returns the month component (1-12) of a date or date with time. Syntax: 
toMonth(value)
Alias:
  • MONTH
Arguments:
  • value (Date, Date32, DateTime, or DateTime64): A date or date with time.
Returns:
  • The month of the year (1-12) of the given date/time. Type: UInt8.
Example: 
SELECT
	toMonth(toDateTime('2023-04-21 10:20:30')) AS month_number;
Result: 
| month_number |
|--------------|
| 4            |
This example extracts the month (4, representing April) from the given date.
For dates outside the supported range, the behavior is implementation-specific. ClickHouse may return zero, throw an exception, or perform a “natural” overflow.

​
toDayOfYear

Returns the number of the day within the year (1-366) for a given date or date with time. Syntax 
toDayOfYear(date)
Alias:
  • DAYOFYEAR
Arguments
  • date (Date, Date32, DateTime, or DateTime64): Date or date with time.
Returns:
  • The day of the year (1-366) for the given date/time.
  • Type: UInt16
Example 
SELECT
	toDayOfYear(toDate('2023-04-21')) AS day_of_year;
Result: 
| day_of_year |
|-------------|
| 111         |
In this example, April 21, 2023 is the 111th day of the year.

​
toDayOfMonth

Returns the number of the day within the month (1-31) for a given date or date with time. Syntax 
toDayOfMonth(value)
Alias:
  • DAYOFMONTH
  • DAY
Arguments
  • value (Date, Date32, DateTime, or DateTime64): Date or date with time.
Returns
  • The day of the month (1-31) of the given date/time. UInt8.
Example 
SELECT
	toDayOfMonth(toDateTime('2023-05-15 10:20:30')) AS day_of_month;
Result: 
| day_of_month |
|--------------|
| 15           |
In this example, toDayOfMonth extracts the day (15) from the given date.

​
toDayOfWeek

Returns the number of the day within the week for a date or date with time. Syntax 
toDayOfWeek(t[, mode[, timezone]])
Arguments
  • t (Date, Date32, DateTime, or DateTime64): Date or date with time.
  • mode (UInt8, optional): Determines the first day of the week and return value range. Default is 0.
  • timezone (String, optional): Timezone for the returned value.
Modes
ModeFirst day of weekRange
0Monday1-7 (Monday = 1, …, Sunday = 7)
ותוMonday0-6 (Monday = 0, …, Sunday = 6)
2Sunday0-6 (Sunday = 0, Monday = 1, …, Saturday = 6)
3Sunday1-7 (Sunday = 1, Monday = 2, …, Saturday = 7)
Returns:
  • The day of the week as an integer, depending on the chosen mode.
Example 
SELECT
	toDayOfWeek(toDateTime('2023-04-21')),
	toDayOfWeek(toDateTime('2023-04-21'), 1)
Result: 
| toDayOfWeek(toDateTime('2023-04-21')) | toDayOfWeek(toDateTime('2023-04-21'), 1) |
|---------------------------------------|------------------------------------------|
| 5                                     | 4                                        |
In this example, April 21, 2023 was a Friday. The first query returns 5 (Friday in mode 0), while the second query returns 4 (Friday in mode 1).
If the first argument is a string, it will be parsed as a date using the format supported by parseDateTime64BestEffort(). However, using string arguments is not recommended for performance reasons.

​
toHour

Returns the hour component (0-23) of a date with time.
Assumes that if clocks are moved ahead, it is by one hour and occurs at 2 a.m., and if clocks are moved back, it is by one hour and occurs at 3 a.m. (which is not always exactly when it occurs - it depends on the timezone).
Syntax: 
toHour(value)
Alias:
  • HOUR
Arguments:
  • value (DateTime or DateTime64): A date/time value.
Returns:
  • The hour of the day (0 - 23) of the given date/time. [UInt8]
Example: 
SELECT
	toHour(toDateTime('2023-04-21 10:20:30')) AS taco_hour;
Result: 
| taco_hour |
|-----------|
| 10        |
In this example, we extract the hour (10) from the given timestamp, which could represent the hour when a taco shop opens.

​
toMinute

Returns the minute component (0-59) of a date with time. Syntax: 
toMinute(value)
Alias:
  • MINUTE
Arguments:
  • value (DateTime or DateTime64): A DateTime or DateTime64 value.
Returns:
  • The minute of the hour (0 - 59) of the given date/time.
  • Type: UInt8.
Example: 
SELECT
	toMinute(toDateTime('2023-04-21 10:20:30')) AS taco_minute;
Result: 
| taco_minute |
|-------------|
| 20          |
In this example, we extract the minute (20) from the given timestamp of a taco order placement.

​
toSecond

Returns the second component (0-59) of a date with time. Leap seconds are not considered. Syntax: 
toSecond(value)
Alias:
  • SECOND
Arguments:
  • value (DateTime or DateTime64): A DateTime or DateTime64 value.
Returns:
  • The second in the minute (0 - 59) of the given date/time. [UInt8]
Example: 
SELECT
	toSecond(toDateTime('2023-04-21 10:20:30')) AS taco_order_second;
Result: 
| taco_order_second |
|-------------------|
| 30                |
In this example, we extract the second (30) from the timestamp of a taco order placed at 10:20:30.

​
toMillisecond

Returns the millisecond component (0-999) of a date with time. Syntax 
toMillisecond(value)
Arguments
  • value (DateTime or DateTime64): The input date and time value.
Returns:
  • The millisecond in the second (0-999) of the given date/time.
  • Type: UInt16
Example 
SELECT
	toMillisecond(toDateTime64('2023-04-21 10:20:30.456', 3)) AS milliseconds;
Result: 
| milliseconds |
|--------------|
| 456          |
This function is particularly useful when working with high-precision timestamps or when you need to extract the millisecond component for time-based calculations or grouping operations.
If the input is a DateTime (which doesn’t store milliseconds), the function will always return 0.

​
toUnixTimestamp

Converts a date, date with time, or string-encoded date/time to a Unix timestamp. Syntax 
toUnixTimestamp(date)
toUnixTimestamp(str, [timezone])
Arguments
  • date (Date, DateTime, or DateTime64): Date or date with time.
  • str (String): String representing a date or date with time.
  • timezone (String, optional): Timezone for the returned value.
Returns
  • The Unix timestamp. (UInt32).
Example 
SELECT
	'2023-05-05 12:30:00' AS datetime_str,
	toUnixTimestamp(datetime_str) AS unix_timestamp,
	toUnixTimestamp(datetime_str, 'America/Los_Angeles') AS unix_timestamp_la;
Result: 
| datetime_str        | unix_timestamp | unix_timestamp_la |
|---------------------|----------------|-------------------|
| 2023-05-05 12:30:00 | 1683289800     | 1683314400        |
In this example:
  • unix_timestamp is calculated using the server’s timezone.
  • unix_timestamp_la is calculated using the ‘America/Los_Angeles’ timezone.
When using the string format, the function accepts an optional timezone argument. This allows you to specify the timezone of the input string if it’s different from the server’s timezone.

​
toStartOfYear

Rounds down a date or date with time to the first day of the year. Syntax 
toStartOfYear(value)
Arguments
  • value (Date, Date32, DateTime, or DateTime64): Date or date with time.
Returns
  • The first day of the year for the given date/time. Date.
Example 
SELECT
	toStartOfYear(toDateTime('2023-04-21 10:20:30')) AS start_of_year;
Result: 
| start_of_year |
|---------------|
| 2023-01-01    |
In this example, the function rounds down the given date (April 21, 2023) to the first day of the year (January 1, 2023).

​
toStartOfISOYear

Rounds down a date or date with time to the first day of the ISO year. Syntax 
toStartOfISOYear(value)
Arguments
  • value (Date, Date32, DateTime, or DateTime64): Date or date with time.
Returns
  • The first day of the ISO year for the given date/time. Date.
Example 
SELECT
	toStartOfISOYear(toDate('2023-04-21')) AS iso_year_start;
Result: 
| iso_year_start |
|----------------|
| 2023-01-02     |
In this example, the ISO year for April 21, 2023 starts on January 2, 2023, because that’s the first Monday of the year, which is how ISO weeks are defined.
The ISO year can differ from the calendar year. For dates close to the new year, toStartOfISOYear may return a date from the previous or next calendar year.

​
toStartOfQuarter

Rounds down a date or date with time to the first day of the quarter. The first day of the quarter is either January 1, April 1, July 1, or October 1. Syntax 
toStartOfQuarter(value)
Arguments
  • value (Date, Date32, DateTime, or DateTime64): Date or date with time.
Returns
  • The first day of the quarter for the given date/time. Date.
Example 
SELECT toStartOfQuarter(toDateTime('2023-04-21 10:20:30')) AS quarter_start;
Result: 
| quarter_start |
|---------------|
| 2023-04-01    |
In this example, the function rounds down April 21, 2023 to April 1, 2023, which is the first day of the second quarter.

​
toStartOfMonth

Rounds down a date or date with time to the first day of the month. Syntax 
toStartOfMonth(value)
Arguments
  • value (Date, Date32, DateTime, or DateTime64): Date or date with time.
Returns
  • The first day of the month for the given date/time. Date.
Example 
SELECT
	toStartOfMonth(toDateTime('2023-04-21 10:20:30')) AS start_of_month;
Result: 
| start_of_month |
|----------------|
| 2023-04-01     |
This function rounds down the input date to the first day of its month. In this taco-themed example, we’re finding the start of the month for a hypothetical Taco Tuesday event date.
The behavior of parsing incorrect dates is implementation specific. ClickHouse may return zero date, throw an exception, or do “natural” overflow.

​
toLastDayOfMonth

Rounds a date or date with time to the last day of the month. Returns the date. Syntax 
toLastDayOfMonth(value)
Alias:
  • LAST_DAY
Arguments
  • value (Date, Date32, DateTime, or DateTime64): A date or date with time.
Returns:
  • The last day of the month for the given date/time. Date.
Example 
SELECT
	toLastDayOfMonth(toDateTime('2023-04-21 10:20:30')) AS last_day_of_month;
Result: 
| last_day_of_month |
|-------------------|
| 2023-04-30        |
In this example, we pass a date in April 2023 to toLastDayOfMonth. The function returns the last day of April, which is the 30th.

​
toMonday

Rounds down a date or date with time to the nearest Monday. Returns the date. Syntax 
toMonday(value)
Arguments
  • value (Date, Date32, DateTime, or DateTime64): A date or date with time.
Returns:
  • The date of the nearest Monday on or prior to the given date. Date.
Example 
SELECT
	toMonday(toDateTime('2023-04-21 10:20:30')) AS friday_to_monday,
	toMonday(toDate('2023-04-24')) AS monday_to_monday;
Result: 
| friday_to_monday | monday_to_monday |
|------------------|------------------|
| 2023-04-17       | 2023-04-24       |
In this example:
  • For the Friday date ‘2023-04-21’, toMonday returns the previous Monday ‘2023-04-17’.
  • For the Monday date ‘2023-04-24’, toMonday returns the same date since it’s already a Monday.
This function is useful for grouping dates by week, ensuring all dates within the same week are associated with the same Monday date. Arguments
  • t (Date, Date32, DateTime, or DateTime64) — Date or date with time.
  • mode (UInt8, optional) — Determines the first day of the week:
    • 0 (default): Sunday
    • 1: Monday
  • timezone (String, optional) — Timezone parameter.
Returns:
  • The date of the nearest Sunday or Monday on or prior to the given date, depending on the mode. (Date).
Example 
SELECT
	toStartOfWeek(toDateTime('2023-04-21 10:20:30')) AS default_mode,
	toStartOfWeek(toDateTime('2023-04-21 10:20:30'), 1) AS monday_mode,
	toStartOfWeek(toDate('2023-04-24')) AS date_default,
	toStartOfWeek(toDate('2023-04-24'), 1) AS date_monday
Result: 
| default_mode | monday_mode | date_default | date_monday |
|--------------|-------------|--------------|-------------|
| 2023-04-16   | 2023-04-17  | 2023-04-23   | 2023-04-24  |
In this example:
  • default_mode and date_default round down to the previous Sunday
  • monday_mode and date_monday round down to the previous Monday (or stay on Monday if it’s already a Monday)
This function is useful for grouping dates by week, especially when you need to align with different week-starting conventions.

​
toLastDayOfWeek

Rounds a date or date with time up to the nearest Saturday or Sunday. The mode argument works exactly like the mode argument in function toWeek(). If no mode is specified, mode is assumed as 0. Syntax 
toLastDayOfWeek(t[, mode[, timezone]])
Arguments
  • t (Date, Date32, DateTime, or DateTime64): A date or date with time.
  • mode (UInt8, optional): Determines the last day of the week as described in the toWeek function.
  • timezone (String, optional): It behaves like any other conversion function.
Returns
  • The date of the nearest Sunday or Monday on or after the given date, depending on the mode. (Date).
Example 
SELECT
	toLastDayOfWeek(toDateTime('2023-04-21 10:20:30')), -- a Friday
	toLastDayOfWeek(toDateTime('2023-04-21 10:20:30'), 1), -- a Friday
	toLastDayOfWeek(toDate('2023-04-22')), -- a Saturday
	toLastDayOfWeek(toDate('2023-04-22'), 1) -- a Saturday
Result: 
| toLastDayOfWeek(toDateTime('2023-04-21 10:20:30')) | toLastDayOfWeek(toDateTime('2023-04-21 10:20:30'), 1) | toLastDayOfWeek(toDate('2023-04-22')) | toLastDayOfWeek(toDate('2023-04-22'), 1) |
|---------------------------------------------------|------------------------------------------------------|---------------------------------------|------------------------------------------|
| 2023-04-22                                        | 2023-04-23                                           | 2023-04-22                            | 2023-04-23                               |
In this example:
  • For mode 0 (default), the week ends on Saturday.
  • For mode 1, the week ends on Sunday.
  • When the input date is already the last day of the week for the given mode, it returns that date.

​
toStartOfDay

Rounds down a date or date with time to the start of the day. Syntax 
toStartOfDay(value)
Arguments
  • value (Date, Date32, DateTime, or DateTime64): Date or date with time.
Returns:
  • The start of the day for the given date/time. DateTime.
Example 
SELECT
	toStartOfDay(toDateTime('2023-04-21 10:20:30')) AS start_of_day;
Result: 
| start_of_day         |
|----------------------|
| 2023-04-21 00:00:00  |
In this example, toStartOfDay rounds down the given date and time to the start of the day (midnight).

​
toStartOfHour

Rounds down a date with time to the start of the hour. Syntax: 
toStartOfHour(value)
Arguments:
  • value (DateTime or DateTime64): A date with time.
Returns:
  • The start of the hour for the given date and time.
  • Type: DateTime.
Example: 
SELECT
	toStartOfHour(toDateTime('2023-04-21 10:20:30')) AS rounded_hour,
	toStartOfHour(toDateTime64('2023-04-21 10:20:30.5300', 4)) AS rounded_hour_precise;
Result: 
| rounded_hour         | rounded_hour_precise     |
|----------------------|--------------------------|
| 2023-04-21 10:00:00  | 2023-04-21 10:00:00.0000 |
In this example:
  • rounded_hour shows the start of the hour for a regular DateTime.
  • rounded_hour_precise demonstrates that the function also works with DateTime64, preserving the precision but setting the minutes, seconds, and sub-seconds to zero.
This function is useful for grouping or filtering data by hourly intervals, especially in time series analysis or when working with log data.

​
toStartOfMinute

Rounds down a date with time to the start of the minute. Syntax 
toStartOfMinute(value)
Arguments
  • value (DateTime or DateTime64): A date with time.
Returns:
  • The start of the minute for the given date and time.
  • Type: DateTime
Example Query: 
SELECT
	toStartOfMinute(toDateTime('2023-04-21 10:20:30')) AS start_of_minute,
	toStartOfMinute(toDateTime64('2023-04-21 10:20:30.5300', 4)) AS start_of_minute_64;
Result: 
| start_of_minute      | start_of_minute_64        |
|----------------------|---------------------------|
| 2023-04-21 10:20:00  | 2023-04-21 10:20:00.0000  |
In this example:
  • start_of_minute shows the result for a DateTime input
  • start_of_minute_64 shows the result for a DateTime64 input with precision of 4 decimal places
This function is useful for grouping or filtering data by minute intervals, discarding the seconds and sub-second components.

​
toStartOfSecond

Truncates sub-seconds from a DateTime64 value. Syntax 
toStartOfSecond(value[, timezone])
Arguments
  • value (DateTime64): Date and time.
  • timezone (String, optional): Timezone for the returned value.
Returns
  • Input value without sub-seconds. (DateTime64).
Example Query without timezone: 
SELECT
	toStartOfSecond(toDateTime64('2020-01-01 10:20:30.999', 3)) AS taco_time;
Result: 
| taco_time               |
|-------------------------|
| 2020-01-01 10:20:30.000 |
Query with timezone: 
SELECT
	toStartOfSecond(toDateTime64('2020-01-01 10:20:30.999', 3), 'America/Los_Angeles') AS la_taco_time;
Result: 
| la_taco_time            |
|-------------------------|
| 2020-01-01 02:20:30.000 |
In this example, the function truncates the milliseconds from the taco order timestamp, giving us the exact second when the order was placed. The timezone example shows how the function adjusts for different taco-serving locations around the world.

​
toStartOfMillisecond

Truncates a DateTime64 value to the start of the millisecond. Syntax 
toStartOfMillisecond(value[, timezone])
Arguments
  • value (DateTime64): Date and time.
  • timezone (String, optional): Timezone for the returned value.
Returns
  • Input value truncated to the start of the millisecond. (DateTime64).
Example Query: 
SELECT
	toStartOfMillisecond(toDateTime64('2020-01-01 10:20:30.123456', 6)) AS taco_time;
Result: 
| taco_time                |
|--------------------------|
| 2020-01-01 10:20:30.123  |
In this example, we truncate a DateTime64 value representing a taco order timestamp to the start of the millisecond, preserving the millisecond precision but removing any microsecond components.

​
toStartOfMicrosecond

Truncates a DateTime64 value to the start of the microsecond. Syntax 
toStartOfMicrosecond(value[, timezone])
Arguments
  • value (DateTime64): Date and time.
  • timezone (String, optional): Timezone for the returned value.
Returns
  • Input value truncated to the start of the microsecond. (DateTime64).
Example Query: 
SELECT
	toStartOfMicrosecond(toDateTime64('2020-01-01 10:20:30.123456', 6)) AS truncated_datetime;
Result: 
| truncated_datetime        |
|---------------------------|
| 2020-01-01 10:20:30.123456|
Query with timezone: 
SELECT
	toStartOfMicrosecond(toDateTime64('2020-01-01 10:20:30.123456', 6), 'Europe/Moscow') AS truncated_datetime_moscow;
Result: 
| truncated_datetime_moscow  |
|----------------------------|
| 2020-01-01 13:20:30.123456 |
In this example, the function truncates the DateTime64 value to the start of the microsecond. When a timezone is specified, it adjusts the result accordingly. Syntax 
toStartOfNanosecond(value[, timezone])
Arguments
  • value (DateTime64): Date and time.
  • timezone (String, optional): Timezone for the returned value.
Returns:
  • Input value truncated to the start of the nanosecond. DateTime64.
Example Query: 
SELECT
	toStartOfNanosecond(toDateTime64('2020-01-01 10:20:30.123456789', 9)) AS truncated_time;
Result: 
| truncated_time                |
|-------------------------------|
| 2020-01-01 10:20:30.123456789 |
In this example, the function doesn’t change the input value because it’s already at the start of a nanosecond.
Query with timezone: 
SELECT
	toStartOfNanosecond(toDateTime64('2020-01-01 10:20:30.123456789', 9), 'Europe/Moscow') AS truncated_time_moscow;
Result: 
| truncated_time_moscow         |
|-------------------------------|
| 2020-01-01 13:20:30.123456789 |
Note that the time is adjusted for the Moscow timezone, which is 3 hours ahead of UTC.
This function is particularly useful when you need to align DateTime64 values to the start of nanoseconds, which can be helpful in high-precision time series analysis or when working with data from systems that provide nanosecond-level timestamps.

​
toStartOfFiveMinutes

Rounds down a date with time to the start of the five-minute interval. Syntax: 
toStartOfFiveMinutes(value)
Arguments:
  • value (DateTime or DateTime64): A date with time.
Returns:
  • The start of the five-minute interval of the given date/time.
  • Type: DateTime.
Example: 
SELECT
	toStartOfFiveMinutes(toDateTime('2023-04-21 10:17:00')) AS five_min_1,
	toStartOfFiveMinutes(toDateTime('2023-04-21 10:20:00')) AS five_min_2,
	toStartOfFiveMinutes(toDateTime('2023-04-21 10:23:00')) AS five_min_3;
Result: 
| five_min_1           | five_min_2           | five_min_3           |
|----------------------|----------------------|----------------------|
| 2023-04-21 10:15:00  | 2023-04-21 10:20:00  | 2023-04-21 10:20:00  |
In this taco-themed example, we’re rounding down various order times to the nearest five-minute interval. This could be useful for grouping taco orders into five-minute batches for efficient kitchen operations.

​
toStartOfTenMinutes

Rounds down a date with time to the start of the ten-minute interval. Syntax 
toStartOfTenMinutes(value)
Arguments
  • value (DateTime or DateTime64): A date with time.
Returns
  • The start of the ten-minute interval for the given date and time.
  • Type: DateTime.
Example 
SELECT
	toStartOfTenMinutes(toDateTime('2023-04-21 10:17:00')) AS interval1,
	toStartOfTenMinutes(toDateTime('2023-04-21 10:20:00')) AS interval2,
	toStartOfTenMinutes(toDateTime('2023-04-21 10:23:00')) AS interval3;
Result: 
| interval1            | interval2            | interval3            |
|----------------------|----------------------|----------------------|
| 2023-04-21 10:10:00  | 2023-04-21 10:20:00  | 2023-04-21 10:20:00  |
In this example:
  • 10:17:00 is rounded down to 10:10:00
  • 10:20:00 remains 10:20:00 as it’s already at the start of a ten-minute interval
  • 10:23:00 is rounded down to 10:20:00
This function is useful for grouping or analyzing data in ten-minute chunks, such as aggregating taco sales data by ten-minute intervals throughout the day.

​
toStartOfFifteenMinutes

Rounds down a date with time to the start of the fifteen-minute interval. Syntax: 
toStartOfFifteenMinutes(value)
Arguments:
  • value (DateTime or DateTime64): A date with time.
Returns:
  • The start of the fifteen-minute interval of the given date/time.
  • Type: DateTime.
Example: 
SELECT
	toStartOfFifteenMinutes(toDateTime('2023-05-15 10:17:00')) AS round_down_17,
	toStartOfFifteenMinutes(toDateTime('2023-05-15 10:30:00')) AS round_down_30,
	toStartOfFifteenMinutes(toDateTime('2023-05-15 10:45:59')) AS round_down_45
FROM
	taco_orders;
Result: 
| round_down_17         | round_down_30         | round_down_45         |
|-----------------------|-----------------------|-----------------------|
| 2023-05-15 10:15:00   | 2023-05-15 10:30:00   | 2023-05-15 10:45:00   |
In this example:
  • 10:17:00 is rounded down to 10:15:00
  • 10:30:00 remains 10:30:00 as it’s already at the start of a 15-minute interval
  • 10:45:59 is rounded down to 10:45:00
This function is useful for grouping time-series data into 15-minute buckets, which can be helpful for analyzing trends in taco order patterns throughout the day.

​
toStartOfInterval

Rounds down a date or date with time to the start of the specified interval. Syntax 
toStartOfInterval(time_or_data, INTERVAL x unit [, time_zone])
Arguments
  • time_or_data (Date, DateTime, or DateTime64): Date, DateTime, or DateTime64 to round down.
  • x (UInt8): Number of interval units.
  • unit (String): The type of interval. Possible values:
    • year, quarter, month, week, day
    • hour, minute, second
    • millisecond, microsecond, nanosecond
  • time_zone (String, optional): Optional timezone name (e.g. ‘America/New_York’).
Returns
  • Rounded down date or date with time, matching the type of the input.
Example 
SELECT
	toStartOfInterval(toDateTime('2023-03-15 13:27:30'), INTERVAL 10 MINUTE) AS rounded_time,
	toStartOfInterval(toDate('2023-03-15'), INTERVAL 1 MONTH) AS rounded_date;
Result: 
| rounded_time         | rounded_date |
|----------------------|--------------|
| 2023-03-15 13:20:00  | 2023-03-01   |
This function generalizes other toStartOf*() functions. For example:
  • toStartOfInterval(t, INTERVAL 1 YEAR) is equivalent to toStartOfYear(t)
  • toStartOfInterval(t, INTERVAL 1 MONTH) is equivalent to toStartOfMonth(t)
  • toStartOfInterval(t, INTERVAL 1 DAY) is equivalent to toStartOfDay(t)
  • toStartOfInterval(t, INTERVAL 15 MINUTE) is equivalent to toStartOfFifteenMinutes(t)
  • For WEEK intervals, weeks are considered to start on Monday.
  • Hour intervals are special: calculations are always performed relative to 00:00:00 (midnight) of the current day.

​
toTime

Converts a date with time to a certain fixed date, while preserving the time. Syntax: 
toTime(date[, timezone])
Arguments:
  • date (Date / DateTime / DateTime64): Date to convert to a time.
  • timezone (String, optional): Timezone for the returned value.
Returns:
  • DateTime with date equated to 1970-01-02 while preserving the time.
If the date input argument contained sub-second components, they will be dropped in the returned DateTime value with second-accuracy.
Example: 
SELECT
	toTime(toDateTime64('2023-09-15 12:30:45.6789', 4)) AS result,
	toTypeName(result) AS type;
Result: 
| result              | type     |
|---------------------|----------|
| 1970-01-02 12:30:45 | DateTime |
In this example:
  • The input 2023-09-15 12:30:45.6789 is converted to 1970-01-02 12:30:45.
  • The date is changed to the fixed date 1970-01-02.
  • The time 12:30:45 is preserved.
  • Sub-second precision (.6789) is dropped as the result is a DateTime type.
This function is useful when you need to compare or group times without considering the date component.

​
toRelativeYearNum

Converts a date or date with time to the number of years elapsed since a certain fixed point in the past. Syntax: 
toRelativeYearNum(date)
Arguments:
  • date (Date, Date32, DateTime, or DateTime64): Date or date with time.
Returns:
  • The number of years from a fixed reference point in the past. UInt16.
Example: 
SELECT
	toRelativeYearNum(toDate('2022-05-15')) AS relative_year,
	toRelativeYearNum(toDateTime('2022-05-15 12:30:00')) AS relative_year_time;
Result: 
| relative_year | relative_year_time |
|---------------|-------------------|
| 2022          | 2022              |
This example shows how toRelativeYearNum works with both Date and DateTime inputs, returning the year number relative to the internal reference point.
The fixed reference point is implementation-defined and may change in future versions. This function is primarily used for internal operations and comparisons rather than for displaying actual year values.

​
toRelativeQuarterNum

Converts a date or date with time to the number of quarters elapsed since a certain fixed point in the past. Syntax: 
toRelativeQuarterNum(date)
Arguments:
  • date (Date, Date32, DateTime, or DateTime64): Date or date with time.
Returns:
  • The number of quarters from a fixed reference point in the past. UInt32.
Example: 
SELECT
	toRelativeQuarterNum(toDate('2023-05-15')) AS quarters_since_fixed_point;
Result: 
| quarters_since_fixed_point |
|----------------------------|
| 8093                       |
In this example, we calculate the number of quarters that have elapsed between a fixed point in the past and May 15, 2023. The result of 8093 quarters indicates the relative position of this date in terms of quarters. This function is useful for time-based analysis and comparisons, especially when dealing with quarterly data or reports spanning multiple years.

​
toRelativeMonthNum

Converts a date or date with time to the number of months elapsed since a certain fixed point in the past. Syntax: 
toRelativeMonthNum(date)
Arguments:
  • date (Date, Date32, DateTime, or DateTime64): Date or date with time.
Returns:
  • The number of months from a fixed reference point in the past. UInt32.
Example: 
SELECT
	toRelativeMonthNum(toDate('2001-04-25')) AS m1,
	toRelativeMonthNum(toDate('2009-07-08')) AS m2
Result: 
| m1    | m2    |
|-------|-------|
| 24016 | 24115 |
In this example:
  • m1 shows the number of months from the reference point to April 25, 2001.
  • m2 shows the number of months from the reference point to July 8, 2009.
The difference between m2 and m1 (99) represents the number of months between these two dates.

​
toRelativeWeekNum

Converts a date or date with time to the number of weeks elapsed since a certain fixed point in the past. Syntax: 
toRelativeWeekNum(date)
Arguments:
  • date (Date, Date32, DateTime, or DateTime64): Date or date with time.
Returns:
  • The number of weeks from a fixed reference point in the past. UInt32.
Example: 
SELECT
	toRelativeWeekNum(toDate('2000-02-29')) AS w1,
	toRelativeWeekNum(toDate('2001-01-12')) AS w2;
Result: 
| w1   | w2   |
|------|------|
| 1574 | 1619 |
In this example:
  • w1 shows the number of weeks from the fixed point to February 29, 2000.
  • w2 shows the number of weeks from the fixed point to January 12, 2001.
This function is useful for calculating week-based metrics or for grouping data by weeks across long periods of time.

​
toRelativeDayNum

Converts a date or date with time to the number of days elapsed since a certain fixed point in the past. Syntax: 
toRelativeDayNum(date)
Arguments:
  • date (Date, Date32, DateTime, or DateTime64): Date or date with time.
Returns:
  • The number of days from a fixed reference point in the past. UInt32.
Example: 
SELECT toRelativeDayNum(toDate('2023-10-05')) AS days_since_taco_tuesday;
Result: 
| days_since_taco_tuesday |
|-------------------------|
| 19635                   |
In this example, we calculate the number of days that have passed since a fixed point in the past (which happens to be before the first Taco Tuesday) up to October 5, 2023.

​
toRelativeHourNum

Converts a date or date with time to the number of hours elapsed since a certain fixed point in the past. Syntax: 
toRelativeHourNum(date)
Arguments:
  • date (Date, Date32, DateTime, or DateTime64): Date or date with time.
Returns:
  • The number of hours from a fixed reference point in the past. UInt32.
Example: 
SELECT
	toRelativeHourNum(toDateTime('2023-05-05 12:30:00')) AS taco_order_hour,
	toRelativeHourNum(toDateTime('2023-05-05 18:45:00')) AS taco_delivery_hour;
Result: 
| taco_order_hour | taco_delivery_hour  |
|-----------------|---------------------|
| 468138          | 468144              |
In this example:
  • taco_order_hour shows the number of hours elapsed since the reference point to the taco order time.
  • taco_delivery_hour shows the number of hours elapsed to the taco delivery time, which is 6 hours later.
The difference between these two values (468144 - 468138 = 6) represents the 6-hour gap between order and delivery. Arguments:
  • date (Date, Date32, DateTime, or DateTime64): Date or date with time.
Returns:
  • The number of minutes from a fixed reference point in the past. UInt32.
Example: 
SELECT toRelativeMinuteNum(toDateTime('2023-05-05 12:30:00')) AS minutes_since_epoch;
Result: 
| minutes_since_epoch |
|---------------------|
| 27956430            |
This example calculates the number of minutes elapsed from a fixed point in the past (epoch) to May 5, 2023, at 12:30 PM. In this taco-themed example, we’ll use the function to calculate how long a taco order has been waiting: 
SELECT
	order_id,
	order_time,
	toRelativeMinuteNum(now()) - toRelativeMinuteNum(order_time) AS wait_time_minutes
FROM
	taco_orders
WHERE
	status = 'pending'
ORDER BY
	wait_time_minutes DESC
LIMIT 5;
Result: 
| order_id | order_time           | wait_time_minutes |
|----------|----------------------|-------------------|
| 1001     | 2023-05-05 11:45:00  | 45                |
| 1002     | 2023-05-05 11:55:00  | 35                |
| 1003     | 2023-05-05 12:10:00  | 20                |
| 1004     | 2023-05-05 12:20:00  | 10                |
| 1005     | 2023-05-05 12:25:00  | 5                 |
This query shows the top 5 pending taco orders with the longest wait times, helping the taco shop prioritize their order fulfillment.

​
toRelativeSecondNum

Converts a date or date with time to the number of seconds elapsed since a certain fixed point in the past. Syntax: 
toRelativeSecondNum(date)
Arguments:
  • date (Date, Date32, DateTime, or DateTime64): Date or date with time.
Returns:
  • The number of seconds from a fixed reference point in the past. UInt32.
Example: 
SELECT
	toRelativeSecondNum(toDateTime('2023-05-05 12:30:45')) AS seconds_since_epoch,
	toRelativeSecondNum(toDateTime('2023-05-05 12:31:15')) AS seconds_30s_later;
Result: 
| seconds_since_epoch | seconds_30s_later |
|---------------------|-------------------|
| 1683290945          | 1683290975        |
In this example:
  • We convert two timestamps to their relative second numbers.
  • The difference between the two values is 30 seconds (1683290975 - 1683290945 = 30).
This function is useful for calculating time differences or for sorting and grouping time-based data efficiently.

​
toISOYear

Converts a date or date with time to the ISO Year number. Syntax 
toISOYear(date)
Arguments
  • date (Date, Date32, DateTime, or DateTime64): Date or date with time.
Returns
  • The ISO Year number. Type: UInt16
Example 
SELECT toISOYear(toDate('2010-01-04')) AS iso_year;
Result: 
| iso_year |
|----------|
| 2009     |
In this example, January 4, 2010 falls in the ISO year 2009 because it’s part of the last week of 2009 in the ISO calendar.
The ISO year can differ from the calendar year for dates close to year boundaries. The ISO year starts with the Monday of the week containing January 4th.

​
toISOWeek

Converts a date or date with time to a UInt8 number containing the ISO Week number. Syntax: 
toISOWeek(date)
Arguments:
  • date (Date, Date32, DateTime, or DateTime64): Date or date with time.
Returns:
  • The ISO week number (1-53) of the given date/time. UInt8.
Example: 
SELECT
	toISOWeek(toDate('2023-04-21')) AS iso_week,
	toISOWeek(toDateTime('2023-12-31 23:59:59')) AS last_week_of_year;
Result: 
| iso_week | last_week_of_year |
|----------|-------------------|
| 16       | 52                |
In this example:
  • April 21, 2023 is in the 16th ISO week of the year.
  • December 31, 2023 is in the 52nd ISO week of the year.
The ISO week number may differ from the calendar week number. The first ISO week of a year is the week that contains the first Thursday of that year.
See also:
  • toWeek function for more flexible week numbering options.
  • toISOYear function to get the ISO year.

​
toWeek

Returns the week number for a date or datetime. The two-argument form allows you to specify whether the week starts on Sunday or Monday and whether the return value should be in the range from 0 to 53 or from 1 to 53. If the mode argument is omitted, the default mode is 0. The following table describes how the mode argument works:
ModeFirst day of weekRangeWeek 1 is the first week …
0Sunday0-53with a Sunday in this year
1Monday0-53with 4 or more days this year
2Sunday1-53with a Sunday in this year
3Monday1-53with 4 or more days this year
4Sunday0-53with 4 or more days this year
5Monday0-53with a Monday in this year
6Sunday1-53with 4 or more days this year
7Monday1-53with a Monday in this year
8Sunday1-53contains January 1
9Monday1-53contains January 1
For mode values with a meaning of “with 4 or more days this year,” weeks are numbered according to ISO 8601:1988:
  • If the week containing January 1 has 4 or more days in the new year, it is week 1.
  • Otherwise, it is the last week of the previous year, and the next week is week 1.
For mode values with a meaning of “contains January 1”, the week containing January 1 is week 1. It doesn’t matter how many days in the new year the week contained, even if it contained only one day. Syntax 
toWeek(t[, mode[, timezone]])
Alias:
  • WEEK
Arguments
  • t (Date or DateTime): Date or DateTime.
  • mode (UInt8, optional): Optional parameter, range of values is [0,9], default is 0.
  • timezone (String, optional): Optional parameter, it behaves like any other conversion function.
Returns:
  • The week number as a UInt8 value.
Example 
SELECT
	toDate('2024-05-27') AS date,
	toWeek(date) AS week0,
	toWeek(date, 1) AS week1,
	toWeek(date, 9) AS week9;
Result: 
| date       | week0 | week1 | week9 |
|------------|-------|-------|-------|
| 2024-05-27 | 21    | 22    | 22    |
In this example:
  • week0 (mode 0) returns 21 because it’s the 21st Sunday-starting week of 2024.
  • week1 (mode 1) returns 22 because it’s the 22nd Monday-starting week of 2024.
  • week9 (mode 9) returns 22 because it’s the 22nd Monday-starting week of 2024 that contains January 1.

​
toYearWeek

Returns the year and week number for a given date. The year in the result may be different from the year in the date argument for the first and the last week of the year. The function has two modes of operation:
  1. Single-argument syntax (uses mode value of 0)
  2. Two-argument syntax (allows specifying a mode)
Syntax 
toYearWeek(date)
toYearWeek(date, mode)
Arguments
  • date (Date, DateTime, or DateTime64): Date or date with time.
  • mode (UInt8, optional): Determines how the week is calculated.
The mode argument works like the mode argument in toWeek(). It affects which day is considered the first day of the week and whether the return value should be in the range 0-53 or 1-53. Returns
  • UInt32: A number containing the year and week number (YYYYWW).
Examples Query: 
SELECT
	toYearWeek('2024-01-01') AS year_week_default,
	toYearWeek('2024-01-01', 1) AS year_week_mode1,
	toYearWeek('2024-01-01', 3) AS year_week_mode3;
Result: 
| year_week_default | year_week_mode1 | year_week_mode3 |
|-------------------|-----------------|-----------------|
| 202401            | 202401          | 202401          |
Query: 
SELECT
	toYearWeek('2024-12-31') AS year_week_default,
	toYearWeek('2024-12-31', 1) AS year_week_mode1,
	toYearWeek('2024-12-31', 3) AS year_week_mode3;
Result: 
| year_week_default | year_week_mode1 | year_week_mode3 |
|-------------------|-----------------|-----------------|
| 202453            | 202453          | 202501          |
The week number returned by toYearWeek() can be different from what toWeek() returns. toWeek() always returns the week number in the context of the given year, while toYearWeek() may return a week number corresponding to the next year for dates at the end of December.
See Also

​
toDaysSinceYearZero

Returns the number of days passed since January 1, 0000 in the proleptic Gregorian calendar for a given date. Syntax 
toDaysSinceYearZero(date[, time_zone])
Alias:
  • TO_DAYS
Arguments
  • date (Date, Date32, DateTime, or DateTime64): The date to calculate the number of days from.
  • time_zone (String, optional): Optional timezone parameter.
Returns
  • The number of days passed since date 0000-01-01. (UInt32).
Example 
SELECT
	toDaysSinceYearZero(toDate('2023-09-08'));
Result: 
| toDaysSinceYearZero(toDate('2023-09-08')) |
|-------------------------------------------|
| 713569                                    |
In this example, we calculate the number of days passed since January 1, 0000 until September 8, 2023.
This function uses the same calculation method as MySQL’s TO_DAYS() function.
See Also

​
fromDaysSinceYearZero

Returns a date corresponding to the number of days passed since January 1, 0000 in the proleptic Gregorian calendar. Syntax 
fromDaysSinceYearZero(days)
Alias:
  • FROM_DAYS
Arguments
  • days (UInt32): The number of days passed since year zero.
Returns:
  • The date corresponding to the number of days passed since year zero. (Date)
Example 
SELECT
	fromDaysSinceYearZero(739136) AS date_from_days,
	fromDaysSinceYearZero(toDaysSinceYearZero(toDate('2023-09-08'))) AS original_date;
Result: 
| date_from_days | original_date |
|----------------|---------------|
| 2023-09-08     | 2023-09-08    |
In this example:
  • We convert 739136 days (which corresponds to September 8, 2023) back to a date.
  • We also demonstrate round-trip conversion by first converting ‘2023-09-08’ to days since year zero, then back to a date.
See Also
The result is undefined if it cannot be represented within the bounds of the Date type.

​
fromDaysSinceYearZero

Returns a date corresponding to the number of days passed since January 1, 0000 in the proleptic Gregorian calendar. Syntax 
fromDaysSinceYearZero(days)
Alias:
  • FROM_DAYS
Arguments
  • days (UInt32): The number of days passed since year zero.
Returns:
  • The date corresponding to the number of days passed since year zero. (Date)
Example 
SELECT
	fromDaysSinceYearZero(739136) AS date_from_days,
	fromDaysSinceYearZero(toDaysSinceYearZero(toDate('2023-09-08'))) AS original_date;
Result: 
| date_from_days | original_date |
|----------------|---------------|
| 2023-09-08     | 2023-09-08    |
In this example:
  • We convert 739136 days (which corresponds to September 8, 2023) back to a date.
  • We also demonstrate round-trip conversion by first converting ‘2023-09-08’ to days since year zero, then back to a date.
See Also
The result is undefined if it cannot be represented within the bounds of the Date type.

​
age

Calculates the difference between two dates or timestamps in the specified unit. Syntax: 
age(unit, startdate, enddate[, timezone])
Arguments:
  • unit (String): The unit to measure the difference in. Possible values:
    • ‘nanosecond’, ‘microsecond’, ‘millisecond’
    • ‘second’, ‘minute’, ‘hour’
    • ‘day’, ‘week’, ‘month’, ‘quarter’, ‘year’
  • startdate (Date, DateTime, or DateTime64): The earlier date/time.
  • enddate (Date, DateTime, or DateTime64): The later date/time.
  • timezone (optional String): The timezone to use for the calculation.
Returns: The difference between enddate and startdate expressed in the specified unit. [Int] Example: 
SELECT
	toDate('2024-01-01') AS end_date,
	toDate('2023-12-29') AS start_date,
	age('day', start_date, end_date) AS days_diff,
	age('month', start_date, end_date) AS months_diff,
	age('year', start_date, end_date) AS years_diff;
Result: 
| end_date   | start_date | days_diff | months_diff | years_diff |
|------------|------------|-----------|-------------|------------|
| 2024-01-01 | 2023-12-29 | 3         | 0           | 0          |
In this example:
  • The difference is 3 days
  • 0 months (since it’s within the same month)
  • 0 years (since it’s within the same year)
The age function calculates the difference using a precision of 1 nanosecond. For an alternative that counts unit boundaries crossed, see the date_diff function.

​
date_diff

Calculates the difference between two dates or timestamps in the specified unit. Syntax: 
date_diff(unit, startdate, enddate[, timezone])
Alias:
  • dateDiff
  • DATE_DIFF
  • timestampDiff
  • timestamp_diff
  • TIMESTAMP_DIFF
Arguments:
  • unit (String): The unit of time for the result. Possible values:
    • ‘nanosecond’, ‘microsecond’, ‘millisecond’
    • ‘second’, ‘minute’, ‘hour’
    • ‘day’, ‘week’, ‘month’, ‘quarter’, ‘year’
  • startdate (Date or DateTime or DateTime64): The earlier date or timestamp.
  • enddate (Date or DateTime or DateTime64): The later date or timestamp.
  • timezone (String, optional): Optional timezone name for the calculation.
Returns: The number of complete units between startdate and enddate. (Int) Example: 
SELECT
	date_diff('day', toDate('2023-05-01'), toDate('2023-05-15')) AS days_diff,
	date_diff('month', toDateTime('2023-01-01 00:00:00'), toDateTime('2023-12-31 23:59:59')) AS months_diff;
Result: 
| days_diff | months_diff |
|-----------|-------------|
| 14        | 11          |
In this example:
  • days_diff calculates the number of days between May 1st and May 15th, 2023.
  • months_diff calculates the number of complete months between the start and end of 2023.
  • The function uses relative units. For example, the difference between 2023-12-29 and 2024-01-01 is 3 days, 1 month, and 1 year.
  • For the ‘week’ unit, weeks are assumed to start on Monday.

​
date_trunc

Truncates a date or timestamp to the specified unit. Syntax 
date_trunc(unit, value[, timezone])
Arguments
  • unit (String): The unit to truncate to. [String Literal] Possible values (case-insensitive):
    • ‘second’
    • ‘minute’
    • ‘hour’
    • ‘day’
    • ‘week’
    • ‘month’
    • ‘quarter’
    • ‘year’
  • value (Date / DateTime / DateTime64): Date or timestamp to truncate.
  • timezone (String, optional): Timezone for the returned value.
Returns
  • Truncated date or timestamp. [DateTime]
Example 
SELECT
	date_trunc('month', toDateTime('2023-05-15 12:30:45')) AS truncated_month,
	date_trunc('day', toDateTime('2023-05-15 12:30:45'), 'America/Los_Angeles') AS truncated_day_la
Result: 
| truncated_month     | truncated_day_la    |
|---------------------|---------------------|
| 2023-05-01 00:00:00 | 2023-05-15 00:00:00 |
In this example:
  • truncated_month shows the date truncated to the start of the month.
  • truncated_day_la shows the date truncated to the start of the day in the Los Angeles timezone.
The date_trunc function is useful for grouping time series data by specific time intervals.

​
date_add

Adds a time interval to a date or date with time. Syntax: 
date_add(unit, value, date)
Alternative syntax: 
date_add(date, INTERVAL value unit)
Alias:
  • dateAdd
  • DATE_ADD
Arguments:
  • unit — The type of interval to add. Possible values:
    • second
    • minute
    • hour
    • day
    • week
    • month
    • quarter
    • year
  • value (Int) — Value of interval to add.
  • date (Date, Date32, DateTime, or DateTime64) — The date or date with time to which value is added.
Returns: Date or date with time obtained by adding value, expressed in unit, to date. Returns the same type as the input date. Example: 
SELECT date_add(YEAR, 3, toDate('2018-01-01')) as three_years_from_now;
Result: 
| three_years_from_now |
|----------------------|
| 2021-01-01           |
Using alternative syntax: 
SELECT date_add(toDate('2018-01-01'), INTERVAL 3 YEAR) as three_years_from_now;
Result: 
| three_years_from_now |
|----------------------|
| 2021-01-01           |
This function adds the specified time interval to the provided date or date with time. If the addition results in a value outside the bounds of the data type, the result is undefined.
The unit argument is not a string and should not be quoted.
See Also:

​
date_sub

Subtracts a time interval from a date or date with time. Syntax: 
date_sub(unit, value, date)
Alternative syntax: 
date_sub(date, INTERVAL value unit)
Arguments:
  • unit (String) — The type of interval to subtract. Possible values:
    • second
    • minute
    • hour
    • day
    • week
    • month
    • quarter
    • year
  • value (Int) — Number of interval units to subtract.
  • date (Date, Date32, DateTime, or DateTime64) — The date or date with time to subtract from.
Returns:
  • Date or date with time obtained by subtracting the specified interval. Same type as the input date.
Example: 
SELECT
	date_sub(YEAR, 3, toDate('2018-01-01')) as three_years_ago;
Result: 
| three_years_ago  |
|------------------|
| 2015-01-01       |
Using alternative syntax: 
SELECT
	date_sub(toDate('2018-01-01'), INTERVAL 3 YEAR);
Result: 
| minus(toDate('2018-01-01'), toIntervalYear(3)) |
|------------------------------------------------|
| 2015-01-01                                     |
This function is useful for performing date arithmetic, such as finding dates in the past relative to a given date.
If the subtraction results in a date outside the valid range for the data type, the result is undefined.
See Also:

​
timestamp_add

Adds the specified time interval to a date or date with time value. Syntax: 
timestamp_add(date, INTERVAL value unit)
Aliases:
  • timeStampAdd
  • TIMESTAMP_ADD
Arguments:
  • date (Date, Date32, DateTime, or DateTime64): Date or date with time.
  • value (Int): Value of interval to add.
  • unit (String): The type of interval to add. Possible values:
    • second
    • minute
    • hour
    • day
    • week
    • month
    • quarter
    • year
Returns: Date or date with time with the specified interval added. The return type matches the input type of date. Example: 
SELECT
	timestamp_add(toDate('2023-05-01'), INTERVAL 3 MONTH) AS taco_day;
Result: 
| taco_day   |
|------------|
| 2023-08-01 |
This example adds 3 months to May 1, 2023, resulting in the date for National Taco Day (August 1).
If the addition results in a value outside the bounds of the data type, the result is undefined.

​
timestamp_sub

Subtracts the time interval from the provided date or date with time. If the subtraction results in a value outside the bounds of the data type, the result is undefined. Syntax: 
timestamp_sub(unit, value, date)
Alias:
  • timeStampSub
  • TIMESTAMP_SUB
Arguments:
  • unit (String): The type of interval to subtract. Possible values:
    • second
    • minute
    • hour
    • day
    • week
    • month
    • quarter
    • year
  • value (Int): Value of interval to subtract.
  • date (Date, Date32, DateTime, or DateTime64): Date or date with time.
Returns: Date or date with time obtained by subtracting value, expressed in unit, from date. (Date, Date32, DateTime, or DateTime64). Example: 
SELECT
	timestamp_sub(MONTH, 5, toDateTime('2018-12-18 01:02:03')) as five_months_ago;
Result: 
| five_months_ago     |
|---------------------|
| 2018-07-18 01:02:03 |
In this example, we subtract 5 months from the given date and time, resulting in a new date and time 5 months earlier.

​
addDate

Adds a time interval to a date, date with time, or string-encoded date/time value. Syntax: 
addDate(date, interval)
Arguments:
  • date (Date, Date32, DateTime, DateTime64, or String): The date or date with time to which the interval is added.
  • interval (Interval): The interval to add.
Returns:
  • Date or date with time obtained by adding the interval to the input date. The return type matches the input type.
Example: 
SELECT
	addDate(toDate('2024-05-01'), INTERVAL 3 MONTH) AS taco_day;
Result: 
| taco_day   |
|------------|
| 2024-08-01 |
In this example, we add 3 months to May 1st, 2024, to find the date of a future taco celebration day.
If the addition results in a value outside the bounds of the data type, the result is undefined.
See Also:
  • date_add — An alternative function for adding intervals to dates.

​
subDate

Subtracts the time interval from the provided date, date with time or String-encoded date / date with time.
If the subtraction results in a value outside the bounds of the data type, the result is undefined.
Syntax: 
subDate(date, interval)
Alias:
  • SUBDATE
Arguments:
  • date (Date, Date32, DateTime, DateTime64, or String): The date or date with time from which interval is subtracted.
  • interval (Interval): Interval to subtract.
Returns: Date or date with time obtained by subtracting interval from date. (Date, Date32, DateTime or DateTime64). Example: 
SELECT
	subDate(toDate('2024-01-01'), INTERVAL 3 YEAR) AS three_years_ago;
Result: 
| three_years_ago |
|-----------------|
| 2021-01-01      |
In this example, we subtract 3 years from January 1, 2024, resulting in January 1, 2021. See Also:

​
now

Returns the current date and time at the moment of query analysis. The function is a constant expression. Syntax: 
now([timezone])
Alias:
  • current_timestamp
Arguments:
  • timezone (String, optional) — Timezone name for the returned value.
Returns:
  • Current date and time. (DateTime)
Example: Query without timezone: 
SELECT now();
Result: 
| now()               |
|---------------------|
| 2024-03-03 12:42:09 |
Query with the specified timezone: 
SELECT now('America/Los_Angeles');
Result: 
| now('America/Los_Angeles') |
|----------------------------|
| 2024-03-03 04:42:23        |
This function returns the current date and time at the moment the query is analyzed. It’s important to note that for long-running queries, this time will remain constant throughout the query execution.
For scenarios where you need the current time for each row in long-running INSERT SELECT queries, consider using the nowInBlock() function instead.

​
now64

Returns the current date and time with sub-second precision at the moment of query analysis. The function is a constant expression. Syntax: 
now64([scale[, timezone]])
Arguments:
  • scale (UInt8, optional): Tick size (precision): 10^-precision seconds. Valid range: [0:9]. Typically used values:
    • 3 (default) - milliseconds
    • 6 - microseconds
    • 9 - nanoseconds
  • timezone (String, optional): Optional timezone for the returned value.
Returns: Current date and time with sub-second precision. [DateTime64] Example: 
SELECT
	now64(),
	now64(9, 'America/Los_Angeles') AS now_la;
Result: 
| now64()                 | now_la                         |
|-------------------------|--------------------------------|
| 2024-03-15 12:30:45.123 | 2024-03-15 05:30:45.123456789 |
In this example:
  • now64() returns the current time with millisecond precision in the server’s timezone.
  • now64(9, 'America/Los_Angeles') returns the current time with nanosecond precision in the Los Angeles timezone.
The actual values will differ based on when and where you run the query.

​
nowInBlock

Returns the current date and time at the moment of processing each block of data. Unlike the now() function, it is not a constant expression, and the returned value will be different in different blocks for long-running queries. This function is useful for generating the current time in long-running INSERT SELECT queries. Syntax 
nowInBlock([timezone])
Arguments
  • timezone (String, optional): Timezone name for the returned value.
Returns
  • Current date and time at the moment of processing each block of data. (DateTime)
Example 
SELECT
	now() AS static_time,
	nowInBlock() AS block_time,
	sleep(1)
FROM
	numbers(3)
Result: 
| static_time         | block_time          | sleep(1) |
|---------------------|---------------------|----------|
| 2024-03-05 12:00:00 | 2024-03-05 12:00:00 | 0        |
| 2024-03-05 12:00:00 | 2024-03-05 12:00:01 | 0        |
| 2024-03-05 12:00:00 | 2024-03-05 12:00:02 | 0        |
In this taco-themed example, static_time remains constant as it’s evaluated once at query analysis, while block_time updates for each block, simulating a scenario where you might be processing taco orders in real-time across different time blocks.
The sleep(1) function is used to introduce a delay between blocks, making the time difference more noticeable. In real-world scenarios, this delay would be replaced by actual data processing.

​
today

Returns the current date at the moment of query analysis. Syntax: 
today()
Alias:
  • curdate()
  • current_date()
Returns:
  • The current date. (Date)
Example: 
SELECT
	today() AS today,
	today() + 1 AS tomorrow,
	today() - 1 AS yesterday
Result: 
| today      | tomorrow   | yesterday  |
|------------|------------|------------|
| 2024-03-03 | 2024-03-04 | 2024-03-02 |
This function is equivalent to toDate(now()).
The result is calculated at query analysis time, not at execution time. This means the function returns the same value for all rows in a query, even if the query takes a long time to execute.

​
yesterday

Returns the date of the previous day. Syntax: 
yesterday()
Returns: A Date value representing yesterday’s date. Example: 
SELECT
	today() AS today,
	yesterday() AS yesterday,
	yesterday() = today() - 1 AS is_correct
FROM taco_sales
LIMIT 1;
Result: 
| today      | yesterday  | is_correct |
|------------|------------|------------|
| 2023-09-15 | 2023-09-14 | 1          |
This example demonstrates that yesterday() returns the date immediately before today(), which is equivalent to subtracting 1 day from today’s date.
The yesterday() function is a constant expression, meaning it returns the same value for all rows in the query and is evaluated once at query analysis time.

​
timeSlot

Rounds the time to the half hour. Syntax: 
timeSlot(time)
Arguments:
  • time (DateTime or DateTime64): Date with time.
Returns:
  • DateTime rounded down to the nearest half hour.
  • Type: DateTime.
Example: 
SELECT
	timeSlot(toDateTime('2023-05-15 12:17:00')) AS rounded_time,
	timeSlot(toDateTime('2023-05-15 12:45:00')) AS next_slot
Result: 
| rounded_time         | next_slot            |
|----------------------|----------------------|
| 2023-05-15 12:00:00  | 2023-05-15 12:30:00  |
In this example:
  • ‘12:17:00’ is rounded down to ‘12:00:00’
  • ‘12:45:00’ is rounded down to ‘12:30:00’
This function is useful for grouping events into half-hour intervals, which can be helpful for time-based analysis of taco sales or other time-sensitive data.

​
toYYYYMM

Converts a date or date with time to a UInt32 number containing the year and month number (YYYY * 100 + MM). Syntax: 
toYYYYMM(date[, timezone])
Arguments:
  • date (Date, Date32, DateTime, or DateTime64): Date or date with time.
  • timezone (String, optional): Timezone name. If provided, it must be a string constant.
Returns:
  • A UInt32 number representing the year and month (YYYYMM).
Example: 
SELECT
	toYYYYMM(toDate('2024-03-15')) AS taco_month;
Result: 
| taco_month |
|------------|
| 202403     |
This function is useful for grouping dates by year and month, which can be handy for analyzing taco sales trends over time.
This function is the opposite of the YYYYMMDDToDate() function.

​
toYYYYMMDD

Converts a date or date with time to a UInt32 number containing the year, month, and day (YYYY * 10000 + MM * 100 + DD). Syntax 
toYYYYMMDD(date[, timezone])
Arguments
  • date (Date, Date32, DateTime, or DateTime64): Date or date with time.
  • timezone (String, optional): Timezone name. If provided, it must be a string constant.
Returns
  • A UInt32 number representing the date in YYYYMMDD format.
Example 
SELECT
	toYYYYMMDD(now(), 'US/Eastern') AS date_as_number;
Result: 
| date_as_number |
|----------------|
| 20240302       |
This example assumes the current date is March 2, 2024, in the US/Eastern timezone.
This function is the opposite of the YYYYMMDDToDate function.

​
toYYYYMMDDhhmmss

Converts a date or date with time to a UInt64 number containing the year, month, day, hour, minute and second components. Syntax 
toYYYYMMDDhhmmss(date[, timezone])
Arguments
  • date (Date, DateTime, or DateTime64): Date or date with time.
  • timezone (String, optional): Timezone for the returned value.
Returns
  • A UInt64 number in the format YYYYMMDDhhmmss.
Example 
SELECT toYYYYMMDDhhmmss(now(), 'America/Los_Angeles') AS taco_time;
Result: 
| taco_time      |
|----------------|
| 20240305112209 |
In this example, we convert the current timestamp to the YYYY-MM-DD-hh-mm-ss format in the Los Angeles timezone. The result shows when it’s taco time on the West Coast!
This function is useful for generating compact datetime representations for sorting or unique identifiers. It’s particularly handy when you need to order your taco delivery timestamps!

​
YYYYMMDDToDate

Converts a number containing the year, month and day to a Date. Syntax: 
YYYYMMDDToDate(yyyymmdd)
Arguments:
  • yyyymmdd (Integer, Float, or Decimal): A number representing the year, month and day.
Returns:
  • A date created from the arguments. (Date).
This function is the opposite of the toYYYYMMDD() function.
The output is undefined if the input does not encode a valid Date value.
Example: 
SELECT
	YYYYMMDDToDate(20230911) AS taco_tuesday;
Result: 
| taco_tuesday |
|--------------|
| 2023-09-11   |
In this example, we convert the numeric representation of September 11, 2023 (which happens to be a Monday, not a Tuesday) to a Date value. This could be useful for processing dates of taco-related events or promotions stored in a compact numeric format.

​
YYYYMMDDToDate32

Converts a number containing the year, month and day to a Date32 value. Syntax 
YYYYMMDDToDate32(yyyymmdd)
Arguments
  • yyyymmdd (numeric): A number representing the year, month and day. Integer, Float or Decimal.
Returns
  • A date created from the arguments. Date32.
Example 
SELECT YYYYMMDDToDate32(20230911) AS taco_tuesday;
Result: 
| taco_tuesday |
|--------------|
| 2023-09-11   |
In this example, we convert the number 20230911 (representing September 11, 2023) to a Date32 value. This could be useful for converting numeric date representations into actual date values, perhaps for a taco shop’s event planning or sales analysis.
The output is undefined if the input does not encode a valid Date32 value. Always ensure your input represents a valid date to avoid unexpected results.

​
YYYYMMDDhhmmssToDateTime

Converts a string containing a date and time in the format ‘YYYYMMDDHHMMSS’ to a DateTime value. Syntax 
YYYYMMDDhhmmssToDateTime(datetime_string[, timezone])
Arguments
  • datetime_string (String): A string in the format ‘YYYYMMDDHHMMSS’.
  • timezone (String, optional): Optional parameter specifying the timezone for the returned value.
Returns
  • A DateTime value created from the input string. (DateTime)
Example 
SELECT YYYYMMDDhhmmssToDateTime('20230911131415') AS taco_order_time;
Result: 
| taco_order_time        |
|------------------------|
| 2023-09-11 13:14:15    |
In this example, we convert a string representing a taco order timestamp to a DateTime value.
If the input string does not represent a valid date and time, the function will return an undefined result. Always ensure your input is in the correct format to avoid unexpected behavior.

​
YYYYMMDDhhmmssToDateTime64

Converts a string containing a date and time in the format ‘YYYYMMDDHHMMSS’ to a DateTime64 value. Syntax 
YYYYMMDDhhmmssToDateTime64(datetime_string[, precision[, timezone]])
Arguments
  • datetime_string (String): A string in the format ‘YYYYMMDDHHMMSS’.
  • precision (UInt8, optional): The precision of the resulting DateTime64. Default is 3 (milliseconds).
  • timezone (String, optional): The timezone to use for the conversion.
Returns
  • A DateTime64 value representing the input date and time. (DateTime64)
Example 
SELECT YYYYMMDDhhmmssToDateTime64('20230915120000', 3, 'UTC') AS taco_time;
Result: 
| taco_time                |
|--------------------------|
| 2023-09-15 12:00:00.000  |
In this example, we convert a string representing September 15, 2023 at 12:00:00 PM to a DateTime64 value with millisecond precision in the UTC timezone. This could be useful for tracking the exact time a taco order was placed.
If the input string is not in the correct format or represents an invalid date/time, the function will throw an exception. Always validate your input data to ensure it’s in the correct format before using this function.

​
changeYear

Changes the year component of a date or date time. Syntax: 
changeYear(date_or_datetime, value)
Arguments:
  • date_or_datetime (Date, Date32, DateTime, or DateTime64): A date or date time value.
  • value (Int32): A new value of the year.
Returns: The same type as date_or_datetime. Example: 
SELECT
	changeYear(toDate('1999-01-01'), 2000),
	changeYear(toDateTime64('1999-01-01 00:00:00.000', 3), 2000);
Result: 
| changeYear(toDate('1999-01-01'), 2000) | changeYear(toDateTime64('1999-01-01 00:00:00.000', 3), 2000) |
|----------------------------------------|--------------------------------------------------------------|
| 2000-01-01                             | 2000-01-01 00:00:00.000                                      |
In this example:
  • The year of the Date value is changed from 1999 to 2000.
  • The year of the DateTime64 value is changed from 1999 to 2000, preserving the time component.
The function preserves the month and day components of the original date, adjusting only the year. If the original date was February 29th of a leap year and the new year is not a leap year, the result will be February 28th of the new year.

​
changeMonth

Changes the month component of a date or date time. Syntax: 
changeMonth(date_or_datetime, value)
Arguments:
  • date_or_datetime (Date, Date32, DateTime, or DateTime64): A date or date time value.
  • value (Int8, Int16, Int32, or Int64): A new value of the month.
Returns: The same type as date_or_datetime. Example: 
SELECT
	changeMonth(toDate('1999-01-01'), 2) AS new_date,
	changeMonth(toDateTime64('1999-01-01 00:00:00.000', 3), 2) AS new_datetime;
Result: 
| new_date   | new_datetime        |
|------------|---------------------|
| 1999-02-01 | 1999-02-01 00:00:00 |
In this example:
  • new_date changes the month of the date to February (2).
  • new_datetime changes the month of the date time to February (2) while preserving the time component.
This function is useful for date manipulation in taco-related queries, such as adjusting promotion periods or analyzing seasonal taco sales trends.

​
changeDay

Changes the day component of a date or date time. Syntax: 
changeDay(date_or_datetime, value)
Arguments:
  • date_or_datetime (Date, Date32, DateTime, or DateTime64): A date or date with time.
  • value (Integer): New value for the day.
Returns:
  • Returns a value of the same type as date_or_datetime.
Example: 
SELECT
	changeDay(toDate('2023-05-05'), 15) AS new_date,
	changeDay(toDateTime64('2023-05-05 12:30:45.123', 3), 15) AS new_datetime
Result: 
| new_date   | new_datetime             |
|------------|--------------------------|
| 2023-05-15 | 2023-05-15 12:30:45.123  |
In this example:
  • new_date changes the day of ‘2023-05-05’ to the 15th.
  • new_datetime changes the day of ‘2023-05-05 12:30:45.123’ to the 15th while preserving the time.
This function is useful for date manipulation in taco-related queries, such as adjusting delivery dates or modifying scheduled taco night events.

​
changeHour

Changes the hour component of a date or date time. Syntax: 
changeHour(date_or_datetime, value)
Arguments:
  • date_or_datetime (Date, Date32, DateTime, or DateTime64): A date or date with time.
  • value (Integer): New value for the hour.
Returns:
  • Returns a value of the same type as date_or_datetime.
  • If the input is a Date, it returns a DateTime.
  • If the input is a Date32, it returns a DateTime64.
Example: 
SELECT
	changeHour(toDate('2023-05-05'), 14) AS date_to_datetime,
	changeHour(toDateTime64('2023-05-05 10:20:30.123', 3), 14) AS change_hour_datetime64;
Result: 
| date_to_datetime     | change_hour_datetime64 |
|----------------------|------------------------|
| 2023-05-05 14:00:00  | 2023-05-05 14:20:30.123|
In this example:
  • date_to_datetime changes the hour of a Date to 14 and converts it to a DateTime.
  • change_hour_datetime64 changes the hour of a DateTime64 to 14 while preserving minutes, seconds, and milliseconds.
Note that when changing the hour of a Date, it’s automatically converted to a DateTime with the time set to the specified hour and 00:00 for minutes and seconds.

​
changeMinute

Changes the minute component of a date or date with time. Syntax: 
changeMinute(date_or_datetime, value)
Arguments:
  • date_or_datetime (Date, Date32, DateTime, or DateTime64): A date or date with time.
  • value (Integer): New value for the minute component.
Returns: Returns a value of the same type as date_or_datetime. If the input is a Date, it returns a DateTime. If the input is a Date32, it returns a DateTime64. Example: 
SELECT
	changeMinute(toDate('2023-05-01'), 30) AS date_change,
	changeMinute(toDateTime64('2023-05-01 12:00:00.000', 3), 45) AS datetime_change;
Result: 
| date_change          | datetime_change           |
|----------------------|---------------------------|
| 2023-05-01 00:30:00  | 2023-05-01 12:45:00.000   |
In this example:
  • date_change converts the Date to DateTime and sets the minute to 30.
  • datetime_change changes the minute of the DateTime64 to 45, preserving other components.
When applied to a Date, the function first converts it to DateTime at midnight (00:00:00) before changing the minute.

​
changeSecond

Changes the second component of a date or date with time. Syntax: 
changeSecond(date_or_datetime, value)
Arguments:
  • date_or_datetime (Date, Date32, DateTime, or DateTime64): A date or date with time.
  • value (Integer): New value for the second component.
Returns: Returns a value of the same type as date_or_datetime. If the input is a Date, it returns a DateTime. If the input is a Date32, it returns a DateTime64. Example: 
SELECT
	changeSecond(toDate('1999-01-01'), 15) AS date_change,
	changeSecond(toDateTime64('1999-01-01 00:00:00.000', 3), 15) AS date_time_change;
Result: 
| date_change          | date_time_change         |
|----------------------|--------------------------|
| 1999-01-01 00:00:15  | 1999-01-01 00:00:15.000  |
In this taco-themed example:
  • date_change shows changing the seconds of a Date to 15, which converts it to a DateTime.
  • date_time_change demonstrates changing the seconds of a DateTime64 to 15, preserving milliseconds.
When applied to a Date, the function implicitly converts it to DateTime at midnight before changing the second component.

​
addYears

Adds a specified number of years to a date or date with time. Syntax: 
addYears(date, num)
Arguments:
  • date (Date, Date32, DateTime, DateTime64, or String): Date or date with time to add years to.
  • num (numeric): Number of years to add.
Returns:
  • Date or date with time with added years. Type matches the input date type.
Example: 
SELECT
	addYears(toDate('2024-01-01'), 1) AS add_years_with_date,
	addYears(toDateTime('2024-01-01 00:00:00'), 1) AS add_years_with_date_time,
	addYears('2024-01-01 00:00:00', 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         |
In this taco-themed example, we’re adding a year to various dates, which could represent the expiration dates of different taco seasonings or the projected opening dates of new taco restaurants.

​
addQuarters

Adds a specified number of quarters to a date, date with time, or string-encoded date/time value. Syntax: 
addQuarters(date, num)
Arguments:
  • date (Date, Date32, DateTime, DateTime64, or String): Date or date with time to add quarters to.
  • num (numeric): Number of quarters to add.
Returns:
  • Date plus num quarters. Type matches input date type.
Example: 
SELECT
	addQuarters(toDate('2024-01-01'), 1) AS add_quarters_with_date,
	addQuarters(toDateTime('2024-01-01 00:00:00'), 1) AS add_quarters_with_date_time,
	addQuarters('2024-01-01 00:00:00', 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            |
In this example, we add one quarter to various date and time representations. The function adjusts for different month lengths and year boundaries automatically.

​
addMonths

Adds a specified number of months to a date or date with time. Syntax 
addMonths(date, num)
Arguments
  • date (Date, Date32, DateTime, DateTime64, or String): Date or date with time to add months to.
  • num (numeric): Number of months to add.
Returns
  • Date or date with time with added months. Same type as the input date.
Example 
SELECT
	addMonths(toDate('2024-01-01'), 6) AS add_months_with_date,
	addMonths(toDateTime('2024-01-01 00:00:00'), 6) AS add_months_with_date_time,
	addMonths('2024-01-01 00:00:00', 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          |
In this taco-themed example, we’re adding 6 months to various dates, which could represent the expiration dates for different taco seasonings or sauces in inventory.

​
addWeeks

Adds a specified number of weeks to a date, date with time, or string-encoded date/time. Syntax 
addWeeks(date, num)
Arguments
  • date (Date, Date32, DateTime, DateTime64, or String): Date or date with time to add weeks to.
  • num (numeric): Number of weeks to add.
Returns
  • Date plus num weeks. Type matches input date type.
Example 
SELECT
	addWeeks(toDate('2024-01-01'), 5) AS add_weeks_with_date,
	addWeeks(toDateTime('2024-01-01 00:00:00'), 5) AS add_weeks_with_date_time,
	addWeeks('2024-01-01 00:00:00', 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         |
In this example, we add 5 weeks to various date and time representations. The function handles Date, DateTime, and string inputs, returning the result in the corresponding format.
If the addition results in a date outside the valid range for the data type, the behavior is undefined.

​
addDays

Adds a specified number of days to a date, a date with time, or a string-encoded date/time. Syntax: 
addDays(date, num)
Arguments:
  • date (Date, Date32, DateTime, DateTime64, or String): Date or date with time to add days to.
  • num (numeric): Number of days to add.
Returns:
  • Date plus num days. Type matches input date type.
Example: 
SELECT
	addDays(toDate('2024-01-01'), 5) AS add_days_with_date,
	addDays(toDateTime('2024-01-01 00:00:00'), 5) AS add_days_with_date_time,
	addDays('2024-01-01 00:00:00', 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        |
This function adds the specified number of days to the given date or datetime. It handles various input formats and returns a result of the same type as the input. The function is useful for date arithmetic operations in queries involving taco sales data, event scheduling, or any time-based analysis.

​
addHours

Adds a specified number of hours to a date, date with time, or string-encoded date/time. Syntax: 
addHours(date, num)
Arguments:
  • date (Date, Date32, DateTime, DateTime64, or String): Date or date with time to add hours to.
  • num (numeric): Number of hours to add.
Returns:
  • Date plus num hours. Type matches input date type.
Example: 
SELECT
	addHours(toDate('2024-01-01'), 12) AS add_hours_with_date,
	addHours(toDateTime('2024-01-01 00:00:00'), 12) AS add_hours_with_date_time,
	addHours('2024-01-01 00:00:00', 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         |
In this example:
  • add_hours_with_date adds 12 hours to a Date, resulting in a DateTime.
  • add_hours_with_date_time adds 12 hours to a DateTime.
  • add_hours_with_date_time_string adds 12 hours to a string-encoded date/time.
If adding hours results in a date outside the valid range for the data type, the behavior is undefined.

​
addMinutes

Adds a specified number of minutes to a date, a date with time, or a string-encoded date/time. Syntax: 
addMinutes(date, num)
Arguments:
  • date (Date, Date32, DateTime, DateTime64, or String): Date or date with time to add specified number of minutes to.
  • num (numeric): Number of minutes to add.
Returns: Returns the input date plus num minutes. The return type matches the input type for Date/DateTime/DateTime64, or DateTime64 for string inputs. Example: 
SELECT
	addMinutes(toDate('2024-01-01'), 20) AS add_minutes_with_date,
	addMinutes(toDateTime('2024-01-01 00:00:00'), 20) AS add_minutes_with_date_time,
	addMinutes('2024-01-01 00:00:00', 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           |
In this example:
  • add_minutes_with_date adds 20 minutes to a Date, resulting in a DateTime.
  • add_minutes_with_date_time adds 20 minutes to a DateTime.
  • add_minutes_with_date_time_string adds 20 minutes to a string-encoded date/time, resulting in a DateTime64.
If the addition results in a value outside the bounds of the data type, the behavior is undefined.

​
addSeconds

Adds a specified number of seconds to a date, date with time, or string-encoded date/time. Syntax: 
addSeconds(date, num)
Arguments:
  • date (Date, Date32, DateTime, DateTime64, or String): Date or date with time to add seconds to.
  • num (numeric): Number of seconds to add.
Returns:
  • Date plus num seconds. Type matches input date type.
Example: 
SELECT
	addSeconds(toDate('2024-01-01'), 30) AS add_seconds_with_date,
	addSeconds(toDateTime('2024-01-01 00:00:00'), 30) AS add_seconds_with_date_time,
	addSeconds('2024-01-01 00:00:00', 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           |
In this example:
  • add_seconds_with_date adds 30 seconds to a Date, resulting in a DateTime.
  • add_seconds_with_date_time adds 30 seconds to a DateTime.
  • add_seconds_with_date_time_string adds 30 seconds to a string-encoded date/time, resulting in a DateTime64.
Note that when adding seconds to a Date, it automatically converts the result to a DateTime to represent the time component.

​
addMilliseconds

Adds a specified number of milliseconds to a date with time or a string-encoded date with time. Syntax: 
addMilliseconds(date_time, num)
Arguments:
  • date_time (DateTime / DateTime64, or String): Date with time to add specified number of milliseconds to.
  • num (numeric): Number of milliseconds to add.
Returns:
  • Returns date_time plus num milliseconds. DateTime64.
Example: 
SELECT
	addMilliseconds(toDateTime('2024-01-01 00:00:00'), 1000) AS add_milliseconds_with_date_time,
	addMilliseconds('2024-01-01 00:00:00', 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                |
In this example, we add 1000 milliseconds (1 second) to the given date and time. The function works with both DateTime objects and string representations of dates.

​
addMicroseconds

Adds a specified number of microseconds to a date with time or a string-encoded date with time. Syntax 
addMicroseconds(date_time, num)
Arguments
  • date_time (DateTime or DateTime64, String) — Date with time to add specified number of microseconds to.
  • num (numeric) — Number of microseconds to add.
Returns:
  • Returns date_time plus num microseconds. (DateTime64).
Example 
SELECT
	addMicroseconds(toDateTime('2024-01-01 00:00:00'), 1000000) AS add_microseconds_with_date_time,
	addMicroseconds('2024-01-01 00:00:00', 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             |
In this example, we add 1,000,000 microseconds (1 second) to the given date and time using both DateTime and String input formats. The result shows the updated time with microsecond precision.

​
addNanoseconds

Adds a specified number of nanoseconds to a date with time or a string-encoded date with time. Syntax 
addNanoseconds(date_time, num)
Arguments
  • date_time (DateTime or DateTime64, String): Date with time to add specified number of nanoseconds to.
  • num (Int, Float): Number of nanoseconds to add.
Returns:
  • Returns date_time plus num nanoseconds. (DateTime64).
Example 
SELECT
	addNanoseconds(toDateTime('2024-01-01 00:00:00'), 1000) AS add_nanoseconds_with_date_time,
	addNanoseconds('2024-01-01 00:00:00', 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         |
In this example, we add 1000 nanoseconds to the given date and time. The function works with both DateTime objects and string representations of dates. Arguments:
  • interval_1 (Interval or Tuple of Interval): First interval or tuple of intervals.
  • interval_2 (Interval): Second interval to be added.
Returns:
  • A tuple of intervals. Tuple of Interval.
Intervals of the same type will be combined into a single interval. For instance, if toIntervalDay(1) and toIntervalDay(2) are passed, the result will be (3) rather than (1,1).
Example: 
SELECT addInterval(INTERVAL 1 DAY, INTERVAL 1 MONTH) AS result;
Result: 
| result |
|--------|
| (1,1)  |
This example adds an interval of 1 day to an interval of 1 month. 
SELECT
	addInterval((INTERVAL 1 DAY, INTERVAL 1 YEAR), INTERVAL 1 MONTH) AS result;
Result: 
| result   |
|----------|
| (1,1,1)  |
This example adds an interval of 1 month to a tuple of intervals (1 day and 1 year). 
SELECT addInterval(INTERVAL 2 DAY, INTERVAL 1 DAY) AS result;
Result: 
| result |
|--------|
| (3)    |
This example demonstrates how intervals of the same type (days) are combined into a single interval. In these examples, the function combines intervals of different types (days, months, years) into a tuple, while intervals of the same type are summed up.

​
addTupleOfIntervals

Consecutively adds a tuple of intervals to a Date or a DateTime. Syntax: 
addTupleOfIntervals(date, intervals)
Arguments:
  • date (Date, Date32, DateTime, or DateTime64): Date or date with time.
  • intervals (Tuple(Interval)): Tuple of intervals to add to date.
Returns:
  • Date with added intervals. Same type as the input date.
Example: 
SELECT
	addTupleOfIntervals(
    toDate('2023-05-01'),
    (INTERVAL 1 DAY, INTERVAL 1 MONTH, INTERVAL 1 YEAR)
  ) AS taco_time;
Result: 
| taco_time  |
|------------|
| 2024-06-02 |
In this example, we start with Cinco de Mayo Eve (May 1, 2023) and add 1 day, 1 month, and 1 year to get the ultimate taco celebration date in the future.
The intervals are applied in the order they appear in the tuple. The result may differ if you change the order of the intervals.

​
subtractYears

Subtracts a specified number of years from a date or date with time. Syntax: 
subtractYears(date, num)
Arguments:
  • date (Date, Date32, DateTime, DateTime64, or String): Date or date with time to subtract years from.
  • num (numeric): Number of years to subtract.
Returns:
  • Date or date with time with the specified number of years subtracted. Same type as the input date.
Example: 
SELECT
	subtractYears(toDate('2024-01-01'), 1) AS subtract_years_with_date,
	subtractYears(toDateTime('2024-01-01 00:00:00'), 1) AS subtract_years_with_date_time,
	subtractYears('2024-01-01 00:00:00', 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              |
In this taco-themed example, we subtract one year from various dates related to a taco shop’s opening: 
SELECT
	subtractYears(toDate('2024-05-05'), 1) AS original_cinco_de_mayo_opening,
	subtractYears(toDateTime('2024-05-05 12:00:00'), 1) AS grand_opening_time,
	subtractYears('2024-05-05 18:30:00', 1) AS first_taco_tuesday;
Result: 
| original_cinco_de_mayo_opening | grand_opening_time    | first_taco_tuesday    |
|--------------------------------|-----------------------|-----------------------|
| 2023-05-05                     | 2023-05-05 12:00:00   | 2023-05-05 18:30:00   |
This example shows how the subtractYears function can be used to calculate dates for planning events or analyzing historical data for a taco business.

​
subtractQuarters

Subtracts a specified number of quarters from a date, a date with time, or a string-encoded date/time value. Syntax 
subtractQuarters(date, num)
Arguments
  • date (Date, Date32, DateTime, DateTime64, or String): Date or date with time to subtract quarters from.
  • num (numeric): Number of quarters to subtract.
Returns
  • Date minus the specified number of quarters. Returns the same type as the input date.
Example 
SELECT
	subtractQuarters(toDate('2024-01-01'), 1) AS subtract_quarters_with_date,
	subtractQuarters(toDateTime('2024-01-01 00:00:00'), 1) AS subtract_quarters_with_date_time,
	subtractQuarters('2024-01-01 00:00:00', 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                 |
This function subtracts the specified number of quarters from the given date or datetime. It handles various input formats and adjusts for different calendar quirks, such as varying month lengths and leap years. Arguments:
  • date (Date, Date32, DateTime, DateTime64, or String): Date or date with time to subtract months from.
  • num (numeric): Number of months to subtract.
Returns:
  • Date or date with time with the specified number of months subtracted. Returns the same type as the input date.
Example: 
SELECT
	subtractMonths(toDate('2024-01-01'), 1) AS subtract_months_with_date,
	subtractMonths(toDateTime('2024-01-01 00:00:00'), 1) AS subtract_months_with_date_time,
	subtractMonths('2024-01-01 00:00:00', 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               |
In this example, we subtract 1 month from January 1, 2024, using different input types. The function returns December 1, 2023, in each case, preserving the input type.

​
subtractWeeks

Subtracts a specified number of weeks from a date, date with time, or string-encoded date/time value. Syntax: 
subtractWeeks(date, num)
Arguments:
  • date (Date, Date32, DateTime, DateTime64, or String): Date or date with time to subtract weeks from.
  • num (numeric): Number of weeks to subtract.
Returns:
  • Date minus the specified number of weeks. Type matches the input date type.
Example: 
SELECT
	subtractWeeks(toDate('2024-01-01'), 1) AS subtract_weeks_with_date,
	subtractWeeks(toDateTime('2024-01-01 00:00:00'), 1) AS subtract_weeks_with_date_time,
	subtractWeeks('2024-01-01 00:00:00', 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              |
This function subtracts the specified number of weeks from the given date or date with time. It handles various input formats and returns a result of the same type as the input date.

​
subtractDays

Subtracts a specified number of days from a date, date with time, or string-encoded date/time value. Syntax: 
subtractDays(date, num)
Arguments:
  • date (Date, Date32, DateTime, DateTime64, or String): Date or date with time to subtract days from.
  • num (numeric): Number of days to subtract.
Returns:
  • Date or date with time with the specified number of days subtracted. Same type as the input date.
Example: 
SELECT
	subtractDays(toDate('2024-01-01'), 31) AS subtract_days_with_date,
	subtractDays(toDateTime('2024-01-01 00:00:00'), 31) AS subtract_days_with_date_time,
	subtractDays('2024-01-01 00:00:00', 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             |
In this example, we subtract 31 days from January 1, 2024, using different input types. The function returns December 1, 2023, in each case, preserving the input type.

​
subtractHours

Subtracts a specified number of hours from a date, date with time, or string-encoded date/time value. Syntax: 
subtractHours(date, num)
Arguments:
  • date (Date, Date32, DateTime, DateTime64, or String): Date or date with time to subtract hours from.
  • num (numeric): Number of hours to subtract.
Returns:
  • Date minus num hours. Type matches input date type. If input is Date, returns DateTime. If input is Date32, returns DateTime64.
Example: 
SELECT
	subtractHours(toDateTime('2024-01-01 12:00:00'), 3) AS subtract_hours_with_datetime,
	subtractHours('2024-01-01 12:00:00', 3) AS subtract_hours_with_string;
Result: 
| subtract_hours_with_datetime | subtract_hours_with_string |
|------------------------------|----------------------------|
| 2024-01-01 09:00:00          | 2024-01-01 09:00:00.000    |
In this example, we subtract 3 hours from the given datetime. The function works with both DateTime and string inputs, automatically parsing the string into a datetime value. Syntax: 
subtractMinutes(date, num)
Arguments:
  • date (Date, Date32, DateTime, DateTime64, or String): Date or date with time to subtract minutes from.
  • num (numeric): Number of minutes to subtract.
Returns:
  • Date or date with time with the specified number of minutes subtracted. The return type matches the input type of date.
Example: 
SELECT
	subtractMinutes(toDateTime('2024-01-01 00:00:00'), 30) AS subtract_minutes_with_datetime,
	subtractMinutes('2024-01-01 00:00:00', 30) AS subtract_minutes_with_string;
Result: 
| subtract_minutes_with_datetime | subtract_minutes_with_string |
|--------------------------------|------------------------------|
| 2023-12-31 23:30:00            | 2023-12-31 23:30:00.000      |
In this example:
  • subtract_minutes_with_datetime subtracts 30 minutes from the DateTime ‘2024-01-01 00:00:00’.
  • subtract_minutes_with_string subtracts 30 minutes from the string-encoded date ‘2024-01-01 00:00:00’.
Note that when using a string input, the function automatically converts it to a DateTime64 value before performing the subtraction.

​
subtractSeconds

Subtracts a specified number of seconds from a date, date with time, or string-encoded date/time value. Syntax: 
subtractSeconds(date, num)
Arguments:
  • date (Date, Date32, DateTime, DateTime64, or String) — Date or date with time to subtract seconds from.
  • num (numeric) — Number of seconds to subtract.
Returns:
  • Date or date with time with num seconds subtracted. Type matches input date type.
Example: 
SELECT
	subtractSeconds(toDate('2024-01-01'), 60) AS subtract_seconds_with_date,
	subtractSeconds(toDateTime('2024-01-01 00:00:00'), 60) AS subtract_seconds_with_date_time,
	subtractSeconds('2024-01-01 00:00:00', 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                |
This example demonstrates subtracting 60 seconds from different date and time formats, including a taco-themed scenario where you might use it to calculate order preparation times: 
SELECT
	order_id,
	order_time,
	subtractSeconds(order_time, 180) AS prep_start_time
FROM
	taco_orders
LIMIT 5;
Result: 
| order_id | order_time           | prep_start_time      |
|----------|----------------------|----------------------|
| 101      | 2024-03-05 12:00:00  | 2024-03-05 11:57:00  |
| 102      | 2024-03-05 12:05:30  | 2024-03-05 12:02:30  |
| 103      | 2024-03-05 12:10:15  | 2024-03-05 12:07:15  |
| 104      | 2024-03-05 12:15:45  | 2024-03-05 12:12:45  |
| 105      | 2024-03-05 12:20:00  | 2024-03-05 12:17:00  |
In this example, we calculate when taco preparation should start (3 minutes before the order time) for each order.

​
subtractMilliseconds

Subtracts a specified number of milliseconds from a date with time or a string-encoded date with time. Syntax: 
subtractMilliseconds(date_time, num)
Arguments:
  • date_time (DateTime or DateTime64, String): Date with time to subtract specified number of milliseconds from.
  • num (numeric): Number of milliseconds to subtract.
Returns:
  • Returns date_time minus num milliseconds. DateTime64.
Example: 
SELECT
	subtractMilliseconds(toDateTime('2024-01-01 00:00:00'), 1000) AS subtract_milliseconds_with_date_time,
	subtractMilliseconds('2024-01-01 00:00:00', 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                     |
In this example, we subtract 1000 milliseconds (1 second) from the start of 2024, resulting in the last second of 2023 for both DateTime and string inputs.

​
subtractMicroseconds

Subtracts a specified number of microseconds from a date with time or a string-encoded date with time. Syntax: 
subtractMicroseconds(date_time, num)
Arguments:
  • date_time (DateTime, DateTime64, String): Date with time to subtract specified number of microseconds from.
  • num (Integer, Float): Number of microseconds to subtract.
Returns:
  • Returns date_time minus num microseconds. (DateTime64).
Example: 
SELECT
	subtractMicroseconds(toDateTime('2024-01-01 00:00:00'), 1000000) AS subtract_microseconds_with_date_time,
	subtractMicroseconds('2024-01-01 00:00:00', 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                  |
In this example, we subtract 1,000,000 microseconds (1 second) from the given datetime. The function works with both DateTime objects and string representations of dates. Arguments:
  • date_time (DateTime or DateTime64 or String): Date with time to subtract specified number of nanoseconds from.
  • num (numeric): Number of nanoseconds to subtract.
Returns:
  • Returns date_time minus num nanoseconds. (DateTime64).
Example: 
SELECT
	subtractNanoseconds(toDateTime('2024-01-01 00:00:00'), 1000) AS subtract_nanoseconds_with_date_time,
	subtractNanoseconds('2024-01-01 00:00:00', 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              |
In this example, we subtract 1000 nanoseconds from the start of 2024, resulting in a time just before midnight on the last day of 2023. The function works with both DateTime and string inputs, returning a DateTime64 value with nanosecond precision.

​
subtractInterval

Subtracts a time interval from a date, date with time, or string-encoded date/time value. Syntax: 
subtractInterval(date, interval)
Arguments:
  • date (Date, Date32, DateTime, DateTime64, or String): Date or date with time to subtract the interval from.
  • interval (Interval): Interval to subtract.
Returns:
  • Date or date with time with the interval subtracted. (Date, Date32, DateTime, or DateTime64).
Example: 
SELECT
	subtractInterval(toDate('2024-01-01'), INTERVAL 3 MONTH) AS three_months_ago;
Result: 
| three_months_ago |
|------------------|
| 2023-10-01       |
This example subtracts 3 months from January 1, 2024, resulting in October 1, 2023.
If the subtraction results in a value outside the bounds of the data type, the result is undefined.
See Also:

​
subtractTupleOfIntervals

Consecutively subtracts a tuple of intervals from a Date or a DateTime. Syntax: 
subtractTupleOfIntervals(date, intervals)
Arguments:
  • date (Date, Date32, DateTime, or DateTime64): Date or date with time.
  • intervals (Tuple(Interval)): Tuple of intervals to subtract from date.
Returns:
  • Date with subtracted intervals. The return type matches the type of date.
Example: 
SELECT
	subtractTupleOfIntervals(toDate('2024-01-01'), (INTERVAL 1 DAY, INTERVAL 1 YEAR)) AS result;
Result: 
| result     |
|------------|
| 2022-12-31 |
In this example, we start with January 1, 2024, subtract 1 day and then 1 year, resulting in December 31, 2022. The function applies the intervals in the order they appear in the tuple. This function is particularly useful when you need to perform multiple interval subtractions in a single operation, such as adjusting dates for complex time-based calculations or reporting.

​
timeSlots

Returns an array of time slots for a given time interval. Syntax: 
timeSlots(startTime, duration[, size])
Arguments:
  • startTime (DateTime or DateTime64): The start time of the interval.
  • duration (UInt32 for DateTime, Decimal64 for DateTime64): The duration of the interval in seconds.
  • size (UInt32, optional, default: 1800): The size of each time slot in seconds.
Returns: An array of DateTime or DateTime64 values representing the time slots. Example: 
SELECT
	timeSlots(
		toDateTime('2023-05-05 12:00:00'),
		3600,
		900
	) AS taco_delivery_slots;
Result: 
| taco_delivery_slots                                                   |
|-----------------------------------------------------------------------|
| ['2023-05-05 12:00:00','2023-05-05 12:15:00',                         |
|  '2023-05-05 12:30:00','2023-05-05 12:45:00']                         |
In this example, we generate 15-minute time slots for a one-hour taco delivery window starting at noon.
The function returns time slots rounded down to the specified size. It’s useful for analyzing events or creating time-based schedules, like delivery windows or appointment slots.

​
formatDateTime

Formats a date or date with time according to the given format string. Syntax 
formatDateTime(date, format[, timezone])
Arguments
  • date (Date, DateTime, or DateTime64): Date or date with time.
  • format (String): Format string.
  • timezone (String, optional): Timezone for the returned value.
Returns
  • Formatted date/time string. (String)
Format Specifiers The format string may contain the following specifiers:
SpecifierDescriptionExample
%YYear (4 digits)2023
%mMonth (01-12)05
%dDay of the month (01-31)15
%HHour in 24h format (00-23)13
%MMinute (00-59)30
%SSecond (00-59)45
%fMicrosecond (000000-999999)123456
%wWeekday as number (0-6, 0 is Sunday)2
%bAbbreviated month nameMay
%BFull month nameMay
%aAbbreviated weekday nameTue
%AFull weekday nameTuesday
For a complete list of format specifiers, refer to the MySQL DATE_FORMAT function documentation. Example Query: 
SELECT
	formatDateTime(toDateTime('2023-05-15 13:30:45'), '%Y-%m-%d %H:%M:%S') AS formatted_date,
	formatDateTime(toDateTime('2023-05-15 13:30:45'), '%A, %b %d, %Y') AS formatted_date_text;
Result: 
| formatted_date      | formatted_date_text    |
|---------------------|------------------------|
| 2023-05-15 13:30:45 | Monday, May 15, 2023   |
This function is useful for converting dates and times into custom string formats for display or further processing. The optional timezone parameter allows for easy conversion between different time zones. See Also

​
formatDateTimeInJodaSyntax

Formats a date and time value according to the specified Joda-style format string. Syntax: 
formatDateTimeInJodaSyntax(date_or_datetime, format[, timezone])
Arguments:
  • date_or_datetime (Date, DateTime, or DateTime64): Date or date with time to format.
  • format (String): Format string in Joda syntax.
  • timezone (String, optional): Timezone for the returned value.
Returns:
  • A formatted string representation of the date and time. (String).
Example: 
SELECT
	formatDateTimeInJodaSyntax(
		toDateTime('2023-05-05 13:30:45'),
		'yyyy-MM-dd HH:mm:ss'
	) AS formatted_date;
Result: 
| formatted_date      |
|---------------------|
| 2023-05-05 13:30:45 |
This function is similar to formatDateTime, but uses Joda-style format patterns instead of MySQL-style patterns. For a full list of supported format patterns, refer to the Joda-Time documentation. Taco-themed Example: 
SELECT
	formatDateTimeInJodaSyntax(
		toDateTime('2023-05-05 13:30:45'),
		'EEEE, MMMM d, yyyy "at" h:mm a "is Taco Time!"'
	) AS taco_time;
Result: 
| taco_time                                        |
|--------------------------------------------------|
| Friday, May 5, 2023 at 1:30 PM is Taco Time!     |
This example demonstrates formatting a date and time into a fun, taco-themed message using various Joda-style patterns.

​
dateName

Returns the specified part of a date as a string. Syntax: 
dateName(date_part, date[, timezone])
Arguments:
  • date_part (String) — The part of the date to return. Possible values: ‘year’, ‘quarter’, ‘month’, ‘week’, ‘dayofyear’, ‘day’, ‘weekday’, ‘hour’, ‘minute’, ‘second’.
  • date (Date, Date32, DateTime, or DateTime64) — The date to extract the part from.
  • timezone (String, optional) — Optional timezone for the returned value.
Returns: The specified part of the date as a string. (String). Example: 
SELECT
	dateName('year', toDateTime('2023-05-05 12:30:45')) AS year,
	dateName('month', toDateTime('2023-05-05 12:30:45')) AS month,
	dateName('day', toDateTime('2023-05-05 12:30:45')) AS day;
Result: 
| year | month | day |
|------|-------|-----|
| 2023 | May   | 5   |
In this example:
  • year returns the year as a string.
  • month returns the full name of the month.
  • day returns the day of the month as a string.
This function is particularly useful when you need to extract specific parts of a date as readable text, such as for reporting or user-friendly displays in taco-themed applications.

​
monthName

Returns the name of the month for a given date or date with time. Syntax: 
monthName(date)
Arguments:
  • date (Date, DateTime, or DateTime64): Date or date with time.
Returns:
  • The name of the month. String
Example: 
SELECT
	monthName(toDateTime('2024-05-15 12:30:45')) AS taco_month;
Result: 
| taco_month |
|------------|
| May        |
In this example, we get the name of the month for a taco-themed Cinco de Mayo celebration planning date.

​
fromUnixTimestamp

Converts a Unix timestamp to a date and time. Syntax: 
fromUnixTimestamp(unix_timestamp)
fromUnixTimestamp(unix_timestamp, format[, timezone])
Alias:
  • FROM_UNIXTIME()
Arguments:
  • unix_timestamp (Integer): Unix timestamp.
  • format (String, optional): Format string for the returned value.
  • timezone (String, optional): Timezone for the returned value.
Returns:
  • When called with a single argument, returns a DateTime value.
  • When called with two or three arguments, returns a String formatted according to the specified format.
Example: 
SELECT
	fromUnixTimestamp(1683849600) AS datetime,
	fromUnixTimestamp(1683849600, '%Y-%m-%d %H:%M:%S', 'America/Los_Angeles') AS formatted_string;
Result: 
| datetime            | formatted_string    |
|---------------------|---------------------|
| 2023-05-11 20:00:00 | 2023-05-11 13:00:00 |
In this example:
  • The first column converts the Unix timestamp to a DateTime.
  • The second column formats the timestamp as a string in Los Angeles time zone.
When using the two or three argument form, the function uses MySQL’s datetime format style. For Joda-style formatting, use fromUnixTimestampInJodaSyntax(). This function is similar to toDateTime() when called with a single argument.
The single-argument form is an alias for FROM_UNIXTIME().

​
fromUnixTimestampInJodaSyntax

Converts a Unix timestamp to a formatted date and time string using Joda-Time syntax. Syntax: 
fromUnixTimestampInJodaSyntax(unix_timestamp, format[, timezone])
Arguments:
  • unix_timestamp (Integer, Date, Date32, DateTime, or DateTime64): Unix timestamp to convert.
  • format (String): Format string in Joda-Time syntax.
  • timezone (String, optional): Optional timezone for the returned value.
Returns:
  • Formatted date and time string. (String).
Example: 
SELECT
	fromUnixTimestampInJodaSyntax(1684789200, 'yyyy-MM-dd HH:mm:ss', 'America/Los_Angeles') AS taco_time;
Result: 
| taco_time           |
|---------------------|
| 2023-05-22 12:00:00 |
This example converts the Unix timestamp for a taco shop’s opening time to a formatted date and time string in the Los Angeles timezone.
The function uses Joda-Time syntax for formatting, which differs from MySQL’s datetime format style. For MySQL-style formatting, use the fromUnixTimestamp function instead.
See also:

​
toModifiedJulianDay

Converts a Proleptic Gregorian calendar date in text form YYYY-MM-DD to a Modified Julian Day number. Syntax 
toModifiedJulianDay(date)
Arguments
  • date (String or FixedString): Date in text form.
Returns
  • Modified Julian Day number. Type: Int32.
This function supports dates from 0000-01-01 to 9999-12-31. It raises an exception if the argument cannot be parsed as a date, or the date is invalid.
Example 
SELECT
	toModifiedJulianDay('2023-09-08') AS taco_day;
Result: 
| taco_day |
|----------|
| 60195    |
In this example, we calculate the Modified Julian Day for International Taco Day (September 8, 2023).

​
toModifiedJulianDayOrNull

Converts a date string to a Modified Julian Day number, returning NULL if the conversion fails. Syntax 
toModifiedJulianDayOrNull(date)
Arguments
  • date (String or FixedString): Date string in ‘YYYY-MM-DD’ format.
Returns
  • The Modified Julian Day number, or NULL if the input is invalid. (Nullable(Int32))
Example 
SELECT
	toModifiedJulianDayOrNull('2023-09-15') AS valid_date,
	toModifiedJulianDayOrNull('not-a-date') AS invalid_date;
Result: 
| valid_date | invalid_date |
|------------|--------------|
| 60107      | NULL         |
This function is useful when you need to convert dates to Modified Julian Day numbers in a query, but want to handle invalid dates gracefully without causing errors. It’s particularly handy when dealing with potentially dirty or user-input data.
The function supports dates from 0000-01-01 to 9999-12-31. Any date outside this range or in an invalid format will return NULL.

​
fromModifiedJulianDay

Converts a Modified Julian Day number to a date in the Proleptic Gregorian calendar. Syntax: 
fromModifiedJulianDay(day)
Arguments:
  • day (Integer): Modified Julian Day number.
Returns:
  • A date in ‘YYYY-MM-DD’ format. String.
This function supports day numbers from -678941 to 2973483 (corresponding to dates from 0000-01-01 to 9999-12-31). It throws an exception if the day number is outside this range. Example: 
SELECT fromModifiedJulianDay(58849) AS taco_day;
Result: 
| taco_day   |
|------------|
| 2020-01-01 |
In this example, we convert the Modified Julian Day number 58849 to its corresponding Gregorian date, which happens to be January 1, 2020 - perhaps the day a new taco recipe was invented!
The Modified Julian Day (MJD) is a standard count of days, where MJD 0 corresponds to November 17, 1858. It’s commonly used in astronomy and space science.

​
fromModifiedJulianDayOrNull

Converts a Modified Julian Day number to a date in the Proleptic Gregorian calendar, returning NULL instead of raising an exception for invalid inputs. Syntax 
fromModifiedJulianDayOrNull(day)
Arguments
  • day (Integer): Modified Julian Day number.
Returns
  • A date in the format ‘YYYY-MM-DD’. Nullable(String).
  • Returns NULL if the day number is outside the supported range (-678941 to 2973483).
Example 
SELECT
	fromModifiedJulianDayOrNull(58849) AS valid_date,
	fromModifiedJulianDayOrNull(5884900) AS invalid_date;
Result: 
| valid_date | invalid_date |
|------------|--------------|
| 2020-01-01 | NULL         |
This function is useful when you need to handle potentially invalid Modified Julian Day numbers without causing query errors. It converts valid day numbers to dates and returns NULL for any values outside the supported range.

​
toUTCTimestamp

Converts a DateTime or DateTime64 value from a specified time zone to UTC timezone. Syntax 
toUTCTimestamp(time_val, time_zone)
Arguments
  • time_val (DateTime or DateTime64): A DateTime or DateTime64 value to convert.
  • time_zone (String): The source time zone of time_val.
Returns
  • The input time converted to UTC. [DateTime] or [DateTime64]
Example 
SELECT
	toUTCTimestamp(toDateTime('2023-03-16 12:00:00'), 'America/Los_Angeles') AS utc_time;
Result: 
| utc_time            |
|---------------------|
| 2023-03-16 19:00:00 |
In this example, noon in Los Angeles is converted to 7 PM UTC.
This function is useful for normalizing timestamps from different time zones to a common UTC reference. It’s particularly handy when dealing with data from multiple geographic locations or when preparing data for timezone-agnostic analysis.

​
fromUTCTimestamp

Converts a DateTime or DateTime64 value from UTC timezone to another specified timezone. Syntax 
fromUTCTimestamp(time_val, time_zone)
Arguments
  • time_val (DateTime or DateTime64): A DateTime or DateTime64 value in UTC timezone.
  • time_zone (String): Target timezone name (e.g., ‘America/New_York’, ‘Europe/London’).
Returns
  • DateTime or DateTime64 value adjusted to the specified timezone.
Example 
SELECT
	fromUTCTimestamp(toDateTime64('2023-03-16 10:00:00', 3), 'America/Los_Angeles') AS la_time;
Result: 
| la_time                 |
|-------------------------|
| 2023-03-16 03:00:00.000 |
In this example, we convert 10:00 AM UTC to Los Angeles time, which is 3:00 AM due to the -7 hour offset.
This function is useful for converting UTC timestamps to local timezones, which is common when working with data from different geographical regions or standardizing timestamps for display purposes.

​
UTCTimestamp

Returns the current date and time at the moment of query analysis in the UTC timezone. The function is a constant expression. Syntax: 
UTCTimestamp()
Alias:
  • UTC_timestamp
Returns:
  • The current date and time at the moment of query analysis. DateTime.
Example: 
SELECT UTCTimestamp() AS current_utc_time;
Result: 
| current_utc_time     |
|----------------------|
| 2024-05-28 08:32:09  |
This function gives the same result as now('UTC'). It was added for MySQL compatibility, but now('UTC') is the preferred usage in ClickHouse.
The returned timestamp is fixed at the moment of query analysis, not execution. This means that for long-running queries, the timestamp will not change during query execution.

​
timeDiff

Calculates the difference between two date or datetime values in seconds. Syntax: 
timeDiff(first_datetime, second_datetime)
Arguments:
  • first_datetime (DateTime or DateTime64): The first date/time value.
  • second_datetime (DateTime or DateTime64): The second date/time value.
Returns:
  • The difference between the two dates/times in seconds. (Int64)
Example: 
SELECT
	timeDiff(
		toDateTime64('2023-05-05 12:30:00', 3),
		toDateTime64('2023-05-05 12:00:00', 3)
	) AS time_difference;
Result: 
| time_difference |
|-----------------|
| 1800            |
In this example, timeDiff calculates the number of seconds between two taco order times, showing that 30 minutes (1800 seconds) elapsed between orders.
This function is equivalent to dateDiff('second', first_datetime, second_datetime) and was added for MySQL compatibility. Using dateDiff is generally preferred in ClickHouse.