Formatted time string with nanoseconds is not converted to nanosecond timestamp value when sorting on _search queries

[jsun-m]

Query:

return {
        "sort": [
            {
                "time": "desc"
            },
        ],
        "_source": ["@timestamp", "message", "time"],
        "runtime_mappings": {
            "date_has_nanos": {
                "type": "boolean",
                "script": "emit(doc['time'].value.nano != 0)" 
            }
        },
        "fields": [
            {
                "field": "time",
                "format": "strict_date_optional_time_nanos" 
            },
            {
                "field": "date_has_nanos"
            }   
        ]
    }

Expectation per entry:

 "_score": null,
        "_source": {
            "@timestamp": "2023-04-14T20:33:36.831027500Z",
            "message": "MESSAGE"
        },
        "fields": {
            "@timestamp": [
                "2023-04-14T20:33:36.831027500Z"
            ],
            "date_has_nanos": [
                true
            ]
        },
        "sort": [
            1681504416831027500
        ]

Actual entry:

 "_score": null,
        "_source": {
            "@timestamp": "2023-04-14T20:33:36.831027500Z",
            "message": "MESSAGE"
        },
        "fields": {
            "@timestamp": [
                "2023-04-14T20:33:36.831Z"
            ],
            "date_has_nanos": [
                true
            ]
        },
        "sort": [
            1681504416831
        ]

I'm getting an issue where the @timestamps loses precision when it is formatted by strict_date_optional_time_nanos which leads the precision to be dumb down to milliseconds

[jsun-m]

bump

[dadoonet (David Pilato)]

I'm wondering if you are hitting this:

Fields API can lose precision when fetching dates

[jsun-m]

Insert date as epoch_seconds, output as formatted date

I'm looking to try this epoch_seconds into my query but it also mentions that putting date in _source is not a good idea and instead put it as a field. Is there a way to convert this whenever i insert a log into Elasticsearch or update fluent bit to insert a date field automatically?

[jsun-m]

after some modifications I am able to get my sort to have the value as nanoseconds but it still doesnt convert correctly

it shows it as 1681504416831000000 which totally excludes precision from 2023-04-14T20:33:36.831Z

[jughosta (Julia Rechkunova)]

Hi @jsun-m,

Could you please try adding numeric_type parameter as it's mentioned in the docs Paginate search results | Elasticsearch Guide [8.7] | Elastic

Example:

"sort": [ 
    {
       "@timestamp": {
          "order": "asc", 
          "format": "strict_date_optional_time_nanos", 
          "numeric_type" : "date_nanos" 
       }
     }
  ]