Functions for working with dates and times

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

```
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
    subtractSeconds(date, 60) AS subtract_seconds_with_date,
    subtractSeconds(date_time, 60) AS subtract_seconds_with_date_time,
    subtractSeconds(date_time_string, 60) AS subtract_seconds_with_date_time_string
```

```
┌─subtract_seconds_with_date─┬─subtract_seconds_with_date_time─┬─subtract_seconds_with_date_time_string─┐
│        2023-12-31 23:59:00 │             2023-12-31 23:59:00 │                2023-12-31 23:59:00.000 │
└────────────────────────────┴─────────────────────────────────┴────────────────────────────────────────┘
```

**Using alternative INTERVAL syntax**

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

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

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

Introduced in: v22.11.0

Consecutively subtracts a tuple of intervals from a date or a date with time.

**Syntax**

```
subtractTupleOfIntervals(datetime, intervals)
```

**Arguments**

- `datetime` — Date or date with time to subtract intervals 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)
- `intervals` — Tuple of intervals to subtract from `datetime`. [`Tuple(Interval)`](/docs/sql-reference/data-types/tuple)

**Returned value**

Returns `date` with subtracted `intervals` [`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 tuple of intervals from date**

```
WITH toDate('2018-01-01') AS date SELECT subtractTupleOfIntervals(date, (INTERVAL 1 DAY, INTERVAL 1 YEAR))
```

```
┌─subtractTupl⋯alYear(1)))─┐
│               2016-12-31 │
└──────────────────────────┘
```

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

Introduced in: v1.1.0

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

**Syntax**

```
subtractWeeks(datetime, num)
```

**Arguments**

- `datetime` — Date or date with time to subtract specified number of weeks 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 weeks 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` weeks [`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 weeks 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
    subtractWeeks(date, 1) AS subtract_weeks_with_date,
    subtractWeeks(date_time, 1) AS subtract_weeks_with_date_time,
    subtractWeeks(date_time_string, 1) AS subtract_weeks_with_date_time_string
```

```
┌─subtract_weeks_with_date─┬─subtract_weeks_with_date_time─┬─subtract_weeks_with_date_time_string─┐
│               2023-12-25 │           2023-12-25 00:00:00 │              2023-12-25 00:00:00.000 │
└──────────────────────────┴───────────────────────────────┴──────────────────────────────────────┘
```

**Using alternative INTERVAL syntax**

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

```
┌─minus(CAST('⋯alWeek(10))─┐
│               1998-04-07 │
└──────────────────────────┘
```

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

Introduced in: v1.1.0

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

**Syntax**

```
subtractYears(datetime, num)
```

**Arguments**

- `datetime` — Date or date with time to subtract specified number of years 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 years 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` years [`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 years 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
    subtractYears(date, 1) AS subtract_years_with_date,
    subtractYears(date_time, 1) AS subtract_years_with_date_time,
    subtractYears(date_time_string, 1) AS subtract_years_with_date_time_string
```

```
┌─subtract_years_with_date─┬─subtract_years_with_date_time─┬─subtract_years_with_date_time_string─┐
│               2023-01-01 │           2023-01-01 00:00:00 │              2023-01-01 00:00:00.000 │
└──────────────────────────┴───────────────────────────────┴──────────────────────────────────────┘
```

**Using alternative INTERVAL syntax**

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

```
┌─minus(CAST('⋯alYear(10))─┐
│               1988-06-16 │
└──────────────────────────┘
```

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

Introduced in: v23.4.0

Returns the difference between two dates or dates with time values in seconds.
The difference is calculated as `enddate` - `startdate`.

This function is equivalent to `dateDiff('second', startdate, enddate)`.

For calculating time differences in other units (hours, days, months, etc.), use the [`dateDiff`](#dateDiff) function instead.

**Syntax**

```
timeDiff(startdate, enddate)
```

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