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

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

Examples

FROM employees
| KEEP first_name, last_name, hire_date
| EVAL year_hired = DATE_TRUNC(1 year, hire_date)

first_name:keyword	last_name:keyword	hire_date:date	year_hired:date
Alejandro	McAlpine	1991-06-26T00:00:00.000Z	1991-01-01T00:00:00.000Z
Amabile	Gomatam	1992-11-18T00:00:00.000Z	1992-01-01T00:00:00.000Z
Anneke	Preusig	1989-06-02T00:00:00.000Z	1989-01-01T00:00:00.000Z

Combine DATE_TRUNC with STATS to create date histograms. For example, the number of hires per year:

FROM employees
| EVAL year = DATE_TRUNC(1 year, hire_date)
| STATS hires = COUNT(emp_no) BY year
| SORT year

hires:long	year:date
11	1985-01-01T00:00:00.000Z
11	1986-01-01T00:00:00.000Z
15	1987-01-01T00:00:00.000Z
9	1988-01-01T00:00:00.000Z
13	1989-01-01T00:00:00.000Z
12	1990-01-01T00:00:00.000Z
6	1991-01-01T00:00:00.000Z
8	1992-01-01T00:00:00.000Z
3	1993-01-01T00:00:00.000Z
4	1994-01-01T00:00:00.000Z
5	1995-01-01T00:00:00.000Z
1	1996-01-01T00:00:00.000Z
1	1997-01-01T00:00:00.000Z
1	1999-01-01T00:00:00.000Z

Or an hourly error rate:

FROM sample_data
| EVAL error = CASE(message LIKE "*error*", 1, 0)
| EVAL hour = DATE_TRUNC(1 hour, @timestamp)
| STATS error_rate = AVG(error) by hour
| SORT hour

error_rate:double	hour:date
0.0	2023-10-23T12:00:00.000Z
0.6	2023-10-23T13:00:00.000Z