Return the difference between two dates or timestamps in a specified unit.
DATE_DIFF(unit, start, end)## Overview Computes the difference between two dates or timestamps in a unit you specify, returned as an INTEGER number of whole unit boundaries crossed. The argument order is (unit, start, end), and the result is end minus start: positive when end is later, negative when end is earlier. Use DATE_DIFF for age calculations, SLA tracking, or any time-difference reporting that needs a unit coarser or finer than days (for which DATEDIFF is the day-only shortcut). ## Behavior - Returns an INTEGER count of unit boundaries crossed between start and end. - Returns NULL if any argument is NULL. - Counts boundaries, not elapsed magnitudes. DATE_DIFF('year', '2024-12-31', '2025-01-01') returns 1 because a year boundary was crossed, even though only one day elapsed. - Works on both DATE and TIMESTAMP values; mixed inputs are implicitly aligned. - Unit names are case-insensitive and accept common synonyms (for example 'yr', 'yrs' for 'year' in many engines). - Accepts the following units: year, quarter, month, week, day, hour, minute, second, millisecond, microsecond. ## Timezone handling - For TIMESTAMP WITHOUT TIME ZONE values the difference is computed on the raw wall-clock reading. - For TIMESTAMP WITH TIME ZONE values the underlying instants are compared, so crossing DST does not affect a 'day' count when only the zone offset changes. - Week counts use ISO 8601 week boundaries (Monday as first day of week) in this engine. ## Compatibility - Unit-driven date difference function. Standard SQL provides a (date - date) INTEGER-days operator; DATE_DIFF generalizes it to arbitrary units.
| Name | Type | Description |
|---|---|---|
unit | Specifies the unit for the result. Accepts 'year', 'quarter', 'month', 'week', 'day', 'hour', 'minute', 'second', 'millisecond', 'microsecond'. Case-insensitive. | |
start | Specifies the start date or timestamp. The result is computed as end minus start. | |
end | Specifies the end date or timestamp. |
-- Difference in days between two dates
SELECT DATE_DIFF('day', DATE '2025-01-01', DATE '2025-03-15') AS days;-- Difference in months across one calendar year
SELECT DATE_DIFF('month', DATE '2024-06-01', DATE '2025-06-01') AS months;-- Hours between two timestamps in the same day
SELECT DATE_DIFF('hour', TIMESTAMP '2025-03-15 08:00:00', TIMESTAMP '2025-03-15 17:30:00') AS hours;-- Negative result when start is after end
SELECT DATE_DIFF('day', DATE '2025-12-31', DATE '2025-01-01') AS days;-- Employee tenure in whole years
SELECT employee_id, DATE_DIFF('year', hire_date, CURRENT_DATE()) AS years_employed
FROM workforce.hr.employees;-- Bucket events by minute offset from a fixed origin
SELECT DATE_DIFF('minute', TIMESTAMP '2025-03-15 00:00:00', event_ts) / 5 AS five_min_bucket,
COUNT(*) AS events
FROM telemetry.web.requests
GROUP BY five_min_bucket
ORDER BY five_min_bucket;