Add day to datetime field

Elastic Stack Elasticsearch
1

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",]
2

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 if only a small percent of documents needs to change - you can use Update By Query API.

3

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)

4

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

5

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. ?

6

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

7

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

8

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

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:

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

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

10

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

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)"
  }
}
11

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
}
12

You need to specify the format correctly:

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)"
  }
}
13

thank you

this has been very helpful

14

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