# Negative key value for date\_histogram in epoch\_milli

**URL:** <https://discuss.elastic.co/t/negative-key-value-for-date-histogram-in-epoch-milli/215953>\
**Category:** Elasticsearch\
**Created:** [January 21, 2020, 9:26pm UTC](https://discuss.elastic.co/t/negative-key-value-for-date-histogram-in-epoch-milli/215953 "2020-01-21T21:26:55Z")\
**Posts on this page:** 9\
**Page:** 1

<div class="post-metadata">

**Author:** ![jz1603](https://avatars.discourse-cdn.com/v4/letter/j/c57346/32.png) [@jz1603](https://discuss.elastic.co/u/jz1603)\
**Post date:** [January 21, 2020, 9:26pm UTC](https://discuss.elastic.co/t/negative-key-value-for-date-histogram-in-epoch-milli/215953/1 "2020-01-21T21:26:55Z")

</div>

Hi ES Dev Team,

One of the breaking changes in version 7.0 documentation says that negative epoch timestamps are no longer supported.

In our application, we use negative epoch timestamps as a lot of our documents have dates earlier than 1970. We use the date\_histogram aggregation on these dates, and the "key" field in the response from date\_histogram aggregation is formatted in epoch\_milli. The response keys still come in negative epoch timestamps. Does that mean it is still supported?

Will the key format be changed down the road as well? What would be your suggestion for us moving forward?

Thanks in advance,  
Jenna

---

<div class="post-metadata">

**Author:** ![jz1603](https://avatars.discourse-cdn.com/v4/letter/j/c57346/32.png) [@jz1603](https://discuss.elastic.co/u/jz1603)\
**Post date:** [January 24, 2020, 7:41pm UTC](https://discuss.elastic.co/t/negative-key-value-for-date-histogram-in-epoch-milli/215953/2 "2020-01-24T19:41:41Z")

</div>

Hi can someone help me with this

---

<div class="post-metadata">

**Author:** ![spinscale](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/spinscale/32/25011_2.png) [@spinscale](https://discuss.elastic.co/u/spinscale)\
**Post date:** [January 27, 2020, 9:41am UTC](https://discuss.elastic.co/t/negative-key-value-for-date-histogram-in-epoch-milli/215953/3 "2020-01-27T09:41:44Z")

</div>

Hey,

this statement IIRC refers to negative epoch timestamps when indexing documents and the field is configured as a `type: date` with a mapping of `epoch_*`. This is not about date histogram aggregation.

So, how do you index your data?

--Alex

---

<div class="post-metadata">

**Author:** ![jz1603](https://avatars.discourse-cdn.com/v4/letter/j/c57346/32.png) [@jz1603](https://discuss.elastic.co/u/jz1603)\
**Post date:** [January 28, 2020, 10:44pm UTC](https://discuss.elastic.co/t/negative-key-value-for-date-histogram-in-epoch-milli/215953/4 "2020-01-28T22:44:27Z")

</div>

Hi Alex,

Thanks for looking into this!

From my understanding, you are saying that negative epoch timestamps is only invalid in index time, but still remains supported and valid at query time. Is that correct?

Our mapping for the date looks something like:

> PUT test-date/\_mapping  
> {  
> "properties": {  
> "sort\_date": {  
> "type": "date",  
> "format": "yyyy-MM-dd G || yyyy-MM-dd || yyyy-MM || Y G || Y || date\_time\_no\_millis || date\_hour\_minute\_second || epoch\_millis"  
> }  
> }  
> }

And in this case, we have run into another issue with epoch\_millis. When using the range query, the epoch\_millis format does not recognize any epoch\_millis values with its last 3 digits being anything other than '000'.

To replicate this, using the mapping posted above and put these docs:

```
PUT test-date/_doc/1
{
  "sort_date": "0001-12-31 AD"
}
PUT test-date/_doc/2
{
  "sort_date": "0005-03-12 AD"
}
PUT test-date/_doc/3
{
  "sort_date": "0005-03-12 AD"
}

```

Then run the following query:

> GET test-date/\_search  
> {  
> "query": {  
> "bool": {  
> "must": {  
> "query\_string": {  
> "default\_operator": "AND",  
> "query": "\*"  
> }  
> },  
> "filter": {  
> "bool": {  
> "must": [  
> {  
> "range": {  
> "sort\_date": {  
> "gte": "-62559940430768",  
> "lte": "-59492208738460",  
> "format": "epoch\_millis"  
> }  
> }  
> }  
> ]  
> }  
> }  
> }  
> }  
> }

We then receive an error saying  
failed to parse date field [-62559940430768] with format [epoch\_millis]: [failed to parse date field [-62559940430768] with format [epoch\_millis]]

However, if I change the last 3 digits of the above numbers to 000, the query works and return results successfully.

Any ideas or suggestions?

Thanks in advance!  
Jenna

---

<div class="post-metadata">

**Author:** ![spinscale](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/spinscale/32/25011_2.png) [@spinscale](https://discuss.elastic.co/u/spinscale)\
**Post date:** [January 29, 2020, 2:56pm UTC](https://discuss.elastic.co/t/negative-key-value-for-date-histogram-in-epoch-milli/215953/5 "2020-01-29T14:56:18Z")

</div>

ah, now I understand your use case, thanks for providing the examples.

Judging from [https://github.com/elastic/elasticsearch/blob/master/server/src/main/java/org/elasticsearch/common/time/EpochTime.java](https://github.com/elastic/elasticsearch/blob/master/server/src/main/java/org/elasticsearch/common/time/EpochTime.java) the parsing of negative epoch\_millis timestamp should fail. As only positive numbers are allowed.

Can you provide a reproducible example including indexing/mapping to dig deeper?

---

<div class="post-metadata">

**Author:** ![jz1603](https://avatars.discourse-cdn.com/v4/letter/j/c57346/32.png) [@jz1603](https://discuss.elastic.co/u/jz1603)\
**Post date:** [February 4, 2020, 9:11pm UTC](https://discuss.elastic.co/t/negative-key-value-for-date-histogram-in-epoch-milli/215953/6 "2020-02-04T21:11:01Z")

</div>

Hi Alex,

I have included a simplified mapping and reproducible docs and queries in my previous response above. Is there anything else I can provide you with?

Thanks,  
Jenna

---

<div class="post-metadata">

**Author:** ![spinscale](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/spinscale/32/25011_2.png) [@spinscale](https://discuss.elastic.co/u/spinscale)\
**Post date:** [February 5, 2020, 9:23am UTC](https://discuss.elastic.co/t/negative-key-value-for-date-histogram-in-epoch-milli/215953/7 "2020-02-05T09:23:29Z")

</div>

Thanks for updating the snippet:

```auto
"range": {
  "sort_date": {
    "gte": "-62559940430768",
    "lte": "-59492208738460",
    "format": "epoch_millis"
  }
}

```

this is a negative epoch date and thus the exception is thrown. Replace this with a UTC8601 formatted date...

--Alex

---

<div class="post-metadata">

**Author:** ![jz1603](https://avatars.discourse-cdn.com/v4/letter/j/c57346/32.png) [@jz1603](https://discuss.elastic.co/u/jz1603)\
**Post date:** [February 5, 2020, 7:22pm UTC](https://discuss.elastic.co/t/negative-key-value-for-date-histogram-in-epoch-milli/215953/8 "2020-02-05T19:22:04Z")

</div>

However this query returns successfully if I change the last 3 digits of the timestamps to 000:

```
"range": {
  "sort_date": {
    "gte": "-62559940430000",
    "lte": "-59492208738000",
    "format": "epoch_millis"
  }
}

```

Any reason for this inconsistency?

Thanks,  
Jenna

---

<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:** [March 4, 2020, 7:22pm UTC](https://discuss.elastic.co/t/negative-key-value-for-date-histogram-in-epoch-milli/215953/9 "2020-03-04T19:22:16Z")

</div>

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