Increment datetime using update by query

(Pat) #1

I am tasked with updating the datetime field to keep log records within a week of the current date. The data set contains records from a 1 month period and I need to increment each record by a set number of days so simply replacing the field with a specific date is not ideal. Is there a way to use update by query (example below is what I used to replace a username across all documents) to increment the datetime field?

POST /*/log/_update_by_query
{
  "script": {
    "inline": "ctx._source.user = 'bsmith'"
  },
  "query": {
    "term": {
      "user": "jdoe"
    }
  }
}

i.e. datetime: May 5th 2019, 05:35:18.753 incremented to May 16th 2019, 05:35:18.753

(George) #2

This is hard in Painless because the date field will be returned from the ctx._source context as a string. You'll need to parse it into a LocalDateTime type, which is messy. Once you have that, you can call the plusDays method to add any number of days you like, then probably call toString to get the string representation back and set it in the context.

This might give you enough to go on. I've already worn myself out looking through Painless and Java docs!

(Pat) #3

A colleague was able to put the following together and has resolved the issue

POST localhost:9200/*/_update_by_query
 
{
  "script": {
    "source": "def df = DateTimeFormatter.ofPattern('yyyy-MM-dd\\'T\\'HH:mm:ss.SSS\\'Z\\'');def tmp = LocalDateTime.parse(ctx._source.datetime,df);ctx._source.datetime=tmp.plusMonths(1);",
    "lang": "painless"
  },
  "query": {"match_all":{}}
}
(George) #4

Buy that colleague a drink! Kudos! Glad you got it resolved.

1 Like