Compute the difference between two date/timestamp expressions
(end - start) expressed in the given unit.
Dialect notes (this is the function with the widest genuine divergence):
mysql/mariadb: TIMESTAMPDIFF(UNIT, start, end) (native, all units).
mssql/snowflake: DATEDIFF(unit, start, end) (native, all units).
redshift: also has a genuine native DATEDIFF(datepart, start, end)
function (SQL-Server-style), unlike Postgres which has no such function.
clickhouse: native dateDiff('unit', start, end).
postgres/cockroachdb: NO native DATEDIFF — day uses date subtraction
(end::date - start::date), sub-day units use
EXTRACT(EPOCH FROM (end - start)) scaled, and year/month are computed
from EXTRACT(YEAR/MONTH FROM ...) components.
oracle: NO native DATEDIFF — DATE subtraction yields a day count
(TRUNC(end) - TRUNC(start) for whole days, scaled for other sub-day
units); month/year use MONTHS_BETWEEN.
sqlite: NO native DATEDIFF — built on julianday() differences (day and
sub-day units) and strftime('%Y'/'%m', ...) component differences
(month/year, since julianday-based month math is not calendar-exact).
db2: NO native DATEDIFF — built from DAYS() (whole-day difference) and
MIDNIGHT_SECONDS() (intra-day seconds) for day/sub-day units, and
YEAR()/MONTH() component differences for month/year.
Compute the difference between two date/timestamp expressions (
end - start) expressed in the given unit.Dialect notes (this is the function with the widest genuine divergence):
TIMESTAMPDIFF(UNIT, start, end)(native, all units).DATEDIFF(unit, start, end)(native, all units).DATEDIFF(datepart, start, end)function (SQL-Server-style), unlike Postgres which has no such function.dateDiff('unit', start, end).end::date - start::date), sub-day units useEXTRACT(EPOCH FROM (end - start))scaled, and year/month are computed fromEXTRACT(YEAR/MONTH FROM ...)components.TRUNC(end) - TRUNC(start)for whole days, scaled for other sub-day units); month/year useMONTHS_BETWEEN.julianday()differences (day and sub-day units) andstrftime('%Y'/'%m', ...)component differences (month/year, since julianday-based month math is not calendar-exact).DAYS()(whole-day difference) andMIDNIGHT_SECONDS()(intra-day seconds) for day/sub-day units, andYEAR()/MONTH()component differences for month/year.