Date and Time functions

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.

​

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

​

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.

See Also:

• toDateTime
• timeZones

​

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.

​

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.

​

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

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

​

toHour

Returns the hour component (0-23) of a date with time.

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.

​

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.

​

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.

​

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.

​

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 |

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 |

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)

​

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.

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.

​

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.

​

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.

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:

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          |

See Also

• toWeek()
• toISOYear()

​

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.

See Also

• fromDaysSinceYearZero — The inverse operation, converting days to a date.

​

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

• toDaysSinceYearZero

​

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

• toDaysSinceYearZero

​

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)

​

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.

​

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.

​

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.

See Also:

• addDate

​

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.

See Also:

• subDate
• date_add

​

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

​

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.

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.

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:

• date_sub

​

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.

​

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.

​

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.

​

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

​

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.

​

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.

​

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.

​

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!

​

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.

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.

​

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.

​

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.

​

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.

​

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.

​

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.

​

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.

​

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.

​

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.

​

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.

​

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.

​

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