CODEWITHSUNDEEP
elasticsearch
zer0uno
zer0uno

Reputation: 8030

Convert timestamp to date for filtering

I'm pretty newbie to ElasticSearch and I'm not getting my way to translate the following MySQL query to its equivalent in ElasticSearch:

SELECT 
    * 
FROM 
    table 
WHERE 
    STR_TO_DATE(FROM_UNIXTIME(field1, '%d/%m/%Y'), '%d/%m/%Y') = IF(
        field2 LIKE '%/%/%', 
        STR_TO_DATE(field2, '%d/%m/%Y'), 
        STR_TO_DATE(FROM_UNIXTIME(field2, '%d/%m/%Y'), '%d/%m/%Y')
    )

Is it possibile to do something like this in ElasticSearch? If possible, how?

Upvotes: 0

Views: 1222

Answers (1)

Eirini Graonidou
Eirini Graonidou

Reputation: 1566

In elasticsearch you could pre-define in the mappings the format of the date field. An example with a date definition with format follows: 

PUT my_index
{
 "mappings": {
  "_doc": {
    "properties": {
       "date": {
          "type": "date",
          "format": "yyyy-MM-dd"
        }
     }
   }
 }
}

If you could use this mapping, you would only need to query like:

POST /my_index/_search
{ 
   "query" : {
       "term": {"field1": "field2"}
   }       


}

If not, you could use scripts to format the date as you wish.
Note: I post here an example, as a way to go:

POST my_index/_search
{
 "query": {
    "bool" : {
        "must" : {
            "script" : {
                "script" : {
                    "source": "def formatter = DateTimeFormatter.ofPattern("dd-MM-yyyy"); formatter.format(doc['field1'].value) == formatter.format(doc['field2'].value",
                    "lang": "painless"
                 }
            }
        }
    }
}

Here is the Painless reference for the DateTimeFormatter

Upvotes: 1

Related Questions