Functions for working with dates and times

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

**Convert timezone to UTC**

```
SELECT toUTCTimestamp(toDateTime('2023-03-16'), 'Asia/Shanghai')
```

```
┌─toUTCTimestamp(toDateTime('2023-03-16'), 'Asia/Shanghai')─┐
│                                     2023-03-15 16:00:00 │
└─────────────────────────────────────────────────────────┘
```

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

Introduced in: v1.1.0

Converts a `String`, `Date`, or `DateTime` to a Unix timestamp (seconds since `1970-01-01 00:00:00 UTC`) as `UInt32`.

**Syntax**

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

**Arguments**

- `date` — Value to convert. [`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) or [`String`](/docs/sql-reference/data-types/string)
- `timezone` — Optional. Timezone to use for conversion. If not specified, the server's timezone is used. [`String`](/docs/sql-reference/data-types/string)

**Returned value**

Returns the Unix timestamp. [`UInt32`](/docs/sql-reference/data-types/int-uint)

**Examples**

**Usage example**

```
SELECT
'2017-11-05 08:07:47' AS dt_str,
toUnixTimestamp(dt_str) AS from_str,
toUnixTimestamp(dt_str, 'Asia/Tokyo') AS from_str_tokyo,
toUnixTimestamp(toDateTime(dt_str)) AS from_datetime,
toUnixTimestamp(toDateTime64(dt_str, 0)) AS from_datetime64,
toUnixTimestamp(toDate(dt_str)) AS from_date,
toUnixTimestamp(toDate32(dt_str)) AS from_date32
FORMAT Vertical;
```

```
Row 1:
──────
dt_str:          2017-11-05 08:07:47
from_str:        1509869267
from_str_tokyo:  1509836867
from_datetime:   1509869267
from_datetime64: 1509869267
from_date:       1509840000
from_date32:     1509840000
```

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

Introduced in: v20.1.0

This function returns the week number for date or datetime. The two-argument form of `toWeek()` enables you to specify whether the week starts
on Sunday or Monday and whether the return value should be in the range from `0` to `53` or from `1` to `53`.

[`toISOWeek()`](#toWeek) is a compatibility function that is equivalent to `toWeek(date,3)`.

The following table describes how the mode argument works.

|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- |
| Mode First day of week Range Week 1 is the first week ...|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | 0 Sunday 0-53 with a Sunday in this year|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | 1 Monday 0-53 with 4 or more days this year|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | 2 Sunday 1-53 with a Sunday in this year|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | 3 Monday 1-53 with 4 or more days this year|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | 4 Sunday 0-53 with 4 or more days this year|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | 5 Monday 0-53 with a Monday in this year|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | 6 Sunday 1-53 with 4 or more days this year|  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | 7 Monday 1-53 with a Monday in this year|  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | | 8 Sunday 1-53 contains January 1|  |  |  |  | | --- | --- | --- | --- | | 9 Monday 1-53 contains January 1 | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |

For mode values with a meaning of "with 4 or more days this year," weeks are numbered according to ISO 8601:1988:

- If the week containing January 1 has 4 or more days in the new year, it is week 1.
- Otherwise, it is the last week of the previous year, and the next week is week 1.

For mode values with a meaning of "contains January 1", the week contains January 1 is week 1.
It does not matter how many days in the new year the week contained, even if it contained only one day.
I.e. if the last week of December contains January 1 of the next year, it will be week 1 of the next year.

The first argument can also be specified as [`String`](/docs/sql-reference/data-types/string) in a format supported by [`parseDateTime64BestEffort()`](/docs/sql-reference/functions/type-conversion-functions#parseDateTime64BestEffort). Support for string arguments exists only for reasons of compatibility with MySQL which is expected by certain 3rd party tools. As string argument support may in future be made dependent on new MySQL-compatibility settings and because string parsing is generally slow, it is recommended to not use it.

**Syntax**

```
toWeek(datetime[, mode[, time_zone]])
```

**Aliases**: `week`

**Arguments**

- `datetime` — Date or date with time to get the week number from. [`Date`](/docs/sql-reference/data-types/date) or [`DateTime`](/docs/sql-reference/data-types/datetime)
- `mode` — Optional. A mode `0` to `9` determines the first day of the week and the range of the week number. Default `0`. - `time_zone` — Optional. Time zone. [`String`](/docs/sql-reference/data-types/string)

**Returned value**

Returns the week number according to the specified mode. [`UInt32`](/docs/sql-reference/data-types/int-uint)

**Examples**

**Get week numbers with different modes**

```
SELECT toDate('2016-12-27') AS date, toWeek(date) AS week0, toWeek(date,1) AS week1, toWeek(date,9) AS week9
```

```
┌───────date─┬─week0─┬─week1─┬─week9─┐
│ 2016-12-27 │    52 │    52 │     1 │
└────────────┴───────┴───────┴───────┘
```

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

Introduced in: v1.1.0