# Update "timestamp" field by query (subtract time from the original field and reindex)

**URL:** <https://discuss.elastic.co/t/update-timestamp-field-by-query-subtract-time-from-the-original-field-and-reindex/133851>\
**Category:** Elasticsearch\
**Created:** [May 30, 2018, 10:05am UTC](https://discuss.elastic.co/t/update-timestamp-field-by-query-subtract-time-from-the-original-field-and-reindex/133851 "2018-05-30T10:05:39Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![dboss101](https://avatars.discourse-cdn.com/v4/letter/d/7993a0/32.png) [@dboss101](https://discuss.elastic.co/u/dboss101)\
**Post date:** [May 30, 2018, 10:05am UTC](https://discuss.elastic.co/t/update-timestamp-field-by-query-subtract-time-from-the-original-field-and-reindex/133851/1 "2018-05-30T10:05:39Z")

</div>

Hi,

I'm trying to create a rest call that will perform the following.

1. find all documents that match a simple query of field:value.
2. in those documents, update the value of the "timestamp" field to be timestamp - 1 minute.

I was thinking to do this with the update by query api, I'm just not sure how to write the painless script to do the subtraction update of the timestamp field.

any help appreciated.

thanks!

---

<div class="post-metadata">

**Author:** ![abdon](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/abdon/32/9195_2.png) [@abdon](https://discuss.elastic.co/u/abdon)\
**Post date:** [May 31, 2018, 8:15am UTC](https://discuss.elastic.co/t/update-timestamp-field-by-query-subtract-time-from-the-original-field-and-reindex/133851/2 "2018-05-31T08:15:11Z")

</div>

Something like this should work:

```auto
POST my_index/_update_by_query
{
  "query": {
    "match": {
      "field": "value"
    }
  },
  "script": {
    "source": "ctx._source.timestamp = OffsetDateTime.parse(ctx._source.timestamp).minusMinutes(1)"
  }
}

```

---

<div class="post-metadata">

**Author:** ![dboss101](https://avatars.discourse-cdn.com/v4/letter/d/7993a0/32.png) [@dboss101](https://discuss.elastic.co/u/dboss101)\
**Post date:** [May 31, 2018, 9:13am UTC](https://discuss.elastic.co/t/update-timestamp-field-by-query-subtract-time-from-the-original-field-and-reindex/133851/3 "2018-05-31T09:13:36Z")

</div>

thank you very much!

---

<div class="post-metadata">

**Author:** ![dboss101](https://avatars.discourse-cdn.com/v4/letter/d/7993a0/32.png) [@dboss101](https://discuss.elastic.co/u/dboss101)\
**Post date:** [May 31, 2018, 12:41pm UTC](https://discuss.elastic.co/t/update-timestamp-field-by-query-subtract-time-from-the-original-field-and-reindex/133851/4 "2018-05-31T12:41:14Z")

</div>

actually got an error while trying to run this...

```
{
"error": {
	"root_cause": [{
		"type": "script_exception",
		"reason": "runtime error",
		"script_stack": ["java.util.Objects.requireNonNull(Objects.java:228)", "java.time.format.DateTimeFormatter.parse(DateTimeFormatter.java:1848)", "java.time.OffsetDateTime.parse(OffsetDateTime.java:402)", "java.time.OffsetDateTime.parse(OffsetDateTime.java:387)", "ctx._timestamp = OffsetDateTime.parse(ctx._timestamp).minusMinutes(3)", " ^---- HERE"],
		"script": "ctx._timestamp = OffsetDateTime.parse(ctx._timestamp).minusMinutes(3)",
		"lang": "painless"
	}],
	"type": "script_exception",
	"reason": "runtime error",
	"script_stack": ["java.util.Objects.requireNonNull(Objects.java:228)", "java.time.format.DateTimeFormatter.parse(DateTimeFormatter.java:1848)", "java.time.OffsetDateTime.parse(OffsetDateTime.java:402)", "java.time.OffsetDateTime.parse(OffsetDateTime.java:387)", "ctx._timestamp = OffsetDateTime.parse(ctx._timestamp).minusMinutes(3)", " ^---- HERE"],
	"script": "ctx._timestamp = OffsetDateTime.parse(ctx._timestamp).minusMinutes(3)",
	"lang": "painless",
	"caused_by": {
		"type": "null_pointer_exception",
		"reason": "text"
	}
},
"status": 500
}

```

any idea what can be the issue here?

---

<div class="post-metadata">

**Author:** ![abdon](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/abdon/32/9195_2.png) [@abdon](https://discuss.elastic.co/u/abdon)\
**Post date:** [May 31, 2018, 8:19pm UTC](https://discuss.elastic.co/t/update-timestamp-field-by-query-subtract-time-from-the-original-field-and-reindex/133851/5 "2018-05-31T20:19:03Z")

</div>

You are using `ctx._timestamp` instead of `ctx._source.timestamp` (or `ctx._source.fieldname`, where `fieldname` is the name of the timestamp field you are trying to update).

---

<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:** [June 28, 2018, 8:19pm UTC](https://discuss.elastic.co/t/update-timestamp-field-by-query-subtract-time-from-the-original-field-and-reindex/133851/6 "2018-06-28T20:19:04Z")

</div>

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