ES|QL date-time functions

Serverless Stack

ES|QL supports these date-time functions:

• DATE_DIFF
• DATE_EXTRACT
• DATE_FORMAT
• DATE_PARSE
• DATE_TRUNC
• DAY_NAME
• MONTH_NAME
• NOW
• TRANGE

Syntax

×

Parameters

unit

Time difference unit

startTimestamp

A string representing a start timestamp

endTimestamp

A string representing an end timestamp

Description

Subtracts the startTimestamp from the endTimestamp and returns the difference in multiples of unit. If startTimestamp is later than the endTimestamp, negative values are returned.

Datetime difference units

Note that while there is an overlap between the function’s supported units and ES|QL’s supported time span literals, these sets are distinct and not interchangeable. Similarly, the supported abbreviations are conveniently shared with implementations of this function in other established products and not necessarily common with the date-time nomenclature used by Elasticsearch.

Supported types

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)
		

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)
		

Syntax

×

Parameters

datePart

Part of the date to extract. Can be: aligned_day_of_week_in_month, aligned_day_of_week_in_year, aligned_week_of_month, aligned_week_of_year, ampm_of_day, clock_hour_of_ampm, clock_hour_of_day, day_of_month, day_of_week, day_of_year, epoch_day, era, hour_of_ampm, hour_of_day, instant_seconds, micro_of_day, micro_of_second, milli_of_day, milli_of_second, minute_of_day, minute_of_hour, month_of_year, nano_of_day, nano_of_second, offset_seconds, proleptic_month, second_of_day, second_of_minute, year, or year_of_era. Refer to java.time.temporal.ChronoField for a description of these values. If null, the function returns null.

date

Date expression. If null, the function returns null.

Description

Extracts parts of a date, like year, month, day, hour.

Supported types

Examples

ROW date = DATE_PARSE("yyyy-MM-dd", "2022-05-06")
| EVAL year = DATE_EXTRACT("year", date)
		

Find all events that occurred outside of business hours (before 9 AM or after 5PM), on any given date:

FROM sample_data
| WHERE DATE_EXTRACT("hour_of_day", @timestamp) < 9
    AND DATE_EXTRACT("hour_of_day", @timestamp) >= 17
		

Syntax

×

Parameters

dateFormat

Date format (optional). If no format is specified, the yyyy-MM-dd'T'HH:mm:ss.SSSZ format is used. If null, the function returns null.

date

Date expression. If null, the function returns null.

Description

Returns a string representation of a date, in the provided format.

Supported types

Example

FROM employees
| KEEP first_name, last_name, hire_date
| EVAL hired = DATE_FORMAT("yyyy-MM-dd", hire_date)
		

Syntax

×

Parameters

datePattern

The date format. Refer to the DateTimeFormatter documentation for the syntax. If null, the function returns null.

dateString

Date expression as a string. If null or an empty string, the function returns null.

options

(Optional) Additional options for date parsing, specifying time zone and locale as function named parameters.

Description

Returns a date by parsing the second argument using the format specified in the first argument.

Supported types

Supported function named parameters

time_zone

(keyword) Coordinated Universal Time (UTC) offset or IANA time zone used to convert date values in the query string to UTC.

locale

(keyword) The locale to use when parsing the date, relevant when parsing month names or week days.

Example

ROW date_string = "2022-05-06"
| EVAL date = DATE_PARSE("yyyy-MM-dd", date_string)
		

Syntax

×

Parameters

interval

Interval; expressed using the timespan literal syntax.

date

Date expression

Description

Rounds down a date to the closest interval since epoch, which starts at 0001-01-01T00:00:00Z.

Supported types

Examples

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

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
		

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
		

Stack 9.2.0

Syntax

×

Parameters

date

Date expression. If null, the function returns null.

Description

Returns the name of the weekday for date based on the configured Locale.

Supported types

Example

ROW dt = to_datetime("1953-09-02T00:00:00.000Z")
| EVAL weekday = DAY_NAME(dt);
		

Stack 9.2.0

Syntax

×

Parameters

date

Date expression. If null, the function returns null.

Description

Returns the month name for the provided date based on the configured Locale.

Supported types

Example

ROW dt = to_datetime("1996-03-21T00:00:00.000Z")
| EVAL monthName = MONTH_NAME(dt);
		

Syntax

×

Parameters

Description

Returns current date and time.

Supported types

Examples

ROW current_date = NOW()
		

To retrieve logs from the last hour:

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

Syntax

×

Parameters

start_time_or_offset

Offset from NOW for the single parameter mode. Start time for two parameter mode. In two parameter mode, the start time value can be a date string, date, date_nanos or epoch milliseconds.

end_time

Explicit end time that can be a date string, date, date_nanos or epoch milliseconds.

Description

Filters data for the given time range using the @timestamp attribute.

Supported types

Examples

FROM k8s
| WHERE TRANGE(1h)
| KEEP @timestamp
		

FROM k8s
| WHERE TRANGE("2024-05-10T00:17:14.000Z", "2024-05-10T00:18:33.000Z")
| SORT @timestamp
| KEEP @timestamp
| LIMIT 10
		

FROM k8s
| WHERE TRANGE(to_datetime("2024-05-10T00:17:14Z"), to_datetime("2024-05-10T00:18:33Z"))
| SORT @timestamp
| KEEP @timestamp
| LIMIT 10
		

FROM k8s
| WHERE TRANGE(to_datetime("2024-05-10T00:17:14.000Z"), to_datetime("2024-05-10T00:18:33.000Z"))
| SORT @timestamp
| KEEP @timestamp
| LIMIT 10
		

FROM k8s
| WHERE TRANGE(1715300236000, 1715300282000)
| SORT @timestamp
| KEEP @timestamp
| LIMIT 10