DATE_DIFF()

All functions > DATE AND TIME > DATE_DIFF()

Returns the difference between start and end in the given unit (end − start). Matches the SQL/DuckDB argument order.

Signatures

Returns: Number of units between start and end (end − start)

DATE_DIFF(unit: VARCHAR, start: DATE | TIMESTAMP, end: DATE | TIMESTAMP) → BIGINT

Notes

• Result equals end − start in the given unit
• Positive when end is after start, negative otherwise
• DATE and TIMESTAMP inputs can be mixed freely
• Common units: 'second', 'minute', 'hour', 'day', 'week', 'month', 'year'
• Argument order matches DuckDB's native DATE_DIFF(part, startdate, enddate)
• Use DATE_SUBTRACT for the chaining-friendly (base, subtrahend, unit) order

Examples

Date inputs

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

Mixed DATE and TIMESTAMP inputs

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

Timestamp inputs

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