Functions for working with dates and times

**Arguments**

- `startdate` — The first time value to subtract (the subtrahend). [`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)
- `enddate` — The second time value to subtract from (the minuend). [`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 difference between `enddate` and `startdate` expressed in seconds. [`Int64`](/docs/sql-reference/data-types/int-uint)

**Examples**

**Calculate time difference in seconds**

```
SELECT timeDiff(toDateTime('2018-01-01 22:00:00'), toDateTime('2018-01-02 23:00:00')) AS res
```

```
┌───res─┐
│ 90000 │
└───────┘
```

**Calculate time difference and convert to hours**

```
SELECT timeDiff(toDateTime('2018-01-01 22:00:00'), toDateTime('2018-01-02 23:00:00')) / 3600 AS hours
```

```
┌─hours─┐
│    25 │
└───────┘
```

**Equivalent to dateDiff with seconds**

```
SELECT
    timeDiff(toDateTime('2021-12-29'), toDateTime('2022-01-01')) AS time_diff_result,
    dateDiff('second', toDateTime('2021-12-29'), toDateTime('2022-01-01')) AS date_diff_result
```

```
┌─time_diff_result─┬─date_diff_result─┐
│           259200 │           259200 │
└──────────────────┴──────────────────┘
```

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

Introduced in: v1.1.0

Round the time to the start of a half-an-hour length interval.

Note

Although this function can take values of the extended types `Date32` and `DateTime64` as an argument,
passing it a time outside the normal range (year 1970 to 2149 for `Date` / 2106 for `DateTime`) will produce wrong results.

**Syntax**

```
timeSlot(time[, time_zone])
```

**Arguments**

- `time` — Time to round to the start of a half-an-hour length interval. [`DateTime`](/docs/sql-reference/data-types/datetime) or [`Date32`](/docs/sql-reference/data-types/date32) or [`DateTime64`](/docs/sql-reference/data-types/datetime64)
- `time_zone` — Optional. A String type const value or an expression representing the time zone. [`String`](/docs/sql-reference/data-types/string)

**Returned value**

Returns the time rounded to the start of a half-an-hour length interval. [`DateTime`](/docs/sql-reference/data-types/datetime)

**Examples**

**Round time to half-hour interval**

```
SELECT timeSlot(toDateTime('2000-01-02 03:04:05', 'UTC'))
```

```
┌─timeSlot(toDateTime('2000-01-02 03:04:05', 'UTC'))─┐
│                                2000-01-02 03:00:00 │
└────────────────────────────────────────────────────┘
```

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

Introduced in: v1.1.0

For a time interval starting at `StartTime` and continuing for `Duration` seconds, it returns an array of moments in time, consisting of points from this interval rounded down to the `Size` in seconds. `Size` is an optional parameter set to 1800 (30 minutes) by default.

This is necessary, for example, when searching for pageviews in the corresponding session.

For `DateTime64`, the return value's scale can differ from the scale of `StartTime`. The highest scale among all given arguments is taken.

**Syntax**

```
timeSlots(StartTime, Duration[, Size])
```

**Arguments**

- `StartTime` — Starting time for the interval. [`DateTime`](/docs/sql-reference/data-types/datetime) or [`DateTime64`](/docs/sql-reference/data-types/datetime64)
- `Duration` — Duration of the interval in seconds. [`UInt32`](/docs/sql-reference/data-types/int-uint) or [`DateTime64`](/docs/sql-reference/data-types/datetime64)
- `Size` — Optional. Size of time slots in seconds. Default is 1800 (30 minutes). [`UInt32`](/docs/sql-reference/data-types/int-uint) or [`DateTime64`](/docs/sql-reference/data-types/datetime64)

**Returned value**

Returns an array of DateTime/DateTime64 (return type matches the type of `StartTime`). For DateTime64, the return value's scale can differ from the scale of `StartTime` - the highest scale among all given arguments is taken. [`Array(DateTime)`](/docs/sql-reference/data-types/array) or [`Array(DateTime64)`](/docs/sql-reference/data-types/array)

**Examples**

**Generate time slots for an interval**

```
SELECT timeSlots(toDateTime('2012-01-01 12:20:00'), toUInt32(600));
SELECT timeSlots(toDateTime('1980-12-12 21:01:02', 'UTC'), toUInt32(600), 299);
SELECT timeSlots(toDateTime64('1980-12-12 21:01:02.1234', 4, 'UTC'), toDecimal64(600.1, 1), toDecimal64(299, 0))
```

```
┌─timeSlots(toDateTime('2012-01-01 12:20:00'), toUInt32(600))─┐
│ ['2012-01-01 12:00:00','2012-01-01 12:30:00']               │
└─────────────────────────────────────────────────────────────┘
┌─timeSlots(toDateTime('1980-12-12 21:01:02', 'UTC'), toUInt32(600), 299)─┐
│ ['1980-12-12 20:56:13','1980-12-12 21:01:12','1980-12-12 21:06:11']     │
└─────────────────────────────────────────────────────────────────────────┘
┌─timeSlots(toDateTime64('1980-12-12 21:01:02.1234', 4, 'UTC'), toDecimal64(600.1, 1), toDecimal64(299, 0))─┐
│ ['1980-12-12 20:56:13.0000','1980-12-12 21:01:12.0000','1980-12-12 21:06:11.0000']                        │
└───────────────────────────────────────────────────────────────────────────────────────────────────────────┘
```

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

Introduced in: v23.9.0

Converts the first argument `expr` to type [`DateTime64(6)`](/docs/sql-reference/data-types/datetime64).
If a second argument `expr_time` is provided, it adds the specified time to the converted value.

**Syntax**

```
timestamp(expr[, expr_time])
```

**Arguments**

- `expr` — Date or date with time. [`String`](/docs/sql-reference/data-types/string)
- `expr_time` — Optional. Time to add to the converted value. [`String`](/docs/sql-reference/data-types/string)

**Returned value**

Returns the converted value of `expr`, or `expr` with added time [`DateTime64(6)`](/docs/sql-reference/data-types/datetime64)

**Examples**

**Convert date string to DateTime64(6)**

```
SELECT timestamp('2023-12-31') AS ts;
```

```
┌─────────────────────────ts─┐
│ 2023-12-31 00:00:00.000000 │
└────────────────────────────┘
```

**Add time to date string**

```
SELECT timestamp('2023-12-31 12:00:00', '12:00:00.11') AS ts;
```

```
┌─────────────────────────ts─┐
│ 2024-01-01 00:00:00.110000 │
└────────────────────────────┘
```

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

Introduced in: v21.4.0

Returns the time zone name of the current session or converts a time zone
offset or name to a canonical time zone name.

**Syntax**

```
timezone()
```

**Aliases**: `timeZone`

**Arguments**

- None.

**Returned value**

Returns the canonical time zone name as a [`String`](/docs/sql-reference/data-types/string)

**Examples**

**Usage example**

```
SELECT timezone()
```

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

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

Introduced in: v21.4.0

Returns the timezone name of a [`DateTime`](/docs/sql-reference/data-types/datetime) or [`DateTime64`](/docs/sql-reference/data-types/datetime64) value.

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