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

[aqiank]

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!

[dadoonet (David Pilato)]

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

[aqiank]

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

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

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

[dadoonet (David Pilato)]

Exact. As the sort Is applied in order.