SUBTRACT()

All functions > DATE AND TIME > SUBTRACT()

Subtracts an interval from a timestamp or date, or returns the interval between two timestamps or two dates.

Signatures

Date and time subtraction

Returns: Timestamp after subtracting an interval, or the interval between two datetimes

SUBTRACT(timestamp1: TIMESTAMP or DATE, timestamp2: INTERVAL, TIMESTAMP, or DATE) → TIMESTAMP or INTERVAL

Signature notes:

• timestamp - interval or date - interval → TIMESTAMP
• timestamp1 - timestamp2 or date1 - date2 → INTERVAL
• Returns NULL if either operand is NULL

Related operators

• - [subtraction]

Examples

SUBTRACT(...)

SELECT
    -- Function call
    f1 := SUBTRACT(10, 3)
;

.SUBTRACT(...) — chained

SELECT
    -- Integer literal: use parentheses for `.SUBTRACT` on a BIGINT value
    f1 := (10).SUBTRACT(3),
    -- Decimal base: chain without extra parentheses
    f2 := 10.0.SUBTRACT(3)
;

Date and time

SELECT
    -- Chained subtract on a timestamp
    f1 := TIMESTAMP '2021-01-02'.SUBTRACT(INTERVAL '1 DAY')
;

Edge cases

SELECT
    -- Interval between two timestamps via chained call
    f1 := TIMESTAMP '2021-01-02'.SUBTRACT(TIMESTAMP '2021-01-01')
;