# Add day to datetime field

**URL:** <https://discuss.elastic.co/t/add-day-to-datetime-field/162711>\
**Category:** Elasticsearch\
**Created:** [January 2, 2019, 8:28pm UTC](https://discuss.elastic.co/t/add-day-to-datetime-field/162711 "2019-01-02T20:28:56Z")\
**Posts on this page:** 14\
**Page:** 1

<div class="post-metadata">

**Author:** ![jpaz93](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/jpaz93/32/39371_2.png) [@jpaz93](https://discuss.elastic.co/u/jpaz93)\
**Post date:** [January 2, 2019, 8:28pm UTC](https://discuss.elastic.co/t/add-day-to-datetime-field/162711/1 "2019-01-02T20:28:56Z")

</div>

Hello, I need help with the following:

I need to add days to a datetime field

- I have different indices with datetime fields:  
"created\_at": "2018-12-21 15:29:53",  
"created\_at": "2018-12-21 15:32:11",  
"created\_at": "2019-01-01 08:17:43",

The format of the field is this  
"type": "date",  
"format": "yyyy-MM-dd HH:mm:ss"

How can I update many indexes so that only the day changes?

- the result of the update would be like this  
["created\_at": "2018-12-22 15:29:53",]  
["created\_at": "2018-12-22 15:32:11",]  
["created\_at": "2019-01-02 08:17:43",]

---

<div class="post-metadata">

**Author:** ![Igor\_Motov](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/igor_motov/32/45193_2.png) [@Igor\_Motov](https://discuss.elastic.co/u/Igor_Motov)\
**Post date:** [January 2, 2019, 9:07pm UTC](https://discuss.elastic.co/t/add-day-to-datetime-field/162711/2 "2019-01-02T21:07:39Z")

</div>

Please note, that every time you change anything in a document the entire document has to be reindex. So, if you need to change a value of a field in every document in an index, the entire index will have to be reindexed. You can do it using [Reindex API](https://www.elastic.co/guide/en/elasticsearch/reference/6.5/docs-reindex.html) if only a small percent of documents needs to change - you can use [Update By Query API](https://www.elastic.co/guide/en/elasticsearch/reference/6.5/docs-update-by-query.html).

---

<div class="post-metadata">

**Author:** ![jpaz93](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/jpaz93/32/39371_2.png) [@jpaz93](https://discuss.elastic.co/u/jpaz93)\
**Post date:** [January 2, 2019, 9:29pm UTC](https://discuss.elastic.co/t/add-day-to-datetime-field/162711/3 "2019-01-02T21:29:32Z")

</div>

Hello,  
thanks for answering

I think I did not make myself understood.

what I need is not to change a data type or add a new format

what I have to do is update several records

For example if there are 200 indices with date and time of yesterday I must update them so that they are with today's date (but only the day)

---

<div class="post-metadata">

**Author:** ![Igor\_Motov](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/igor_motov/32/45193_2.png) [@Igor\_Motov](https://discuss.elastic.co/u/Igor_Motov)\
**Post date:** [January 2, 2019, 9:36pm UTC](https://discuss.elastic.co/t/add-day-to-datetime-field/162711/4 "2019-01-02T21:36:04Z")

</div>

If only a small portion of the records will be affected, the Update By Query API should work for you.

---

<div class="post-metadata">

**Author:** ![jpaz93](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/jpaz93/32/39371_2.png) [@jpaz93](https://discuss.elastic.co/u/jpaz93)\
**Post date:** [January 2, 2019, 10:07pm UTC](https://discuss.elastic.co/t/add-day-to-datetime-field/162711/5 "2019-01-02T22:07:01Z")

</div>

Thanks,  
I've made updates with \_update\_by\_query

for example in sql server you can add a day to the records with the following function

UPDATE users set created\_at = DATEADD(day, 1, created\_at)

can i do something similar with elasticsearch. ?

---

<div class="post-metadata">

**Author:** ![Igor\_Motov](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/igor_motov/32/45193_2.png) [@Igor\_Motov](https://discuss.elastic.co/u/Igor_Motov)\
**Post date:** [January 2, 2019, 10:12pm UTC](https://discuss.elastic.co/t/add-day-to-datetime-field/162711/6 "2019-01-02T22:12:04Z")

</div>

> [@jpaz93](#):
>
> can i do something similar with elasticsearch. ?

Yes, you will need to use painless script for that.

---

<div class="post-metadata">

**Author:** ![jpaz93](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/jpaz93/32/39371_2.png) [@jpaz93](https://discuss.elastic.co/u/jpaz93)\
**Post date:** [January 9, 2019, 6:09pm UTC](https://discuss.elastic.co/t/add-day-to-datetime-field/162711/7 "2019-01-09T18:09:43Z")

</div>

Hi,

I have tried in several ways but I have not managed to do it.

I researched the painless script and with this script I can get a query on the day of the month

```
"script" : {
   "lang": "painless",
   "source" : "doc.evg_fecha_creacion.value.dayOfMonth"
}

```

For example, in this script, I try to get the day of the dates and update it to 28:

```
POST event/_update_by_query
{
  "script" : {
    "lang": "painless",
    "source": "OffsetDateTime.parse(ctx._source['created_at']).DayOfMonth.getValue() = 28"
  }

```

I get the following error:  
`Left-hand side cannot be assigned a value.`

-I really can not find a way to do this update

---

<div class="post-metadata">

**Author:** ![Igor\_Motov](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/igor_motov/32/45193_2.png) [@Igor\_Motov](https://discuss.elastic.co/u/Igor_Motov)\
**Post date:** [January 9, 2019, 8:26pm UTC](https://discuss.elastic.co/t/add-day-to-datetime-field/162711/8 "2019-01-09T20:26:57Z")

</div>

This portion `OffsetDateTime.parse(ctx._source['created_at']).DayOfMonth.getValue()` returns you an integer. So, what your script is doing is like assigning 28 to an integers. What you wrote in painless is basically equivalent to

```auto
UPDATE users set DATEPART(day, created_at) = 28

```

To fix your example you need to assign the result back to `ctx._source['created_at']` like this:

```auto
POST event/_update_by_query
{
  "script" : {
    "lang": "painless",
    "source": "ctx._source['created_at'] = OffsetDateTime.parse(ctx._source['created_at']).withDayOfMonth(28)"
  }
}

```

---

<div class="post-metadata">

**Author:** ![jpaz93](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/jpaz93/32/39371_2.png) [@jpaz93](https://discuss.elastic.co/u/jpaz93)\
**Post date:** [January 9, 2019, 8:56pm UTC](https://discuss.elastic.co/t/add-day-to-datetime-field/162711/9 "2019-01-09T20:56:39Z")

</div>

I have implemented that script and it throws the following error

```
"ctx._source['created_at'] = OffsetDateTime.parse(ctx._source['created_at']).withDayOfMonth(28)",
" ^---- HERE"

```

`could not be parsed at index 10`

---

<div class="post-metadata">

**Author:** ![Igor\_Motov](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/igor_motov/32/45193_2.png) [@Igor\_Motov](https://discuss.elastic.co/u/Igor_Motov)\
**Post date:** [January 9, 2019, 9:09pm UTC](https://discuss.elastic.co/t/add-day-to-datetime-field/162711/10 "2019-01-09T21:09:08Z")

</div>

Which version of elasticearch are you using? This works for me on 6.4.2:

```auto
DELETE test

PUT test/doc/1
{
  "created_at": "2007-12-03T10:15:30+01:00"
}

POST test/_update_by_query
{
  "script" : {
    "lang": "painless",
    "source": "ctx._source['created_at'] = OffsetDateTime.parse(ctx._source['created_at']).withDayOfMonth(28)"
  }
}

```

---

<div class="post-metadata">

**Author:** ![jpaz93](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/jpaz93/32/39371_2.png) [@jpaz93](https://discuss.elastic.co/u/jpaz93)\
**Post date:** [January 9, 2019, 9:29pm UTC](https://discuss.elastic.co/t/add-day-to-datetime-field/162711/11 "2019-01-09T21:29:55Z")

</div>

**The version is elasticsearch-6.5.4**

This is the complete error

```
{
  "error": {
    "root_cause": [
      {
        "type": "script_exception",
        "reason": "runtime error",
        "script_stack": [
          "java.time.format.DateTimeFormatter.parseResolved0(DateTimeFormatter.java:1949)",
          "java.time.format.DateTimeFormatter.parse(DateTimeFormatter.java:1851)",
          "java.time.OffsetDateTime.parse(OffsetDateTime.java:402)",
          "java.time.OffsetDateTime.parse(OffsetDateTime.java:387)",
          "ctx._source['evg_fecha_creacion'] = OffsetDateTime.parse(ctx._source['created_at']).withDayOfMonth(28)",
          " ^---- HERE"
        ],
        "script": "ctx._source['created_at'] = OffsetDateTime.parse(ctx._source['created_at']).withDayOfMonth(28)",
        "lang": "painless"
      }
    ],
    "type": "script_exception",
    "reason": "runtime error",
    "script_stack": [
      "java.time.format.DateTimeFormatter.parseResolved0(DateTimeFormatter.java:1949)",
      "java.time.format.DateTimeFormatter.parse(DateTimeFormatter.java:1851)",
      "java.time.OffsetDateTime.parse(OffsetDateTime.java:402)",
      "java.time.OffsetDateTime.parse(OffsetDateTime.java:387)",
      "ctx._source['created_at'] = OffsetDateTime.parse(ctx._source['created_at']).withDayOfMonth(28)",
      " ^---- HERE"
    ],
    "script": "ctx._source['created_at'] = OffsetDateTime.parse(ctx._source['created_at']).withDayOfMonth(28)",
    "lang": "painless",
    "caused_by": {
      "type": "date_time_parse_exception",
      "reason": "Text '2018-12-27 08:01:20' could not be parsed at index 10"
    }
  },
  "status": 500
}

```

I have made your example and throws this error.

```
{
  "error": {
    "root_cause": [
      {
        "type": "illegal_argument_exception",
        "reason": "cannot write xcontent for unknown value of type class java.time.OffsetDateTime"
      }
    ],
    "type": "illegal_argument_exception",
    "reason": "cannot write xcontent for unknown value of type class java.time.OffsetDateTime"
  },
  "status": 400
}
```

---

<div class="post-metadata">

**Author:** ![Igor\_Motov](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/igor_motov/32/45193_2.png) [@Igor\_Motov](https://discuss.elastic.co/u/Igor_Motov)\
**Post date:** [January 9, 2019, 9:50pm UTC](https://discuss.elastic.co/t/add-day-to-datetime-field/162711/12 "2019-01-09T21:50:17Z")

</div>

You need to specify the format correctly:

```auto
POST test/_update_by_query
{
  "script" : {
    "lang": "painless",
    "source": "DateTimeFormatter fomatter=DateTimeFormatter.ofPattern('yyyy-MM-dd HH:mm:ss').withZone(ZoneId.of('UTC')); ctx._source['created_at'] = ZonedDateTime.parse(ctx._source['created_at'], fomatter).withDayOfMonth(28).format(fomatter)"
  }
}

```

---

<div class="post-metadata">

**Author:** ![jpaz93](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/jpaz93/32/39371_2.png) [@jpaz93](https://discuss.elastic.co/u/jpaz93)\
**Post date:** [January 10, 2019, 1:42pm UTC](https://discuss.elastic.co/t/add-day-to-datetime-field/162711/13 "2019-01-10T13:42:45Z")

</div>

thank you

this has been very helpful

---

<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:** [February 7, 2019, 1:42pm UTC](https://discuss.elastic.co/t/add-day-to-datetime-field/162711/14 "2019-02-07T13:42:48Z")

</div>

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