DATE_DIFF()

All functions > DATE AND TIME > DATE_DIFF()

Returns the number of calendar boundary crossings in timestamp1 − timestamp2. Same argument order as DATE_SUBTRACT (chaining-friendly).

Signatures

Returns: Boundary crossings in timestamp1 − timestamp2

DATE_DIFF(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')

Notes

Counts calendar boundary crossings in timestamp1 − timestamp2 (not complete elapsed units)
Crossing midnight once → 1 day even if only 2 hours elapsed; Jan 31 → Feb 1 → 1 month
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_SUBTRACT; use DATE_SUBTRACT when you want complete elapsed units instead
Chaining: timestamp1.DATE_DIFF(timestamp2, unit)

Examples

Boundary crossings (not elapsed units)

FeatureQL

SELECT
    -- 2 hours overnight: one midnight crossed
    f1 := DATE_DIFF(
        TIMESTAMP '2024-01-02 01:00:00',
        TIMESTAMP '2024-01-01 23:00:00',
        'day'
    ),
    -- Adjacent calendar days in different months: one month boundary
    f2 := DATE_DIFF(DATE '2024-02-01', DATE '2024-01-31', 'month')
;

Result

f1 BIGINT	f2 BIGINT
1	1

Date inputs

FeatureQL

SELECT
    -- Days between two dates
    f1 := DATE_DIFF(DATE '2024-03-22', DATE '2024-03-15', 'day'),
    -- Month boundaries from mid-March to June 1
    f2 := DATE_DIFF(DATE '2024-06-01', DATE '2024-03-15', 'month')
;

Result

f1 BIGINT	f2 BIGINT
7	3

Mixed DATE and TIMESTAMP inputs

FeatureQL

SELECT
    -- Calendar days from March 15 to June 1 (time-of-day ignored for 'day')
    f1 := DATE_DIFF(DATE '2024-06-01', TIMESTAMP '2024-03-15 10:00:00', 'day')
;

Result

f1 BIGINT
78

Timestamp inputs

FeatureQL

SELECT
    -- Difference in days
    f1 := DATE_DIFF(
        TIMESTAMP '2024-03-22 10:00:00',
        TIMESTAMP '2024-03-15 10:00:00',
        'day'
    ),
    -- Difference in hours
    f2 := DATE_DIFF(
        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/08/04 11:25:38

On this page

Signatures Notes Examples Boundary crossings (not elapsed units)Date inputs Mixed DATE and TIMESTAMP inputs Timestamp inputs