{{esql}} time spans [esql-time-spans]

Time spans represent intervals between two datetime values. There are currently two supported types of time spans:

• DATE_PERIOD specifies intervals in years, quarters, months, weeks and days
• TIME_DURATION specifies intervals in hours, minutes, seconds and milliseconds

A time span requires two elements: an integer value and a temporal unit.

Time spans work with grouping functions such as BUCKET, scalar functions such as DATE_TRUNC and arithmetic operators such as + and -. Convert strings to time spans using TO_DATEPERIOD, TO_TIMEDURATION, or the cast operators ::DATE_PERIOD, ::TIME_DURATION.

Examples of using time spans in {{esql}} [esql-time-spans-examples]

With BUCKET:

FROM employees
| WHERE hire_date >= "1985-01-01T00:00:00Z" AND hire_date < "1986-01-01T00:00:00Z"
| STATS hires_per_week = COUNT(*) BY week = BUCKET(hire_date, 1 week)
| SORT week

With DATE_TRUNC:

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

With + and/or -:

FROM sample_data
| WHERE @timestamp > NOW() - 1 hour

When a time span is provided as a named parameter in string format, TO_DATEPERIOD, ::DATE_PERIOD, TO_TIMEDURATION or ::TIME_DURATION can be used to convert to its corresponding time span value for arithmetic operations like + and/or -.

POST /_query
{
   "query": """
   FROM employees
   | EVAL x = hire_date + ?timespan::DATE_PERIOD, y = hire_date - TO_DATEPERIOD(?timespan)
   """,
   "params": [{"timespan" : "1 day"}]
}

When a time span is provided as a named parameter in string format, it can be automatically converted to its corresponding time span value in grouping functions and scalar functions, like BUCKET and DATE_TRUNC.

POST /_query
{
   "query": """
   FROM employees
   | WHERE hire_date >= "1985-01-01T00:00:00Z" AND hire_date < "1986-01-01T00:00:00Z"
   | STATS hires_per_week = COUNT(*) BY week = BUCKET(hire_date, ?timespan)
   | SORT week
   """,
   "params": [{"timespan" : "1 week"}]
}

POST /_query
{
   "query": """
   FROM employees
   | KEEP first_name, last_name, hire_date
   | EVAL year_hired = DATE_TRUNC(?timespan, hire_date)
   """,
   "params": [{"timespan" : "1 year"}]
}

Supported temporal units [esql-time-spans-table]