Functions for working with dates and times

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

Introduced in: v22.8.0

Returns the current date and time at the moment of processing of each block of data. In contrast to the function [`now`](#now), it is not a constant expression, and the returned value will be different in different blocks for long-running queries.

It makes sense to use this function to generate the current time in long-running `INSERT SELECT` queries.

**Syntax**

```
nowInBlock([timezone])
```

**Arguments**

- `timezone` — Optional. Timezone name for the returned value. [`String`](/docs/sql-reference/data-types/string)

**Returned value**

Returns the current date and time at the moment of processing of each block of data. [`DateTime`](/docs/sql-reference/data-types/datetime)

**Examples**

**Difference with the now() function**

```
SELECT
    now(),
    nowInBlock(),
    sleep(1)
FROM numbers(3)
SETTINGS max_block_size = 1
FORMAT PrettyCompactMonoBlock
```

```
┌───────────────now()─┬────────nowInBlock()─┬─sleep(1)─┐
│ 2022-08-21 19:41:19 │ 2022-08-21 19:41:19 │        0 │
│ 2022-08-21 19:41:19 │ 2022-08-21 19:41:20 │        0 │
│ 2022-08-21 19:41:19 │ 2022-08-21 19:41:21 │        0 │
└─────────────────────┴─────────────────────┴──────────┘
```

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

Introduced in: v25.8.0

Returns the current date and time at the moment of processing of each block of data in milliseconds. In contrast to the function [now64](#now64), it is not a constant expression, and the returned value will be different in different blocks for long-running queries.

It makes sense to use this function to generate the current time in long-running INSERT SELECT queries.

**Syntax**

```
nowInBlock64([scale[, timezone]])
```

**Arguments**

- `scale` — Optional. Tick size (precision): 10^-precision seconds. Valid range: [0 : 9]. Typically, are used - 3 (default) (milliseconds), 6 (microseconds), 9 (nanoseconds). [`UInt8`](/docs/sql-reference/data-types/int-uint)
- `timezone` — Optional. Timezone name for the returned value. [`String`](/docs/sql-reference/data-types/string)

**Returned value**

Returns the current date and time at the moment of processing of each block of data with sub-second precision. [`DateTime64`](/docs/sql-reference/data-types/datetime64)

**Examples**

**Difference with the now64() function**

```
SELECT
    now64(),
    nowInBlock64(),
    sleep(1)
FROM numbers(3)
SETTINGS max_block_size = 1
FORMAT PrettyCompactMonoBlock
```

```
┌─────────────────now64()─┬──────────nowInBlock64()─┬─sleep(1)─┐
│ 2025-07-29 17:07:29.526 │ 2025-07-29 17:07:29.534 │        0 │
│ 2025-07-29 17:07:29.526 │ 2025-07-29 17:07:30.535 │        0 │
│ 2025-07-29 17:07:29.526 │ 2025-07-29 17:07:31.535 │        0 │
└─────────────────────────┴─────────────────────────┴──────────┘
```

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

Introduced in: v23.6.0

Returns the timezone of the server, i.e. the value of the [`timezone`](/docs/operations/server-configuration-parameters/settings#timezone) setting.
If the function is executed in the context of a distributed table, then it generates a normal column with values relevant to each shard. Otherwise, it produces a constant value.

**Syntax**

```
serverTimezone()
```

**Aliases**: `serverTimeZone`

**Arguments**

- None.

**Returned value**

Returns the server timezone as a [`String`](/docs/sql-reference/data-types/string)

**Examples**

**Usage example**

```
SELECT serverTimeZone()
```

```
┌─serverTimeZone()─┐
│ UTC              │
└──────────────────┘
```

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

Introduced in: v23.9.0

Subtracts the time interval from the provided date, date with time or string-encoded date or date with time.
If the subtraction results in a value outside the bounds of the data type, the result is undefined.

**Syntax**

```
subDate(datetime, interval)
```

**Arguments**

- `datetime` — The date or date with time from which `interval` is subtracted. [`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)
- `interval` — Interval to subtract. [`Interval`](/docs/sql-reference/data-types/int-uint)

**Returned value**

Returns date or date with time obtained by subtracting `interval` from `datetime`. [`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)

**Examples**

**Subtract interval from date**

```
SELECT subDate(toDate('2018-01-01'), INTERVAL 3 YEAR)
```

```
┌─subDate(toDate('2018-01-01'), toIntervalYear(3))─┐
│                                       2015-01-01 │
└──────────────────────────────────────────────────┘
```

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

Introduced in: v1.1.0

Subtracts a specified number of days from a date, a date with time or a string-encoded date or date with time.

**Syntax**

```
subtractDays(datetime, num)
```

**Arguments**

- `datetime` — Date or date with time to subtract specified number of days 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) or [`String`](/docs/sql-reference/data-types/string)
- `num` — Number of days to subtract. [`(U)Int*`](/docs/sql-reference/data-types/int-uint) or [`Float*`](/docs/sql-reference/data-types/float)

**Returned value**

Returns `datetime` minus `num` days [`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)

**Examples**

**Subtract days from different date types**

```
WITH
    toDate('2024-01-01') AS date,
    toDateTime('2024-01-01 00:00:00') AS date_time,
    '2024-01-01 00:00:00' AS date_time_string
SELECT
    subtractDays(date, 31) AS subtract_days_with_date,
    subtractDays(date_time, 31) AS subtract_days_with_date_time,
    subtractDays(date_time_string, 31) AS subtract_days_with_date_time_string
```

```
┌─subtract_days_with_date─┬─subtract_days_with_date_time─┬─subtract_days_with_date_time_string─┐
│              2023-12-01 │          2023-12-01 00:00:00 │             2023-12-01 00:00:00.000 │
└─────────────────────────┴──────────────────────────────┴─────────────────────────────────────┘
```

**Using alternative INTERVAL syntax**

```
SELECT dateSub('1998-06-16'::Date, INTERVAL 10 day)
```

```
┌─minus(CAST('⋯valDay(10))─┐
│               1998-06-06 │
└──────────────────────────┘
```

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

Introduced in: v1.1.0

Subtracts a specified number of hours from a date, a date with time or a string-encoded date or date with time.

**Syntax**

```
subtractHours(datetime, num)
```

**Arguments**

- `datetime` — Date or date with time to subtract specified number of hours 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) or [`String`](/docs/sql-reference/data-types/string)
- `num` — Number of hours to subtract. [`(U)Int*`](/docs/sql-reference/data-types/int-uint) or [`Float*`](/docs/sql-reference/data-types/float)

**Returned value**

Returns `datetime` minus `num` hours [`DateTime`](/docs/sql-reference/data-types/datetime) or [`DateTime64(3)`](/docs/sql-reference/data-types/datetime64)

**Examples**

**Subtract hours from different date types**