DATE_SUBTRACT()

All functions > DATE AND TIME > DATE_SUBTRACT()

Returns timestamp1 minus timestamp2 in the given unit. Chaining-friendly: the base value comes first.

Signatures

Returns: Number of units between the two values (timestamp1 − timestamp2)

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

Notes

• Result equals timestamp1 − timestamp2 in the given unit
• 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'
• Use DATE_DIFF for the SQL-conventional (unit, start, end) argument order

Examples

Date inputs

SELECT
    f1 := DATE_SUBTRACT(DATE '2024-03-22', DATE '2024-03-15', 'day'), -- Days between two dates
    f2 := DATE_SUBTRACT(DATE '2024-06-01', DATE '2024-03-15', 'month') -- Months between two dates
;

Mixed DATE and TIMESTAMP inputs

SELECT
    f1 := DATE_SUBTRACT(DATE '2024-06-01', TIMESTAMP '2024-03-15 10:00:00', 'day') -- Days from a timestamp to a date boundary
;

Timestamp inputs

SELECT
    f1 := DATE_SUBTRACT(TIMESTAMP '2024-03-22 10:00:00', TIMESTAMP '2024-03-15 10:00:00', 'day'), -- Difference in days
    f2 := DATE_SUBTRACT(TIMESTAMP '2024-03-15 13:00:00', TIMESTAMP '2024-03-15 10:00:00', 'hour') -- Difference in hours
;