# How to merge result when sorting by two different date formats (datetime and unix timestamp)

**URL:** <https://discuss.elastic.co/t/how-to-merge-result-when-sorting-by-two-different-date-formats-datetime-and-unix-timestamp/212253>\
**Category:** Elasticsearch\
**Created:** [December 18, 2019, 3:47am UTC](https://discuss.elastic.co/t/how-to-merge-result-when-sorting-by-two-different-date-formats-datetime-and-unix-timestamp/212253 "2019-12-18T03:47:52Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![aqiank](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/aqiank/32/39723_2.png) [@aqiank](https://discuss.elastic.co/u/aqiank)\
**Post date:** [December 18, 2019, 3:47am UTC](https://discuss.elastic.co/t/how-to-merge-result-when-sorting-by-two-different-date-formats-datetime-and-unix-timestamp/212253/1 "2019-12-18T03:47:52Z")

</div>

Hi, I have two indices which have different formats for creation date (one is YYYY-MM-DDThh:mm:ss and the other is seconds since epoch). When I sort in descending order using the two fields, the returned documents are always from the first index followed by documents from the second index even though the second index may contain documents newer than the first index.

What I want is for Elasticsearch to look at both datetime formats the same way and therefore the results will be merged. Is it possible to do that? Thanks!

---

<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:** [December 18, 2019, 5:55am UTC](https://discuss.elastic.co/t/how-to-merge-result-when-sorting-by-two-different-date-formats-datetime-and-unix-timestamp/212253/2 "2019-12-18T05:55:14Z")

</div>

It should work if both indices are using a date type I think.

---

<div class="post-metadata">

**Author:** ![aqiank](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/aqiank/32/39723_2.png) [@aqiank](https://discuss.elastic.co/u/aqiank)\
**Post date:** [December 18, 2019, 6:35am UTC](https://discuss.elastic.co/t/how-to-merge-result-when-sorting-by-two-different-date-formats-datetime-and-unix-timestamp/212253/3 "2019-12-18T06:35:04Z")

</div>

Ohh right, sorry. I forgot to include an important detail that they are named differently which I suppose is the root cause. One is "timestamp" and the other is "created\_at". So I have to put two items in the Sort DSL such as

```json
        "sort": {
                "timestamp": {
                        "order": "desc"
                },
                "created_at": {
                        "order": "desc"
                }
        },

```

which I guess is what causes the results to be divided..

---

<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:** [December 18, 2019, 8:05am UTC](https://discuss.elastic.co/t/how-to-merge-result-when-sorting-by-two-different-date-formats-datetime-and-unix-timestamp/212253/4 "2019-12-18T08:05:59Z")

</div>

Exact. As the `sort` Is applied in order.

---

<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:** [January 15, 2020, 8:06am UTC](https://discuss.elastic.co/t/how-to-merge-result-when-sorting-by-two-different-date-formats-datetime-and-unix-timestamp/212253/5 "2020-01-15T08:06:00Z")

</div>

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