# How to add date format in \_delete\_by\_query

**URL:** <https://discuss.elastic.co/t/how-to-add-date-format-in-delete-by-query/254600>\
**Category:** Elasticsearch\
**Created:** [November 7, 2020, 10:17am UTC](https://discuss.elastic.co/t/how-to-add-date-format-in-delete-by-query/254600 "2020-11-07T10:17:30Z")\
**Posts on this page:** 17\
**Page:** 1

<div class="post-metadata">

**Author:** ![tusharnemade](https://avatars.discourse-cdn.com/v4/letter/t/67e7ee/32.png) [@tusharnemade](https://discuss.elastic.co/u/tusharnemade)\
**Post date:** [November 7, 2020, 10:17am UTC](https://discuss.elastic.co/t/how-to-add-date-format-in-delete-by-query/254600/1 "2020-11-07T10:17:31Z")

</div>

Hello Team

Could you please help me how to add a format while performing operation of "\_delete\_by\_query" on Index were condition of deletion is based on

range --\> on a field having type as "epoch" and i can pass the date to this query is like "2020-11-07"

```auto
{
    "query": {
      "range": {
        "sysTimeStamp": { "lt": "2020-11-05" , "format": "yyyy-MM-dd" }
      }
    }
}
' 

```

Could you please help me

Thanks  
Tushar Nemade

---

<div class="post-metadata">

**Author:** ![tusharnemade](https://avatars.discourse-cdn.com/v4/letter/t/67e7ee/32.png) [@tusharnemade](https://discuss.elastic.co/u/tusharnemade)\
**Post date:** [November 7, 2020, 10:21am UTC](https://discuss.elastic.co/t/how-to-add-date-format-in-delete-by-query/254600/2 "2020-11-07T10:21:34Z")

</div>

I am getting error 🙂

```auto
          "type" : "query_shard_exception",
          "reason" : "failed to create query: For input string: \"2020-11-07\"",
          "index_uuid" : "WKmRbiRqSr2OhANmvD3yow",
          "index" : "test-1",
          "caused_by" : {
            "type" : "number_format_exception",
            "reason" : "For input string: \"2020-11-07\""

```

---

<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:** [November 7, 2020, 2:20pm UTC](https://discuss.elastic.co/t/how-to-add-date-format-in-delete-by-query/254600/3 "2020-11-07T14:20:21Z")

</div>

Could you provide a full recreation script as described in [About the Elasticsearch category](https://discuss.elastic.co/t/about-the-elasticsearch-category/21). It will help to better understand what you are doing. Please, try to keep the example as simple as possible.

A full reproduction script is something anyone can copy and paste in Kibana dev console, click on the run button to reproduce your use case. It will help readers to understand, reproduce and if needed fix your problem. It will also most likely help to get a faster answer.

---

<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:** [November 7, 2020, 2:20pm UTC](https://discuss.elastic.co/t/how-to-add-date-format-in-delete-by-query/254600/4 "2020-11-07T14:20:38Z")

</div>

May be share the mapping

---

<div class="post-metadata">

**Author:** ![Christian\_Dahlqvist](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/christian_dahlqvist/32/4617_2.png) [@Christian\_Dahlqvist](https://discuss.elastic.co/u/Christian_Dahlqvist)\
**Post date:** [November 7, 2020, 2:57pm UTC](https://discuss.elastic.co/t/how-to-add-date-format-in-delete-by-query/254600/5 "2020-11-07T14:57:03Z")

</div>

> [@tusharnemade](#):
>
> on a field having type as "epoch"

Are you referring to a timestamp field with EPOCH format? Which version of Elasticsearch are you using?

---

<div class="post-metadata">

**Author:** ![tusharnemade](https://avatars.discourse-cdn.com/v4/letter/t/67e7ee/32.png) [@tusharnemade](https://discuss.elastic.co/u/tusharnemade)\
**Post date:** [November 8, 2020, 11:23am UTC](https://discuss.elastic.co/t/how-to-add-date-format-in-delete-by-query/254600/7 "2020-11-08T11:23:04Z")

</div>

Hi David

Mapping of my Filed is as below :

```auto
        "sysTimeStamp" : {
          "type" : "date",
          "format" : "epoch_millis"
        },

```

---

<div class="post-metadata">

**Author:** ![tusharnemade](https://avatars.discourse-cdn.com/v4/letter/t/67e7ee/32.png) [@tusharnemade](https://discuss.elastic.co/u/tusharnemade)\
**Post date:** [November 8, 2020, 11:25am UTC](https://discuss.elastic.co/t/how-to-add-date-format-in-delete-by-query/254600/8 "2020-11-08T11:25:08Z")

</div>

Hi

I am trying to delete using below Command :

```auto
curl -X POST "localhost::9200/test-0001/_delete_by_query?pretty" -H 'Content-Type: application/json' -d'
{
    "query": {
      "range": {
        "sysTimeStamp": { "lt": "2020-11-05" , "format": "yyyy-MM-dd" }
      }
    }
}
'

```

---

<div class="post-metadata">

**Author:** ![tusharnemade](https://avatars.discourse-cdn.com/v4/letter/t/67e7ee/32.png) [@tusharnemade](https://discuss.elastic.co/u/tusharnemade)\
**Post date:** [November 8, 2020, 11:26am UTC](https://discuss.elastic.co/t/how-to-add-date-format-in-delete-by-query/254600/9 "2020-11-08T11:26:06Z")

</div>

Hi

Yes , My Filed is of EPOCH Format.

ElasticSearch Version 7.8.0

---

<div class="post-metadata">

**Author:** ![tusharnemade](https://avatars.discourse-cdn.com/v4/letter/t/67e7ee/32.png) [@tusharnemade](https://discuss.elastic.co/u/tusharnemade)\
**Post date:** [November 8, 2020, 11:26am UTC](https://discuss.elastic.co/t/how-to-add-date-format-in-delete-by-query/254600/10 "2020-11-08T11:26:41Z")

</div>

Please excuse me for my late response. Was busy with Deployments and stuff ....

---

<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:** [November 8, 2020, 11:42am UTC](https://discuss.elastic.co/t/how-to-add-date-format-in-delete-by-query/254600/11 "2020-11-08T11:42:17Z")

</div>

Could you share the full output of the following command?

```
GET YOURINDEXNAME/_mapping
```

---

<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:** [November 8, 2020, 11:42am UTC](https://discuss.elastic.co/t/how-to-add-date-format-in-delete-by-query/254600/12 "2020-11-08T11:42:51Z")

</div>

This is not a full reproduction script. Please read again the instructions I shared.

---

<div class="post-metadata">

**Author:** ![tusharnemade](https://avatars.discourse-cdn.com/v4/letter/t/67e7ee/32.png) [@tusharnemade](https://discuss.elastic.co/u/tusharnemade)\
**Post date:** [November 8, 2020, 12:00pm UTC](https://discuss.elastic.co/t/how-to-add-date-format-in-delete-by-query/254600/13 "2020-11-08T12:00:29Z")

</div>

Hi David

Yes , I did understand. I shared only the information which was relevant to my error / my query which is not working...

Here is my entire mapping of said index :

```auto
    "mappings" : {
      "properties" : {
        "accountId" : {
          "type" : "long"
        },
        "action" : {
          "type" : "text",
          "fields" : {
            "keyword" : {
              "type" : "keyword",
              "ignore_above" : 256
            }
          }
        },
        "clientDetails" : {
          "type" : "text",
          "fields" : {
            "keyword" : {
              "type" : "keyword",
              "ignore_above" : 256
            }
          }
        },
        "commentKey" : {
          "type" : "text",
          "fields" : {
            "keyword" : {
              "type" : "keyword",
              "ignore_above" : 256
            }
          }
        },
        "deviceId" : {
          "type" : "text",
          "fields" : {
            "keyword" : {
              "type" : "keyword",
              "ignore_above" : 256
            }
          }
        },
        "devicekey" : {
          "type" : "text",
          "fields" : {
            "keyword" : {
              "type" : "keyword",
              "ignore_above" : 256
            }
          }
        },
        "eventId" : {
          "type" : "text",
          "fields" : {
            "keyword" : {
              "type" : "keyword",
              "ignore_above" : 256
            }
          }
        },
        "eventName" : {
          "type" : "text",
          "fields" : {
            "keyword" : {
              "type" : "keyword",
              "ignore_above" : 256
            }
          },
          "fielddata" : true
        },
        "forwarderDetails" : {
          "type" : "text",
          "fields" : {
            "keyword" : {
              "type" : "keyword",
              "ignore_above" : 256
            }
          }
        },
        "initiatingScreen" : {
          "type" : "text",
          "fields" : {
            "keyword" : {
              "type" : "keyword",
              "ignore_above" : 256
            }
          }
        },
        "isComment" : {
          "type" : "boolean"
        },
        "latitude" : {
          "type" : "text",
          "fields" : {
            "keyword" : {
              "type" : "keyword",
              "ignore_above" : 256
            }
          }
        },
        "longitude" : {
          "type" : "text",
          "fields" : {
            "keyword" : {
              "type" : "keyword",
              "ignore_above" : 256
            }
          }
        },
        "objectAudios" : {
          "type" : "text",
          "fields" : {
            "keyword" : {
              "type" : "keyword",
              "ignore_above" : 256
            }
          }
        },
        "objectCategs" : {
          "type" : "text",
          "fields" : {
            "keyword" : {
              "type" : "keyword",
              "ignore_above" : 256
            }
          }
        },
        "objectDescription" : {
          "type" : "text",
          "fields" : {
            "keyword" : {
              "type" : "keyword",
              "ignore_above" : 256
            }
          }
        },
        "objectEffects" : {
          "type" : "text",
          "fields" : {
            "keyword" : {
              "type" : "keyword",
              "ignore_above" : 256
            }
          }
        },
        "objectFilters" : {
          "type" : "text",
          "fields" : {
            "keyword" : {
              "type" : "keyword",
              "ignore_above" : 256
            }
          }
        },
        "objectHashtags" : {
          "type" : "text",
          "fields" : {
            "keyword" : {
              "type" : "keyword",
              "ignore_above" : 256
            }
          },
          "fielddata" : true
        },
        "objectKey" : {
          "type" : "text",
          "fields" : {
            "keyword" : {
              "type" : "keyword",
              "ignore_above" : 256
            }
          }
        },
        "objectLangs" : {
          "type" : "text",
          "fields" : {
            "keyword" : {
              "type" : "keyword",
              "ignore_above" : 256
            }
          }
        },
        "objectName" : {
          "type" : "text",
          "fields" : {
            "keyword" : {
              "type" : "keyword",
              "ignore_above" : 256
            }
          }
        },
        "ownerId" : {
          "type" : "text",
          "fields" : {
            "keyword" : {
              "type" : "keyword",
              "ignore_above" : 256
            }
          }
        },
        "playDuration" : {
          "type" : "long"
        },
        "reactionType" : {
          "type" : "text",
          "fields" : {
            "keyword" : {
              "type" : "keyword",
              "ignore_above" : 256
            }
          }
        },
        "referrerAudio" : {
          "type" : "text",
          "fielddata" : true
        },
        "referrerCategory" : {
          "type" : "text",
          "fields" : {
            "keyword" : {
              "type" : "keyword",
              "ignore_above" : 256
            }
          },
          "fielddata" : true
        },
        "replay" : {
          "type" : "boolean"
        },
        "sessionId" : {
          "type" : "text",
          "fields" : {
            "keyword" : {
              "type" : "keyword",
              "ignore_above" : 256
            }
          }
        },
        "shardKey" : {
          "type" : "text",
          "fields" : {
            "keyword" : {
              "type" : "keyword",
              "ignore_above" : 256
            }
          }
        },
        "shareType" : {
          "type" : "text",
          "fields" : {
            "keyword" : {
              "type" : "keyword",
              "ignore_above" : 256
            }
          }
        },
        "sysTimeStamp" : {
          "type" : "date",
          "format" : "epoch_millis"
        },
        "timeStamp" : {
          "type" : "date",
          "format" : "epoch_millis"
        },
        "timestamp" : {
          "type" : "long"
        },
        "totalDuration" : {
          "type" : "long"
        },
        "userAgent" : {
          "type" : "text",
          "fields" : {
            "keyword" : {
              "type" : "keyword",
              "ignore_above" : 256
            }
          }
        },
        "userId" : {
          "type" : "text",
          "fields" : {
            "keyword" : {
              "type" : "keyword",
              "ignore_above" : 256
            }
          }
        }
      }
    }
  }

```

---

<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:** [November 9, 2020, 9:16am UTC](https://discuss.elastic.co/t/how-to-add-date-format-in-delete-by-query/254600/14 "2020-11-09T09:16:48Z")

</div>

I'm not able to reproduce your problem.

Here is the kind of script I was asking for. If you can provide a similar one which reproduces your issue, please share it. Also use latest elasticsearch version. I don't think you shared the version. May be you are using an old one?

```auto
DELETE test 
PUT test
{
  "mappings": {
    "properties": {
      "date": {
        "type": "date",
        "format": "epoch_millis"
      }
    }
  }
}
POST test/_doc
{
  "date": "1604913154000"
}
GET test/_search
{
  "query": {
    "range": {
      "date": {
        "gte": "01/01/2020",
        "format": "dd/MM/yyyy"
      }
    }
  }
}
POST /test/_delete_by_query?pretty
{
  "query": {
    "range": {
      "date": {
        "gte": "01/01/2020",
        "format": "dd/MM/yyyy"
      }
    }
  }
}
GET /test/_search

```

---

<div class="post-metadata">

**Author:** ![tusharnemade](https://avatars.discourse-cdn.com/v4/letter/t/67e7ee/32.png) [@tusharnemade](https://discuss.elastic.co/u/tusharnemade)\
**Post date:** [November 9, 2020, 9:26am UTC](https://discuss.elastic.co/t/how-to-add-date-format-in-delete-by-query/254600/15 "2020-11-09T09:26:47Z")

</div>

I did shared the version above ..

---

<div class="post-metadata">

**Author:** ![tusharnemade](https://avatars.discourse-cdn.com/v4/letter/t/67e7ee/32.png) [@tusharnemade](https://discuss.elastic.co/u/tusharnemade)\
**Post date:** [November 9, 2020, 9:28am UTC](https://discuss.elastic.co/t/how-to-add-date-format-in-delete-by-query/254600/16 "2020-11-09T09:28:12Z")

</div>

Also I will share my demo in here ... which is causing the error...

---

<div class="post-metadata">

**Author:** ![tusharnemade](https://avatars.discourse-cdn.com/v4/letter/t/67e7ee/32.png) [@tusharnemade](https://discuss.elastic.co/u/tusharnemade)\
**Post date:** [November 11, 2020, 12:56pm UTC](https://discuss.elastic.co/t/how-to-add-date-format-in-delete-by-query/254600/17 "2020-11-11T12:56:30Z")

</div>

Hi David

Thanks for your reponse.

I found my mistake. the Index was auto-created based on reindexing curl command.

Due to this , field "sysTimestamp" was getting type as "text" instead of date and format.

Hence by \_delete\_by\_query was failing with my error mentioned above

```auto
type" : "number_format_exception",
            "reason" : "For input string: \"2020-11-07\"

```

I am now creating my index using defined mapping and things are good now.

Thank You very much for your help and responses.

Tushar Nemade

---

<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:** [December 9, 2020, 12:56pm UTC](https://discuss.elastic.co/t/how-to-add-date-format-in-delete-by-query/254600/18 "2020-12-09T12:56:31Z")

</div>

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