Functions for working with dates and times

Converts a date or date with time to the number of weeks elapsed since a certain fixed point in the past.
The exact point in time is an implementation detail, and therefore this function is not intended to be used standalone.
The main purpose of the function is to calculate the difference in weeks between two dates or dates with time, e.g., `toRelativeWeekNum(dt1) - toRelativeWeekNum(dt2)`.

**Syntax**

```
toRelativeWeekNum(date)
```

**Arguments**

- `date` — Date or date with time. [`Date`](/docs/sql-reference/data-types/date) or [`DateTime`](/docs/sql-reference/data-types/datetime) or [`DateTime64`](/docs/sql-reference/data-types/datetime64)

**Returned value**

Returns the number of weeks from a fixed reference point in the past. [`UInt32`](/docs/sql-reference/data-types/int-uint)

**Examples**

**Get relative week numbers**

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

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

## toRelativeYearNum[​](#toRelativeYearNum "Direct link to toRelativeYearNum")

Introduced in: v1.1.0

Converts a date or date with time to the number of years elapsed since a certain fixed point in the past.
The exact point in time is an implementation detail, and therefore this function is not intended to be used
standalone. The main purpose of the function is to calculate the difference in years between two dates or dates with time, e.g., `toRelativeYearNum(dt1) - toRelativeYearNum(dt2)`.

**Syntax**

```
toRelativeYearNum(date)
```

**Arguments**

- `date` — Date or date with time. [`Date`](/docs/sql-reference/data-types/date) or [`DateTime`](/docs/sql-reference/data-types/datetime) or [`DateTime64`](/docs/sql-reference/data-types/datetime64)

**Returned value**

Returns the number of years from a fixed reference point in the past. [`UInt16`](/docs/sql-reference/data-types/int-uint)

**Examples**

**Get relative year numbers**

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

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

## toSecond[​](#toSecond "Direct link to toSecond")

Introduced in: v1.1.0

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

**Syntax**

```
toSecond(datetime)
```

**Aliases**: `SECOND`

**Arguments**

- `datetime` — Date with time to get the second from. [`DateTime`](/docs/sql-reference/data-types/datetime) or [`DateTime64`](/docs/sql-reference/data-types/datetime64)

**Returned value**

Returns the second in the minute (0 - 59) of `datetime`. [`UInt8`](/docs/sql-reference/data-types/int-uint)

**Examples**

**Usage example**

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

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

## toStartOfDay[​](#toStartOfDay "Direct link to toStartOfDay")

Introduced in: v1.1.0

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

Note

The return type can be configured by setting [`enable_extended_results_for_datetime_functions`](/docs/operations/settings/settings#enable_extended_results_for_datetime_functions).

**Syntax**

```
toStartOfDay(datetime)
```

**Arguments**

- `datetime` — A date or date with time to round. [`Date`](/docs/sql-reference/data-types/date) or [`DateTime`](/docs/sql-reference/data-types/datetime)

**Returned value**

Returns the date with time rounded down to the start of the day. [`DateTime`](/docs/sql-reference/data-types/datetime) or [`DateTime64`](/docs/sql-reference/data-types/datetime64)

**Examples**

**Round down to the start of the day**

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

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

## toStartOfFifteenMinutes[​](#toStartOfFifteenMinutes "Direct link to toStartOfFifteenMinutes")

Introduced in: v1.1.0

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

Note

The return type can be configured by setting [`enable_extended_results_for_datetime_functions`](/docs/operations/settings/settings#enable_extended_results_for_datetime_functions).

**Syntax**

```
toStartOfFifteenMinutes(datetime)
```

**Arguments**

- `datetime` — A date or date with time to round. [`DateTime`](/docs/sql-reference/data-types/datetime) or [`DateTime64`](/docs/sql-reference/data-types/datetime64)

**Returned value**

Returns the date with time rounded to the start of the nearest fifteen-minute interval. [`DateTime`](/docs/sql-reference/data-types/datetime) or [`DateTime64`](/docs/sql-reference/data-types/datetime64)

**Examples**

**Example**

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

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

## toStartOfFiveMinutes[​](#toStartOfFiveMinutes "Direct link to toStartOfFiveMinutes")

Introduced in: v22.6.0

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

Note

The return type can be configured by setting [`enable_extended_results_for_datetime_functions`](/docs/operations/settings/settings#enable_extended_results_for_datetime_functions).

**Syntax**

```
toStartOfFiveMinutes(datetime)
```

**Aliases**: `toStartOfFiveMinute`

**Arguments**

- `datetime` — A date with time to round. [`DateTime`](/docs/sql-reference/data-types/datetime) or [`DateTime64`](/docs/sql-reference/data-types/datetime64)

**Returned value**

Returns the date with time rounded to the start of the nearest five-minute interval. [`DateTime`](/docs/sql-reference/data-types/datetime) or [`DateTime64`](/docs/sql-reference/data-types/datetime64)

**Examples**

**Example**

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

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

## toStartOfHour[​](#toStartOfHour "Direct link to toStartOfHour")

Introduced in: v1.1.0

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

Note

The return type can be configured by setting [`enable_extended_results_for_datetime_functions`](/docs/operations/settings/settings#enable_extended_results_for_datetime_functions).

**Syntax**

```
toStartOfHour(datetime)
```

**Arguments**

- `datetime` — A date with time to round. [`DateTime`](/docs/sql-reference/data-types/datetime) or [`DateTime64`](/docs/sql-reference/data-types/datetime64)

**Returned value**

Returns the date with time rounded down to the start of the hour. [`DateTime`](/docs/sql-reference/data-types/datetime) or [`DateTime64`](/docs/sql-reference/data-types/datetime64)

**Examples**

**Round down to the start of the hour**

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

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

## toStartOfISOYear[​](#toStartOfISOYear "Direct link to toStartOfISOYear")

Introduced in: v1.1.0

Rounds down a date or date with time to the first day of the ISO year, which can be different than a regular year. See [ISO week date](https://en.wikipedia.org/wiki/ISO_week_date).

Note

The return type can be configured by setting [`enable_extended_results_for_datetime_functions`](/docs/operations/settings/settings#enable_extended_results_for_datetime_functions).

**Syntax**