Functions for working with dates and times

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

```
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
    subtractHours(date, 12) AS subtract_hours_with_date,
    subtractHours(date_time, 12) AS subtract_hours_with_date_time,
    subtractHours(date_time_string, 12) AS subtract_hours_with_date_time_string
```

```
┌─subtract_hours_with_date─┬─subtract_hours_with_date_time─┬─subtract_hours_with_date_time_string─┐
│      2023-12-31 12:00:00 │           2023-12-31 12:00:00 │              2023-12-31 12:00:00.000 │
└──────────────────────────┴───────────────────────────────┴──────────────────────────────────────┘
```

**Using alternative INTERVAL syntax**

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

```
┌─minus(CAST('⋯alHour(10))─┐
│      1998-06-15 14:00:00 │
└──────────────────────────┘
```

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

Introduced in: v22.11.0

Adds a negated interval to another interval or tuple of intervals.

Note: Intervals of the same type will be combined into a single interval. For instance if `toIntervalDay(2)` and `toIntervalDay(1)` are
passed then the result will be `(1)` rather than `(2,1)`.

**Syntax**

```
subtractInterval(interval_1, interval_2)
```

**Arguments**

- `interval_1` — First interval or interval of tuples. [`Interval`](/docs/sql-reference/data-types/int-uint) or [`Tuple(Interval)`](/docs/sql-reference/data-types/tuple)
- `interval_2` — Second interval to be negated. [`Interval`](/docs/sql-reference/data-types/int-uint)

**Returned value**

Returns a tuple of intervals [`Tuple(T)`](/docs/sql-reference/data-types/tuple)

**Examples**

**Subtract intervals**

```
SELECT subtractInterval(INTERVAL 1 DAY, INTERVAL 1 MONTH);
SELECT subtractInterval((INTERVAL 1 DAY, INTERVAL 1 YEAR), INTERVAL 1 MONTH);
SELECT subtractInterval(INTERVAL 2 DAY, INTERVAL 1 DAY);
```

```
┌─subtractInterval(toIntervalDay(1), toIntervalMonth(1))─┐
│ (1,-1)                                                 │
└────────────────────────────────────────────────────────┘
┌─subtractInterval((toIntervalDay(1), toIntervalYear(1)), toIntervalMonth(1))─┐
│ (1,1,-1)                                                                    │
└─────────────────────────────────────────────────────────────────────────────┘
┌─subtractInterval(toIntervalDay(2), toIntervalDay(1))─┐
│ (1)                                                  │
└──────────────────────────────────────────────────────┘
```

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

Introduced in: v22.6.0

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

**Syntax**

```
subtractMicroseconds(datetime, num)
```

**Arguments**

- `datetime` — Date with time to subtract specified number of microseconds from. [`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 microseconds 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` microseconds [`DateTime64`](/docs/sql-reference/data-types/datetime64)

**Examples**

**Subtract microseconds from different date time types**

```
WITH
    toDateTime('2024-01-01 00:00:00') AS date_time,
    '2024-01-01 00:00:00' AS date_time_string
SELECT
    subtractMicroseconds(date_time, 1000000) AS subtract_microseconds_with_date_time,
    subtractMicroseconds(date_time_string, 1000000) AS subtract_microseconds_with_date_time_string
```

```
┌─subtract_microseconds_with_date_time─┬─subtract_microseconds_with_date_time_string─┐
│           2023-12-31 23:59:59.000000 │                  2023-12-31 23:59:59.000000 │
└──────────────────────────────────────┴─────────────────────────────────────────────┘
```

**Using alternative INTERVAL syntax**

```
SELECT dateSub('1998-06-16'::DateTime, INTERVAL 10 microsecond)
```

```
┌─minus(CAST('1⋯osecond(10))─┐
│ 1998-06-15 23:59:59.999990 │
└────────────────────────────┘
```

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

Introduced in: v22.6.0

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

**Syntax**

```
subtractMilliseconds(datetime, num)
```

**Arguments**

- `datetime` — Date with time to subtract specified number of milliseconds from. [`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 milliseconds 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` milliseconds [`DateTime64`](/docs/sql-reference/data-types/datetime64)

**Examples**

**Subtract milliseconds from different date time types**

```
WITH
    toDateTime('2024-01-01 00:00:00') AS date_time,
    '2024-01-01 00:00:00' AS date_time_string
SELECT
    subtractMilliseconds(date_time, 1000) AS subtract_milliseconds_with_date_time,
    subtractMilliseconds(date_time_string, 1000) AS subtract_milliseconds_with_date_time_string
```

```
┌─subtract_milliseconds_with_date_time─┬─subtract_milliseconds_with_date_time_string─┐
│              2023-12-31 23:59:59.000 │                     2023-12-31 23:59:59.000 │
└──────────────────────────────────────┴─────────────────────────────────────────────┘
```

**Using alternative INTERVAL syntax**

```
SELECT dateSub('1998-06-16'::DateTime, INTERVAL 10 millisecond)
```

```
┌─minus(CAST('⋯second(10))─┐
│  1998-06-15 23:59:59.990 │
└──────────────────────────┘
```

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

Introduced in: v1.1.0

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

**Syntax**

```
subtractMinutes(datetime, num)
```

**Arguments**

- `datetime` — Date or date with time to subtract specified number of minutes 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 minutes 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` minutes [`DateTime`](/docs/sql-reference/data-types/datetime) or [`DateTime64(3)`](/docs/sql-reference/data-types/datetime64)

**Examples**

**Subtract minutes 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
    subtractMinutes(date, 30) AS subtract_minutes_with_date,
    subtractMinutes(date_time, 30) AS subtract_minutes_with_date_time,
    subtractMinutes(date_time_string, 30) AS subtract_minutes_with_date_time_string
```