DATE_SUBTRACT()

All functions > DATE AND TIME > DATE_SUBTRACT()

Returns the number of complete elapsed units in timestamp1 − timestamp2. Chaining-friendly: the base value comes first.

Signatures

Returns: Complete elapsed units in timestamp1 − timestamp2

DATE_SUBTRACT(timestamp1: DATE | TIMESTAMP, timestamp2: DATE | TIMESTAMP, unit: VARCHAR) → BIGINT

Notes

• Counts complete elapsed units in timestamp1 − timestamp2 (not calendar boundary crossings)
• For day/hour/minute/second this matches DATE_PART(unit, timestamp1 - timestamp2)
• Partial units are truncated: e.g. 2 hours overnight → 0 days; 14 days in February → 0 months
• Positive when timestamp1 is after timestamp2, negative otherwise
• DATE and TIMESTAMP inputs can be mixed freely
• Common units: 'second', 'minute', 'hour', 'day', 'week', 'month', 'year'
• Same argument order as DATE_DIFF; use DATE_DIFF when you want boundary crossings instead

Examples

Elapsed units (not boundary crossings)

SELECT
    -- 2 hours overnight: zero complete days elapsed
    f1 := DATE_SUBTRACT(
        TIMESTAMP '2024-01-02 01:00:00',
        TIMESTAMP '2024-01-01 23:00:00',
        'day'
    )
;

Date inputs

SELECT
    -- One calendar day apart: zero complete months elapsed
    f1 := DATE_SUBTRACT(DATE '2024-02-01', DATE '2024-01-31', 'month')
;

Mixed DATE and TIMESTAMP inputs

SELECT
    -- Seven complete days between two dates
    f1 := DATE_SUBTRACT(DATE '2024-03-22', DATE '2024-03-15', 'day')
;

Timestamp inputs

SELECT
    -- Complete months from mid-March to June 1
    f1 := DATE_SUBTRACT(DATE '2024-06-01', DATE '2024-03-15', 'month')
;