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

sql

Parameter	Type	Required	Description
`timestamp1`	`DATE	TIMESTAMP`	Yes
`timestamp2`	`DATE	TIMESTAMP`	Yes
`unit`	`VARCHAR`	Yes	Time unit for the result (e.g. 'day', 'hour') compile-time

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)

FeatureQL

RETURN
    -- 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'
    ),
    -- One calendar day apart: zero complete months elapsed
    f2 := DATE_SUBTRACT(DATE '2024-02-01', DATE '2024-01-31', 'month')
;

Result

f1 BIGINT	f2 BIGINT
0	0

Date inputs

FeatureQL

RETURN
    -- Seven complete days between two dates
    f1 := DATE_SUBTRACT(DATE '2024-03-22', DATE '2024-03-15', 'day'),
    -- Complete months from mid-March to June 1
    f2 := DATE_SUBTRACT(DATE '2024-06-01', DATE '2024-03-15', 'month')
;

Result

f1 BIGINT	f2 BIGINT
7	2

Mixed DATE and TIMESTAMP inputs

FeatureQL

RETURN
    -- Complete days from mid-morning March 15 to June 1 midnight
    f1 := DATE_SUBTRACT(
        DATE '2024-06-01',
        TIMESTAMP '2024-03-15 10:00:00',
        'day'
    )
;

Result

f1 BIGINT
77

Timestamp inputs

FeatureQL

RETURN
    -- Exact 7×24h: seven complete days
    f1 := DATE_SUBTRACT(
        TIMESTAMP '2024-03-22 10:00:00',
        TIMESTAMP '2024-03-15 10:00:00',
        'day'
    ),
    -- Three complete hours
    f2 := DATE_SUBTRACT(
        TIMESTAMP '2024-03-15 13:00:00',
        TIMESTAMP '2024-03-15 10:00:00',
        'hour'
    )
;

Result

f1 BIGINT	f2 BIGINT
7	3

Last update at: 2026/10/13 08:32:21

On this page

Signatures Notes Examples Elapsed units (not boundary crossings)Date inputs Mixed DATE and TIMESTAMP inputs Timestamp inputs