Functions for working with dates and times

**Syntax**

```
timezoneOf(datetime)
```

**Aliases**: `timeZoneOf`

**Arguments**

- `datetime` — A value of type. [`DateTime`](/docs/sql-reference/data-types/datetime) or [`DateTime64`](/docs/sql-reference/data-types/datetime64)
- `timezone` — Optional. Timezone name to convert the `datetime` value's timezone to. [`String`](/docs/sql-reference/data-types/string)

**Returned value**

Returns the timezone name for `datetime` [`String`](/docs/sql-reference/data-types/string)

**Examples**

**Usage example**

```
SELECT timezoneOf(now());
```

```
┌─timezoneOf(now())─┐
│ Europe/Amsterdam  │
└───────────────────┘
```

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

Introduced in: v21.6.0

Returns the timezone offset in seconds from [UTC](https://en.wikipedia.org/wiki/Coordinated_Universal_Time).
The function takes daylight saving time and historical timezone changes at the specified date and time into account.

**Syntax**

```
timezoneOffset(datetime)
```

**Aliases**: `timeZoneOffset`

**Arguments**

- `datetime` — `DateTime` value to get the timezone offset for. [`DateTime`](/docs/sql-reference/data-types/datetime) or [`DateTime64`](/docs/sql-reference/data-types/datetime64)

**Returned value**

Returns the offset from UTC in seconds [`Int32`](/docs/sql-reference/data-types/int-uint)

**Examples**

**Usage example**

```
SELECT toDateTime('2021-04-21 10:20:30', 'America/New_York') AS Time,
toTypeName(Time) AS Type,
timezoneOffset(Time) AS Offset_in_seconds,
(Offset_in_seconds / 3600) AS Offset_in_hours;
```

```
┌────────────────Time─┬─Type─────────────────────────┬─Offset_in_seconds─┬─Offset_in_hours─┐
│ 2021-04-21 10:20:30 │ DateTime('America/New_York') │            -14400 │              -4 │
└─────────────────────┴──────────────────────────────┴───────────────────┴─────────────────┘
```

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

Introduced in: v1.1.0

Returns the day of the month (1-31) of a `Date` or `DateTime`.

**Syntax**

```
toDayOfMonth(datetime)
```

**Aliases**: `DAY`, `DAYOFMONTH`

**Arguments**

- `datetime` — Date or date with time to get the day of month from. [`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 day of the month of the given date/time [`UInt8`](/docs/sql-reference/data-types/int-uint)

**Examples**

**Usage example**

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

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

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

Introduced in: v1.1.0

Returns the number of the day within the week of a `Date` or `DateTime` value.

The two-argument form of `toDayOfWeek()` enables you to specify whether the week starts on Monday or Sunday,
and whether the return value should be in the range from 0 to 6 or 1 to 7.

|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- |
| Mode First day of week Range|  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | 0 Monday 1-7: Monday = 1, Tuesday = 2, ..., Sunday = 7|  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | | 1 Monday 0-6: Monday = 0, Tuesday = 1, ..., Sunday = 6|  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | | 2 Sunday 0-6: Sunday = 0, Monday = 1, ..., Saturday = 6|  |  |  | | --- | --- | --- | | 3 Sunday 1-7: Sunday = 1, Monday = 2, ..., Saturday = 7 | | | | | | | | | | | | | | |

**Syntax**

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

**Aliases**: `DAYOFWEEK`

**Arguments**

- `datetime` — Date or date with time to get the day of week from. [`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)
- `mode` — Optional. Integer specifying the week mode (0-3). Defaults to 0 if omitted. [`UInt8`](/docs/sql-reference/data-types/int-uint)
- `timezone` — Optional. Timezone to use for the conversion. [`String`](/docs/sql-reference/data-types/string)

**Returned value**

Returns the day of the week for the given `Date` or `DateTime` [`UInt8`](/docs/sql-reference/data-types/int-uint)

**Examples**

**Usage example**

```
-- The following date is April 21, 2023, which was a Friday:
SELECT
    toDayOfWeek(toDateTime('2023-04-21')),
    toDayOfWeek(toDateTime('2023-04-21'), 1)
```

```
┌─toDayOfWeek(toDateTime('2023-04-21'))─┬─toDayOfWeek(toDateTime('2023-04-21'), 1)─┐
│                                     5 │                                        4 │
└───────────────────────────────────────┴──────────────────────────────────────────┘
```

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

Introduced in: v18.4.0

Returns the number of the day within the year (1-366) of a `Date` or `DateTime` value.

**Syntax**

```
toDayOfYear(datetime)
```

**Aliases**: `DAYOFYEAR`

**Arguments**

- `datetime` — Date or date with time to get the day of year from. [`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 day of the year of the given Date or DateTime [`UInt16`](/docs/sql-reference/data-types/int-uint)

**Examples**

**Usage example**

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

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

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

Introduced in: v26.3.0

Returns the number of days in the month of a `Date` or `DateTime`.

The returned value is in the range 28 to 31.

**Syntax**

```
toDaysInMonth(datetime)
```

**Arguments**

- `datetime` — Date or date with time to get the number of days in the month from. [`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 number of days in the month of the given date/time. [`UInt8`](/docs/sql-reference/data-types/int-uint)

**Examples**

**Usage example**

```
SELECT toDaysInMonth(toDate('2023-02-01')), toDaysInMonth(toDate('2024-02-01')), toDaysInMonth(toDate('2023-01-01'))
```

```
┌─toDaysInMonth(toDate('2023-02-01'))─┬─toDaysInMonth(toDate('2024-02-01'))─┬─toDaysInMonth(toDate('2023-01-01'))─┐
│                                  28 │                                  29 │                                  31 │
└─────────────────────────────────────┴─────────────────────────────────────┴─────────────────────────────────────┘
```

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

Introduced in: v23.9.0

For a given date, returns the number of days which have passed since [1 January 0000](https://en.wikipedia.org/wiki/Year_zero) in the
[proleptic Gregorian calendar defined by ISO 8601](https://en.wikipedia.org/wiki/Gregorian_calendar#Proleptic_Gregorian_calendar).

The calculation is the same as in MySQL's [`TO_DAYS`](https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_to-days) function.

**Syntax**

```
toDaysSinceYearZero(date[, time_zone])
```

**Aliases**: `TO_DAYS`

**Arguments**