Functions for working with dates and times

**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**

```
toStartOfISOYear(value)
```

**Arguments**

- `value` — The date or date with time to round down to the first day of the ISO year. [`Date`](/docs/sql-reference/data-types/date) or [`Date32`](/docs/sql-reference/data-types/date32) or [`DateTime`](/docs/sql-reference/data-types/datetime) or [`DateTime64`](/docs/sql-reference/data-types/datetime64)

**Returned value**

Returns the first day of the ISO year for the given date or date with time. [`Date`](/docs/sql-reference/data-types/date)

**Examples**

**Round down to the first day of the ISO year**

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

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

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

Introduced in: v20.1.0

This function generalizes other `toStartOf*()` functions with `toStartOfInterval(date_or_date_with_time, INTERVAL x unit [, time_zone])` syntax.

For example,

- `toStartOfInterval(t, INTERVAL 1 YEAR)` returns the same as `toStartOfYear(t)`,
- `toStartOfInterval(t, INTERVAL 1 MONTH)` returns the same as `toStartOfMonth(t)`,
- `toStartOfInterval(t, INTERVAL 1 DAY)` returns the same as `toStartOfDay(t)`,
- `toStartOfInterval(t, INTERVAL 15 MINUTE)` returns the same as `toStartOfFifteenMinutes(t)`.

The calculation is performed relative to specific points in time:

|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- |
| Interval Start|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | YEAR year 0|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | QUARTER 1900 Q1|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | MONTH 1900 January|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | WEEK 1970, 1st week (01-05)|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | DAY 1970-01-01|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | HOUR (\*)|  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | MINUTE 1970-01-01 00:00:00|  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | SECOND 1970-01-01 00:00:00|  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | MILLISECOND 1970-01-01 00:00:00|  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | | MICROSECOND 1970-01-01 00:00:00|  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | | NANOSECOND 1970-01-01 00:00:00|  |  |  |  | | --- | --- | --- | --- | | (\*) hour intervals are special: the calculation is always performed relative to 00:00:00 (midnight) of the current day. As a result, only |  |  | | --- | --- | | hour values between 1 and 23 are useful.  | | | | | | | | | | | | | | | | | | | | | | | | | | | |

If unit `WEEK` was specified, `toStartOfInterval` assumes that weeks start on Monday. Note that this behavior is different from that of function `toStartOfWeek` in which weeks start by default on Sunday.

The second overload emulates TimescaleDB's `time_bucket()` function, respectively PostgreSQL's `date_bin()` function.

**Syntax**

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

**Aliases**: `time_bucket`, `date_bin`

**Arguments**

- `value` — Date or date with time value to round down. [`Date`](/docs/sql-reference/data-types/date) or [`DateTime`](/docs/sql-reference/data-types/datetime) or [`DateTime64`](/docs/sql-reference/data-types/datetime64)
- `x` — Interval length number. - `unit` — Interval unit: YEAR, QUARTER, MONTH, WEEK, DAY, HOUR, MINUTE, SECOND, MILLISECOND, MICROSECOND, NANOSECOND. - `time_zone` — Optional. Time zone name as a string. - `origin` — Optional. Origin point for calculation (second overload only).

**Returned value**

Returns the start of the interval containing the input value. [`DateTime`](/docs/sql-reference/data-types/datetime)

**Examples**

**Basic interval rounding**

```
SELECT toStartOfInterval(toDateTime('2023-01-15 14:30:00'), INTERVAL 1 MONTH)
```

```
┌─toStartOfInt⋯alMonth(1))─┐
│               2023-01-01 │
└──────────────────────────┘
```

**Using origin point**

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

```
┌─toStartOfInt⋯14:35:30'))─┐
│      2023-01-01 14:44:30 │
└──────────────────────────┘
```

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

Introduced in: v22.6.0

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

**Syntax**

```
toStartOfMicrosecond(datetime[, timezone])
```

**Arguments**

- `datetime` — Date and time. [`DateTime64`](/docs/sql-reference/data-types/datetime64)
- `timezone` — Optional. Timezone for the returned value. If not specified, the function uses the timezone of the `value` parameter. [`String`](/docs/sql-reference/data-types/string)

**Returned value**

Input value with sub-microseconds [`DateTime64`](/docs/sql-reference/data-types/datetime64)

**Examples**

**Query without timezone**

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

```
┌────toStartOfMicrosecond(dt64)─┐
│ 2020-01-01 10:20:30.999999000 │
└───────────────────────────────┘
```

**Query with timezone**

```
WITH toDateTime64('2020-01-01 10:20:30.999999999', 9) AS dt64
SELECT toStartOfMicrosecond(dt64, 'Asia/Istanbul');
```

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

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

Introduced in: v22.6.0

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

**Syntax**

```
toStartOfMillisecond(datetime[, timezone])
```

**Arguments**

- `datetime` — Date and time. [`DateTime64`](/docs/sql-reference/data-types/datetime64)
- `timezone` — Optional. Timezone for the returned value. If not specified, the function uses the timezone of the `value` parameter. [`String`](/docs/sql-reference/data-types/string)

**Returned value**

Input value with sub-milliseconds. [`DateTime64`](/docs/sql-reference/data-types/datetime64)

**Examples**