[ES|QL] Support date_nanos field type

[wchaparro]

We want to support this field type in ES|QL, ( for filtering / sorting etc) . Not intending to support aggregations on date_nanos (current aggregations truncate the date_nanos to millisecond resolution before aggregating.

Tasks before we can remove the feature flag

Beta Give feedback

1. [ESQL] Load and Return Date Nanos Values #109987
   :Analytics/ES|QL >enhancement Team:Analytics +3
   
2. [ESQL] Support basic casting for date nanos #109990
   :Analytics/ES|QL >enhancement Team:Analytics +3
   
3. [ESQL] Support date nanos on functions that take any type #109998
   :Analytics/ES|QL >enhancement Team:Analytics +3
   
4. [ESQL] Support date nanos in binary comparisions #109992
   :Analytics/ES|QL >enhancement Team:Analytics +3
   
5. [ESQL] create TO_DATE_NANOS function #111842
   :Analytics/ES|QL >enhancement Team:Analytics +3
   
6. [ESQL] Support Max & Min aggregation on Date Nanos #110002
   :Analytics/ES|QL >enhancement Team:Analytics +3
   
7. [ESQL] Support Count & Count Distinct aggregations on date nanos #110003
   :Analytics/ES|QL >enhancement Team:Analytics +3
   
8. [ESQL] Support Values aggregation on date nanos #110005
   :Analytics/ES|QL >enhancement Team:Analytics +3
   
9. [ESQL] Sort results by a date nanos field #109988
   :Analytics/ES|QL >enhancement Team:Analytics +3
10. [ESQL] Testing for date nanos union type #112885
    :Analytics/ES|QL >test Team:Analytics +3

Additional work we may or may not need

Beta Give feedback

1. [ESQL] Date Trunc function should support date nanos #110008
   :Analytics/ES|QL >enhancement Team:Analytics team-discuss +4
2. [ESQL] Support addition and subtraction of date nanos #109995
   :Analytics/ES|QL >enhancement Team:Analytics +3
3. [ESQL] Support passing Date_Nanos to Date_Format function #109994
   :Analytics/ES|QL >enhancement Team:Analytics +3
4. [ESQL] Add a version of DATE_PARSE that returns date nanos #109997
   :Analytics/ES|QL >enhancement Team:Analytics +3
5. [ESQL] Support Date Diff function on date nanos #109999
   :Analytics/ES|QL >enhancement Team:Analytics +3
6. [ESQL] Support Date Extract function on date nanos #110000
   :Analytics/ES|QL >enhancement Team:Analytics +3
7. [ESQL] Extend range of supported timespan literals #111741
   :Analytics/ES|QL >enhancement Team:Analytics +3
8. [ESQL] Support autocasting to resolve types between nanosecond dates and millisecond dates #110009
   :Analytics/ES|QL >enhancement Team:Analytics team-discuss +4
9. [ESQL] Support date nanos on Bucket function
10. [ESQL] Union type that accepts date millis and date nanos
11. add ::millis and ::nanos as aliases for casting

[elasticsearchmachine]

Pinging @elastic/es-analytical-engine (Team:Analytics)

[ruflin]

Support for date_nanos type is critical as we eventually plan to move over to data_nanos for all logs data ( #102548) and quite a few users already use it as their default today. One of the key features is sorting. These users have more then 1 log event per millisecond and sorting by @timestamp must return the correct order where this events were created.

[ninoslavmiskovic]

@wchaparro Could you clarify this

"Not intending to support aggregations"

This could be a blocker for us since it would mean that we can not create a histogram in Discover.

If we do as current aggregations truncate the date_nano to millisecond resolution before aggregating, then we would be able to e.g. show the histogram like today in non-ES|QL environments.

cc @stratoula , @kertal , @davismcphee

[stratoula]

This could be a blocker for us since it would mean that we can not create a histogram in Discover

What do you mean Nino?

[ninoslavmiskovic]

@stratoula I talked to @kertal earlier today, and if we support the field type: date_nano but do not support the ability to do aggregations (STATS), then it would be impossible to build a histogram right?

[stratoula]

The histogram is built using a count aggregation and the @timestamp field. I can't think why not supporting aggs in the date_nano field types will break it or anything. But maybe @kertal you can explain what I am missing here.

[kertal]

@stratoula so when the @timestamp field is of type date_nanos (which is planned for logs data), it would work out of the box even if in theory date_nanos wouldn't support aggregations (which I assume it will but just with millisecond resolution)

[stratoula]

@nik9000 if the @timestamp is date_nanos

this will work,right?

from kibana_sample_data_logs | limit 10 | EVAL timestamp=DATE_TRUNC(30 second, @timestamp) | stats results = count(*) by timestamp | rename timestamp as `@timestamp every 30 second`

[not-napoleon]

@stratoula I'm picking this up now that I'm back from PTO. I can tell you for a fact that the query you pasted doesn't work right now with date nanos, nor would I expect it to. Supporting that type of thing is exactly what we're talking about here.

I've done some task breakdown for this already, and I'm going to flesh out this ticket (and create some sub-tickets) today.

[stratoula]

Oh good to know. So date_trunc doesn't work with date nanos? Thanx for looking into this!

[elasticsearchmachine]

Pinging @elastic/kibana-esql (ES|QL-ui)

[nik9000]

Right now nothing works with date_nanos. Job 1 is loading them and returning them over the wire. Job 2 is plugging functions into them. After that there's a bunch of functions to look at, including aggs. Stuff like TO_STRING and TO_LONG and DATE_TRUNC and BUCKET and MAX and topn and I dunno, there are like 30 things that'll need touching.

[felixbarny]

This is also relevant for the OTel program as we're planning to map @timestamp to date_nanos for all OTel data streams. When do we expect this to be released? I'm wondering if we should start with date and change to date_nanos later.

[nik9000]

This is also relevant for the OTel program as we're planning to map @timestamp to date_nanos for all OTel data streams. When do we expect this to be released? I'm wondering if we should start with date and change to date_nanos later.

You know we don't give dates. We're working on it now though.