Functions for working with dates and times

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

```
┌─subtract_minutes_with_date─┬─subtract_minutes_with_date_time─┬─subtract_minutes_with_date_time_string─┐
│        2023-12-31 23:30:00 │             2023-12-31 23:30:00 │                2023-12-31 23:30:00.000 │
└────────────────────────────┴─────────────────────────────────┴────────────────────────────────────────┘
```

**Using alternative INTERVAL syntax**

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

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

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

Introduced in: v1.1.0

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

**Syntax**

```
subtractMonths(datetime, num)
```

**Arguments**

- `datetime` — Date or date with time to subtract specified number of months 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 months 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` months [`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 months 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
    subtractMonths(date, 1) AS subtract_months_with_date,
    subtractMonths(date_time, 1) AS subtract_months_with_date_time,
    subtractMonths(date_time_string, 1) AS subtract_months_with_date_time_string
```

```
┌─subtract_months_with_date─┬─subtract_months_with_date_time─┬─subtract_months_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 month)
```

```
┌─minus(CAST('⋯lMonth(10))─┐
│               1997-08-16 │
└──────────────────────────┘
```

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

Introduced in: v20.1.0

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

**Syntax**

```
subtractNanoseconds(datetime, num)
```

**Arguments**

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

**Examples**

**Subtract nanoseconds 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
    subtractNanoseconds(date_time, 1000) AS subtract_nanoseconds_with_date_time,
    subtractNanoseconds(date_time_string, 1000) AS subtract_nanoseconds_with_date_time_string
```

```
┌─subtract_nanoseconds_with_date_time─┬─subtract_nanoseconds_with_date_time_string─┐
│       2023-12-31 23:59:59.999999000 │              2023-12-31 23:59:59.999999000 │
└─────────────────────────────────────┴────────────────────────────────────────────┘
```

**Using alternative INTERVAL syntax**

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

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

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

Introduced in: v20.1.0

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

**Syntax**

```
subtractQuarters(datetime, num)
```

**Arguments**

- `datetime` — Date or date with time to subtract specified number of quarters 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 quarters 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` quarters [`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 quarters 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
    subtractQuarters(date, 1) AS subtract_quarters_with_date,
    subtractQuarters(date_time, 1) AS subtract_quarters_with_date_time,
    subtractQuarters(date_time_string, 1) AS subtract_quarters_with_date_time_string
```

```
┌─subtract_quarters_with_date─┬─subtract_quarters_with_date_time─┬─subtract_quarters_with_date_time_string─┐
│                  2023-10-01 │              2023-10-01 00:00:00 │                 2023-10-01 00:00:00.000 │
└─────────────────────────────┴──────────────────────────────────┴─────────────────────────────────────────┘
```

**Using alternative INTERVAL syntax**

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

```
┌─minus(CAST('1⋯Quarter(10))─┐
│                1996-09-16 │
└───────────────────────────┘
```

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

Introduced in: v1.1.0

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

**Syntax**

```
subtractSeconds(datetime, num)
```

**Arguments**

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

**Examples**

**Subtract seconds from different date types**