Functions for working with dates and times

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

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

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

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

Introduced in: v20.1.0

Rounds a date or date with time down to the nearest Sunday or Monday.

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

```
toStartOfWeek(datetime[, mode[, timezone]])
```

**Arguments**

- `datetime` — A date or date with time to convert. [`Date`](/docs/sql-reference/data-types/date) or [`DateTime`](/docs/sql-reference/data-types/datetime) or [`Date32`](/docs/sql-reference/data-types/date32) or [`DateTime64`](/docs/sql-reference/data-types/datetime64)
- `mode` — Determines the first day of the week as described in the `toWeek()` function. Default `0`. [`UInt8`](/docs/sql-reference/data-types/int-uint)
- `timezone` — The timezone to use for the conversion. If not specified, the server's timezone is used. [`String`](/docs/sql-reference/data-types/string)

**Returned value**

Returns the date of the nearest Sunday or Monday on, or prior to, the given date, depending on the mode. [`Date`](/docs/sql-reference/data-types/date) or [`Date32`](/docs/sql-reference/data-types/date32)

**Examples**

**Round down to the nearest Sunday or Monday**

```
SELECT
        toStartOfWeek(toDateTime('2023-04-21 10:20:30')), /* a Friday */
        toStartOfWeek(toDateTime('2023-04-21 10:20:30'), 1), /* a Friday */
        toStartOfWeek(toDate('2023-04-24')), /* a Monday */
        toStartOfWeek(toDate('2023-04-24'), 1) /* a Monday */
    FORMAT Vertical
```

```
Row 1:
    ──────
    toStartOfWeek(toDateTime('2023-04-21 10:20:30')):      2023-04-17
    toStartOfWeek(toDateTime('2023-04-21 10:20:30'), 1):   2023-04-17
    toStartOfWeek(toDate('2023-04-24')):                   2023-04-24
    toStartOfWeek(toDate('2023-04-24'), 1):                2023-04-24
```

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

Introduced in: v1.1.0

Rounds down a date or date with time to the first day of the year. Returns the date as a `Date` object.

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

```
toStartOfYear(value)
```

**Arguments**

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

**Examples**

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

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

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

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

Introduced in: v1.1.0

Extracts the time component of a date or date with time.
The returned result is an offset to a fixed point in time, currently `1970-01-02`,
but the exact point in time is an implementation detail which may change in future.

`toTime` should therefore not be used standalone.
The main purpose of the function is to calculate the time difference between two dates or dates with time, e.g., `toTime(dt1) - toTime(dt2)`.

**Syntax**

```
toTimeWithFixedDate(date[, timezone])
```

**Arguments**

- `date` — Date to convert to a time. [`Date`](/docs/sql-reference/data-types/date) or [`DateTime`](/docs/sql-reference/data-types/datetime) or [`DateTime64`](/docs/sql-reference/data-types/datetime64)
- `timezone` — Optional. Timezone for the returned value. [`String`](/docs/sql-reference/data-types/string)

**Returned value**

Returns the time component of a date or date with time in the form of an offset to a fixed point in time (selected as 1970-01-02, currently). [`DateTime`](/docs/sql-reference/data-types/datetime)

**Examples**

**Calculate the time difference between two dates**

```
SELECT toTimeWithFixedDate('2025-06-15 12:00:00'::DateTime) - toTimeWithFixedDate('2024-05-10 11:00:00'::DateTime) AS result, toTypeName(result)
```

```
┌─result─┬─toTypeName(result)─┐
│   3600 │ Int32              │
└────────┴────────────────────┘
```

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

Introduced in: v1.1.0

Converts a `DateTime` or `DateTime64` to the specified time zone.
The internal value (number of unix seconds) of the data doesn't change.
Only the value's time zone attribute and the value's string representation changes.

**Syntax**

```
toTimezone(datetime, timezone)
```

**Aliases**: `toTimeZone`

**Arguments**

- `date` — The value to convert. [`DateTime`](/docs/sql-reference/data-types/datetime) or [`DateTime64`](/docs/sql-reference/data-types/datetime64)
- `timezone` — The target time zone name. [`String`](/docs/sql-reference/data-types/string)

**Returned value**

Returns the same timestamp as the input, but with the specified time zone [`DateTime`](/docs/sql-reference/data-types/datetime) or [`DateTime64`](/docs/sql-reference/data-types/datetime64)

**Examples**

**Usage 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, 'Asia/Yekaterinburg') AS time_yekat,
toTypeName(time_yekat) AS type_yekat,
toInt32(time_yekat) AS int32yekat,
toTimezone(time_utc, 'US/Samoa') AS time_samoa,
toTypeName(time_samoa) AS type_samoa,
toInt32(time_samoa) AS int32samoa
FORMAT Vertical;
```

```
Row 1:
──────
time_utc:   2019-01-01 00:00:00
type_utc:   DateTime('UTC')
int32utc:   1546300800
time_yekat: 2019-01-01 05:00:00
type_yekat: DateTime('Asia/Yekaterinburg')
int32yekat: 1546300800
time_samoa: 2018-12-31 13:00:00
type_samoa: DateTime('US/Samoa')
int32samoa: 1546300800
```

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

Introduced in: v23.8.0

Converts a date or date with time value from one time zone to UTC timezone timestamp. This function is mainly included for compatibility with Apache Spark and similar frameworks.

**Syntax**

```
toUTCTimestamp(datetime, time_zone)
```

**Aliases**: `to_utc_timestamp`

**Arguments**

- `datetime` — A date or date with time type const value or an expression. [`DateTime`](/docs/sql-reference/data-types/datetime) or [`DateTime64`](/docs/sql-reference/data-types/datetime64)
- `time_zone` — A String type const value or an expression representing the time zone. [`String`](/docs/sql-reference/data-types/string)

**Returned value**

Returns a date or date with time in UTC timezone. [`DateTime`](/docs/sql-reference/data-types/datetime) or [`DateTime64`](/docs/sql-reference/data-types/datetime64)

**Examples**