Functions for working with dates and times

```
┌─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**

**Query without timezone**

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

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

**Query with timezone**

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

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

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

Introduced in: v1.1.0

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

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

```
toStartOfMinute(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 minute. [`DateTime`](/docs/sql-reference/data-types/datetime) or [`DateTime64`](/docs/sql-reference/data-types/datetime64)

**Examples**

**Round down to the start of the minute**

```
SELECT
    toStartOfMinute(toDateTime('2023-04-21 10:20:30')),
    toStartOfMinute(toDateTime64('2023-04-21 10:20:30.5300', 8))
FORMAT Vertical
```

```
Row 1:
──────
toStartOfMinute(toDateTime('2023-04-21 10:20:30')):           2023-04-21 10:20:00
toStartOfMinute(toDateTime64('2023-04-21 10:20:30.5300', 8)): 2023-04-21 10:20:00
```

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

Introduced in: v1.1.0

Rounds down a date or date with time to the first day of the month.

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

```
toStartOfMonth(value)
```

**Arguments**

- `value` — The date or date with time to round down to the first day of the month. [`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 month for the given date or date with time. [`Date`](/docs/sql-reference/data-types/date)

**Examples**

**Round down to the first day of the month**

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

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

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

Introduced in: v22.6.0

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

**Syntax**

```
toStartOfNanosecond(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 nanoseconds. [`DateTime64`](/docs/sql-reference/data-types/datetime64)

**Examples**

**Query without timezone**

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

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

**Query with timezone**

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

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

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

Introduced in: v1.1.0

Rounds down a date or date with time to the first day of the quarter. The first day of the quarter is either 1 January, 1 April, 1 July, or 1 October.

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

```
toStartOfQuarter(value)
```

**Arguments**

- `value` — The date or date with time to round down to the first day of the quarter. [`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 quarter for the given date or date with time. [`Date`](/docs/sql-reference/data-types/date)

**Examples**

**Round down to the first day of the quarter**

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

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

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

Introduced in: v20.5.0

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

**Syntax**

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

**Arguments**

- `datetime` — Date and time to truncate sub-seconds from. [`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**

Returns the input value without sub-seconds. [`DateTime64`](/docs/sql-reference/data-types/datetime64)

**Examples**

**Query without timezone**

```
WITH toDateTime64('2020-01-01 10:20:30.999', 3) AS dt64
SELECT toStartOfSecond(dt64);
```

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

**Query with timezone**

```
WITH toDateTime64('2020-01-01 10:20:30.999', 3) AS dt64
SELECT toStartOfSecond(dt64, 'Asia/Istanbul');
```

```
┌─toStartOfSecond(dt64, 'Asia/Istanbul')─┐
│                2020-01-01 13:20:30.000 │
└────────────────────────────────────────┘
```

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

Introduced in: v20.1.0

Rounds down a date with time to the start of the nearest ten-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**

```
toStartOfTenMinutes(datetime)
```

**Arguments**

- `datetime` — A date with time. [`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 ten-minute interval. [`DateTime`](/docs/sql-reference/data-types/datetime) or [`DateTime64`](/docs/sql-reference/data-types/datetime64)

**Examples**

**Example**