elasticsearch/docs/reference/query-languages/esql/_snippets/functions/examples/date_diff.md

% This is generated by ESQL's AbstractFunctionTestCase. Do no edit it. See ../README.md for how to regenerate it.

Examples

ROW date1 = TO_DATETIME("2023-12-02T11:00:00.000Z"), date2 = TO_DATETIME("2023-12-02T11:00:00.001Z")
| EVAL dd_ms = DATE_DIFF("microseconds", date1, date2)

date1:date	date2:date	dd_ms:integer
2023-12-02T11:00:00.000Z	2023-12-02T11:00:00.001Z	1000

When subtracting in calendar units - like year, month a.s.o. - only the fully elapsed units are counted. To avoid this and obtain also remainders, simply switch to the next smaller unit and do the date math accordingly.

ROW end_23=TO_DATETIME("2023-12-31T23:59:59.999Z"),
  start_24=TO_DATETIME("2024-01-01T00:00:00.000Z"),
    end_24=TO_DATETIME("2024-12-31T23:59:59.999")
| EVAL end23_to_start24=DATE_DIFF("year", end_23, start_24)
| EVAL end23_to_end24=DATE_DIFF("year", end_23, end_24)
| EVAL start_to_end_24=DATE_DIFF("year", start_24, end_24)

end_23:date	start_24:date	end_24:date	end23_to_start24:integer	end23_to_end24:integer	start_to_end_24:integer
2023-12-31T23:59:59.999Z	2024-01-01T00:00:00.000Z	2024-12-31T23:59:59.999Z	0	1	0