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

**URL:** <https://discuss.elastic.co/t/formatted-time-string-with-nanoseconds-is-not-converted-to-nanosecond-timestamp-value-when-sorting-on-search-queries/330046>\
**Category:** Elasticsearch\
**Tags:** runtime-fields\
**Created:** [April 14, 2023, 9:54pm UTC](https://discuss.elastic.co/t/formatted-time-string-with-nanoseconds-is-not-converted-to-nanosecond-timestamp-value-when-sorting-on-search-queries/330046 "2023-04-14T21:54:26Z")\
**Posts on this page:** 7\
**Page:** 1

<div class="post-metadata">

**Author:** ![jsun-m](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/jsun-m/32/116000_2.png) [@jsun-m](https://discuss.elastic.co/u/jsun-m)\
**Post date:** [April 14, 2023, 9:54pm UTC](https://discuss.elastic.co/t/formatted-time-string-with-nanoseconds-is-not-converted-to-nanosecond-timestamp-value-when-sorting-on-search-queries/330046/1 "2023-04-14T21:54:26Z")

</div>

Query:

```auto
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:

```auto
 "_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:

```auto
 "_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

---

<div class="post-metadata">

**Author:** ![jsun-m](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/jsun-m/32/116000_2.png) [@jsun-m](https://discuss.elastic.co/u/jsun-m)\
**Post date:** [April 17, 2023, 3:41pm UTC](https://discuss.elastic.co/t/formatted-time-string-with-nanoseconds-is-not-converted-to-nanosecond-timestamp-value-when-sorting-on-search-queries/330046/2 "2023-04-17T15:41:52Z")

</div>

bump

---

<div class="post-metadata">

**Author:** ![dadoonet](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/dadoonet/32/137187_2.png) [@dadoonet](https://discuss.elastic.co/u/dadoonet)\
**Post date:** [April 17, 2023, 4:00pm UTC](https://discuss.elastic.co/t/formatted-time-string-with-nanoseconds-is-not-converted-to-nanosecond-timestamp-value-when-sorting-on-search-queries/330046/3 "2023-04-17T16:00:29Z")

</div>

I'm wondering if you are hitting this:

> <https://github.com/elastic/elasticsearch/issues/70085>
>
> We allow folks to specify dates like:
> \`\`\`
> "d": 6123.123
> \`\`\`
> 
> Which you shou…ld read as \`6123123000\` nanoseconds since epoch. There are a lot of layers, but when we see this format we pretty much shift the decimal point six places to the right and parse the whole thing as nanoseconds. Then we use the standard java time utilities to convert that nanosecond precision instant into whatever the date's native format is. This all works fine on parsing.
> 
> But in a few places we let jackson deserialize the \`\_source\` without type hints. When it sees a number like \`6123.123\` it automatically converts it to a double precision floating point value. This will lose precision for big dates. With \`date\` its \*mostly\* ok because double's maximum precise value is 9,007,199,254,740,992 which is something like the year 287396. If I live so long I'll be glad to fix the bugs. I say \*mostly\* here because there are some issues with rounding so we might end up being off by a millisecond. Sadly, with \`date\_nanos\` we lose precision on any date after 6am, April 15th, 1970 UTC.  
> 
> So, you rightly ask, when do we let jackson deserialize the \`\_source\`? Well, when scripts access \`\_source\` via \`params.\_source\`. And when we perform an \`\_update\` action. And in the \`fields\` API. And maybe other places I haven't found yet.
> 
> Note: This describes the state we'll be in if we merge #70040.

---

<div class="post-metadata">

**Author:** ![jsun-m](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/jsun-m/32/116000_2.png) [@jsun-m](https://discuss.elastic.co/u/jsun-m)\
**Post date:** [April 17, 2023, 5:06pm UTC](https://discuss.elastic.co/t/formatted-time-string-with-nanoseconds-is-not-converted-to-nanosecond-timestamp-value-when-sorting-on-search-queries/330046/4 "2023-04-17T17:06:28Z")

</div>

> <https://stackoverflow.com/questions/49350065/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?

---

<div class="post-metadata">

**Author:** ![jsun-m](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/jsun-m/32/116000_2.png) [@jsun-m](https://discuss.elastic.co/u/jsun-m)\
**Post date:** [April 19, 2023, 4:27pm UTC](https://discuss.elastic.co/t/formatted-time-string-with-nanoseconds-is-not-converted-to-nanosecond-timestamp-value-when-sorting-on-search-queries/330046/5 "2023-04-19T16:27:48Z")

</div>

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`

---

<div class="post-metadata">

**Author:** ![jughosta](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/jughosta/32/107160_2.png) [@jughosta](https://discuss.elastic.co/u/jughosta)\
**Post date:** [May 8, 2023, 12:04pm UTC](https://discuss.elastic.co/t/formatted-time-string-with-nanoseconds-is-not-converted-to-nanosecond-timestamp-value-when-sorting-on-search-queries/330046/6 "2023-05-08T12:04:27Z")

</div>

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](https://www.elastic.co/guide/en/elasticsearch/reference/current/paginate-search-results.html)

 ![Screenshot 2023-05-08 at 14.03.06](https://us1.discourse-cdn.com/elastic/original/3X/4/d/4dae2f349f282402c429ab34b49f62942b46e1df.png)

Example:

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

```

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [June 5, 2023, 12:05pm UTC](https://discuss.elastic.co/t/formatted-time-string-with-nanoseconds-is-not-converted-to-nanosecond-timestamp-value-when-sorting-on-search-queries/330046/7 "2023-06-05T12:05:21Z")

</div>

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.