# Date script not working
**URL:** https://discuss.elastic.co/t/date-script-not-working/201873
**Category:** Elasticsearch
**Created:** [October 1, 2019, 9:44pm UTC](https://discuss.elastic.co/t/date-script-not-working/201873 "2019-10-01T21:44:17Z")
**Posts on this page:** 17
**Page:** 1
### Author:  [@Cain310](https://discuss.elastic.co/u/Cain310)
#### Post date: [October 1, 2019, 9:44pm UTC](https://discuss.elastic.co/t/date-script-not-working/201873/1 "2019-10-01T21:44:17Z")
I am having trouble writing a script to work with my two date fields.
ES/KB version 5.6.16
```auto
"doc": {
"chargebackDate": "2019-09-23 14:51:29",
"products": [
{
"recurringDate": "2019-09-02",
}
],
}
```
I am trying to find all documents that have a difference of \< 11 days comparing these two Date fields.
Not sure if this is even possible. I don't need the results to be more precise then a day increment.
i've tried .value and .date.dayOfYear but can seem to get it working.
Any help much appreciated.
---
### Author:  [@rjernst](https://discuss.elastic.co/u/rjernst)
#### Post date: [October 5, 2019, 5:05pm UTC](https://discuss.elastic.co/t/date-script-not-working/201873/2 "2019-10-05T17:05:38Z")
You havne't shown your mappings, but based on the structure of your example doc, you won't be able to get a value for `recurringDate` in a script because it is (I assume) a nested field. Each element of the `products` array is indexed as a separate document in Lucene, yet scripts operate on a single document at a time (and not on the internally created nested docs for such a field). In order to use this within a script you would either need to have fully separate documents per "product" here, or make some choice during indexing as to what `recurringDate` to place in a field directly on the root document.
---
### Author:  [@Cain310](https://discuss.elastic.co/u/Cain310)
#### Post date: [October 7, 2019, 3:22pm UTC](https://discuss.elastic.co/t/date-script-not-working/201873/3 "2019-10-07T15:22:38Z")
Hi Ryan,
Thanks so much for the response. Let me see if i Understand you correctly.
When you say "each Element of the products array" does that mean each key value pair in the nested products array is a separate document?
This is what my mapping looks like.
```auto
{
"mappings": {
"couchbaseDocument": {
"dynamic_templates": [
{
"date_as_string": {
"match_mapping_type": "date",
"match_pattern": "regex",
"match": "^(holdDate|startDate|endDate)$",
"mapping": {
"type": "text",
"fields": {
"type": "keyword",
"ignore_above": 256
}
}
}
},
{
"ignore": {
"match_pattern": "regex",
"match": "^(esQuery|aliasDict|gwData|orderData|firstOrderData|trackData|firstTrackData|dashboardData|jwts|permissions|error|credentials|interval|clvByAffiliate|clvByCountry|clvDistribution|filters)$",
"mapping": {
"enabled": false
}
}
}
],
"properties": {
"doc": {
"properties": {
"products": {
"properties": {
"recurringDate": {
"type": "text",
"fields": {
"date": {
"type": "date",
"format": "yyyy-MM-dd",
"ignore_malformed": true
},
"keyword": {
"type": "keyword"
}
}
}
}
},
"action": {
"type": "nested",
"dynamic": true
},
"externalId": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword"
}
}
},
"recurringDate": {
"type": "text",
"fields": {
"date": {
"type": "date",
"format": "yyyy-MM-dd",
"ignore_malformed": true
},
"keyword": {
"type": "keyword"
}
}
},
"chargebackDate": {
"type": "text",
"fields": {
"date": {
"type": "date",
"format": "yyyy-MM-dd HH:mm:ss",
"ignore_malformed": true
},
"keyword": {
"type": "keyword"
}
}
},
"acquisitionDate": {
"type": "date",
"format": "yyyy-MM-dd HH:mm:ss",
"ignore_malformed": true,
"fields": {
"text": {
"type": "text"
},
"keyword": {
"type": "keyword"
}
}
},
"timeStamp": {
"type": "date",
"format": "yyyy-MM-dd HH:mm:ss",
"ignore_malformed": true,
"fields": {
"text": {
"type": "text"
},
"keyword": {
"type": "keyword"
}
}
},
"date": {
"type": "date",
"format": "epoch_millis",
"ignore_malformed": true,
"fields": {
"text": {
"type": "text"
},
"keyword": {
"type": "keyword"
}
}
}
}
}
}
}
},
"settings": {
"index.mapping.total_fields.limit": 10000
}
}
```
This is what my 'order' Document, which contains the nested product array looks like. Some value omitted for privacy and char limit per reply ☹ .
```auto
{
"_index": "ban.....",
"_type": "doc",
"_id": "Order|05dfcb133189ba801ba5663be46c665badd009810195160419e2462d8b26ce06",
"_score": 1,
"_source": {
"meta": {
"vbuuid": 126392786703854,
"rev": "4-15c5065849d800000000000002000000",
"seqno": 1179,
"cas": 1568667021474267100,
"revSeqno": 4,
"lockTime": 0,
"flags": 33554432,
"vbucket": 840,
"expiration": 0,
"id": "Order|05dfcb133189ba801ba5663be46c665badd009810195160419e2462d8b26ce06"
},
"doc": {
"_type": "Order",
"createdDate": "2019-09-05T20:52:35.872Z",
"externalId": "523...",
"lastUpdate": "2019-09-16T20:50:20.569Z",
"relatedId": "352...",
"acquisitionDate": "2019-07-06 11:39:26",
"affid": "OA",
"affiliate": "OA",
"ancestorId": "35....",
"billingAddressId": "44170d22a65fd0005f9330eff9c1d477afc6d53f24f1d850c4f52417032e08f1",
"billingCycle": "1",
"billingFirstName": "cl....",
"billingLastName": "mo.....",
"chargebackDate": "",
"customerId": "df232f3a70b4dd0fbb20bfd5fe55cf5482d5bbf99cf36017e83a5aa8cb5806be",
"customersTelephone": "617...",
"declineReason": "DO NOT RETRY - Bank decline",
"declineSalvageDiscountPercent": "0",
"emailAddress": "chm...",
"externalCustomerId": "456..",
"gatewayDescriptor": "mect...",
"gatewayId": "198",
"ipAddress": "108.74.30.252",
"isBlacklisted": "0",
"isChargeback": "0",
"parentId": "352593",
"recurringDate": "2019-10-05",
"gift": {},
"products": [
{
"productId": "0e4f3e5398cc97b572c853cedac98671dcfa70cd349bbc8067ffb83f35d2645d",
"quantity": "1",
"isRecurring": "0",
"recurringDate": "2019-10-05",
"subscriptionId": "0c72a08ec378a861cf5d51f536a021bd",
"isShippable": "1",
"isFullRefund": "0",
"refundAmount": "0.00",
"onHold": "0"
}
],
"Notes": {
"systemNotes": [
"September 05, 2019 03:00 PM - ...",
"September 05, 2019 03:00 PM - ..."
]
},
"sourceId": "6f46986c-85ba-4207-9d16-c61fff638f0e",
"clientId": "02d41dbf-bec2-4ee9-b6fa-dc87560f8dc4",
"orderId": "05dfcb133189ba801ba5663be46c665badd009810195160419e2462d8b26ce06",
"date": 1567695611000,
"isInitial": false
}
}
}
```
---
### Author:  [@rjernst](https://discuss.elastic.co/u/rjernst)
#### Post date: [October 7, 2019, 5:14pm UTC](https://discuss.elastic.co/t/date-script-not-working/201873/4 "2019-10-07T17:14:51Z")
Thanks for posting the mappings; that helps a lot in understanding your setup.
This is not what I expected. As I previously mentioned, I had thought your `products` field was a [nested field](https://www.elastic.co/guide/en/elasticsearch/reference/current/nested.html) (which I recommend you read up on).
Going back to your original problem:
> i've tried .value and .date.dayOfYear but can seem to get it working.
I see you have a `date` multi field under `recurringDate`. Given your current mapping, all of the values of recurringDate will be indexed into this field, which would cause multiple values to exist.
Can you elaborate on the error or exact behavior you see when attempting to access doc values with `doc['products.recurringDate.date'].value`? This would give the earliest value stored (doc values are sorted). Note that doc values must be accessed by the full path to the field, so it is important to use the structure noted there.
---
### Author:  [@Cain310](https://discuss.elastic.co/u/Cain310)
#### Post date: [October 7, 2019, 9:17pm UTC](https://discuss.elastic.co/t/date-script-not-working/201873/5 "2019-10-07T21:17:03Z")
> I see you have a `date` multi field under `recurringDate` . Given your current mapping, all of the values of recurringDate will be indexed into this field, which would cause multiple values to exist.
Ok I have two occurrences or recurringDate one in the products object and one in the parent. Although both occurrences always have the same value when in the same Document. Does it matter when scripting in ES/lucene?
I have a multi field for recurringDate for the purposeof querying them in different ways/visualizations.
> multiple values to exist.
When you say multiple values are you referring to 'doc.reccuringDate' (Text field), 'doc.recurringDate.keyword' (nonAnalyzed), 'doc.recurringDate.Date' (Date). If so does this skew results when scripting?
> Can you elaborate on the error or exact behavior you see when attempting to access doc values
Yes, So its not erroring it's just not filtering correctly and the results don't make sense. I've tried all the methods I could find including the ones you just mentioned but the results don't reveal any pattern to me. Either it gives me 0 OR all the Documents that fulfill term queries but ignored my script. I've tried troubleshooting my script by reducing the conditions do very simple perams but no luck the results are all over the place.
Also yes i reference the full path.
Example of one i tried
```auto
POST /ban.../_search
{
"query": {
"bool": {
"must": [
{
"term": {
"doc.isChargeback": "1"
}
},
{
"term": {
"doc._type": "order"
}
}
],
"filter": {
"bool": {
"must": {
"script": {
"script": {
"source": "doc['doc.timeStamp'].date.dayOfYear - doc['doc.chargebackDate.date'].date.dayOfYear > params.difference",
"lang": "painless",
"params": {
"difference": 10
}
}
}
}
}
}
}
}
}
```
for example this should I think return me all documents where the fields have a greater then 10 day difference comparatively. And when flipping the \< sign should return the opposite. \<--- which is my current intended goal at them moment. Instead what I get is all matching terms in documents when .dayOfYear\< chargeback... which is fine but the inverse gives me two documents that are way more then two days in difference...
Hope this makes sense lol. If not happy to rephrase.
---
### Author:  [@rjernst](https://discuss.elastic.co/u/rjernst)
#### Post date: [October 7, 2019, 9:48pm UTC](https://discuss.elastic.co/t/date-script-not-working/201873/6 "2019-10-07T21:48:47Z")
I would try using [script fields](https://www.elastic.co/guide/en/elasticsearch/reference/7.4/search-request-body.html#request-body-search-script-fields) to determine what values you are subtracting. Do a query that matches a sample of your docs, and add script fields to return the script values you would be using.
---
### Author:  [@Cain310](https://discuss.elastic.co/u/Cain310)
#### Post date: [October 8, 2019, 8:05pm UTC](https://discuss.elastic.co/t/date-script-not-working/201873/7 "2019-10-08T20:05:26Z")
Very interesting. I did like you said as seen here to Identify what is really being subtracted.
```auto
POST /ban..../_search
{
"query": {
"bool": {
"must": [
{
"term": {
"doc.sourceId.keyword": "6f46986c-85ba-4207-9d16-c61fff638f0e"
}
},
{
"term": {
"doc.billingCycle": "1"
}
},
{
"term": {
"doc.isChargeback": "1"
}
},
{
"term": {
"doc._type": "order"
}
}
]
}
},
"script_fields": {
"test1": {
"script": {
"lang": "painless",
"source": "doc['doc.timeStamp'].date.dayOfYear"
}
},
"test2": {
"script": {
"lang": "painless",
"source": "doc['doc.chargebackDate.date'].date.dayOfYear"
}
}
}
}
```
The chargebackDate.date value was always 1 which clearly is not correct. So this seems to be the source of my issues.
I then recall I had to adjust my mapping for this field from this
```auto
"chargebackDate": {
"type": "date",
"format": "yyyy-MM-dd HH:mm:ss",
"ignore_malformed": true,
"fields": {
"text": {
"type": "text"
},
"keyword": {
"type": "keyword"
}
}
}
```
to this in order to get the couchbase db connector 4.1 to properly replicate the field without an error.
```auto
"chargebackDate": {
"type": "text",
"fields": {
"date": {
"type": "date",
"format": "yyyy-MM-dd HH:mm:ss",
"ignore_malformed": true
},
"keyword": {
"type": "keyword"
}
}
}
```
the chargebackDate field can be in this format "2019-08-14 12:42:44" or be empty "".
This is the error the couchbase connector gives me which i thought would be resolved by adding "ignore\_malformed": true .
Couchbase connector 4.1 error -
reason=failed to parse [doc.chargebackDate]]]; nested: ElasticsearchException[Elasticsearch exception [type=illegal\_argument\_exception, reason=Invalid format: ""
Seems we have identified the real issue at hand which is nice. But now how do you set a Date mapping on a field which can be empty as well?
---
### Author:  [@rjernst](https://discuss.elastic.co/u/rjernst)
#### Post date: [October 8, 2019, 8:39pm UTC](https://discuss.elastic.co/t/date-script-not-working/201873/8 "2019-10-08T20:39:53Z")
The root of the problem is, as you pointed out, you have some documents without a value for the field. In newer versions of Elasticsearch using `doc['field'].value` when no values exist for `field` now produces an error. The correct way to check this is by checking the number of values before accessing the value. In the current version of ES this would be done with:
```auto
if (doc['field'].size() == 0) {
...
}
```
On 5.6, you may need to use `doc['field'].values.size() == 0`.
---
### Author:  [@Cain310](https://discuss.elastic.co/u/Cain310)
#### Post date: [October 9, 2019, 1:25pm UTC](https://discuss.elastic.co/t/date-script-not-working/201873/9 "2019-10-09T13:25:30Z")
Ok ill get it done 🙏. Do you know why the first mapping of "chargebackDate" produces an error when injesting and the second does not?
---
### Author:  [@rjernst](https://discuss.elastic.co/u/rjernst)
#### Post date: [October 10, 2019, 5:17pm UTC](https://discuss.elastic.co/t/date-script-not-working/201873/10 "2019-10-10T17:17:28Z")
What error are you receiving?
---
### Author:  [@Cain310](https://discuss.elastic.co/u/Cain310)
#### Post date: [October 11, 2019, 3:28pm UTC](https://discuss.elastic.co/t/date-script-not-working/201873/11 "2019-10-11T15:28:34Z")
**Issue #1**
I'm getting the empty string error and all zeros error when I explicitly try to map any date field. Problem is these date fields can sometimes be empty "", or "0000-00-00". not sure how to properly map a field so that it maintains searchability & AGG.
one of my many attempted mapping examples....
```auto
PUT ban....
{
"mappings": {
"couchbaseDocument": {
"dynamic_templates": [
{
"labels": {
"match": "holdDate",
"match_mapping_type": "string",
"mapping": {
"type": "date",
"ignore_malformed": true
}
}
},
{
"labels2": {
"match": "recurringDate",
"match_mapping_type": "string",
"mapping": {
"type": "date",
"ignore_malformed": true
}
}
},
{
"longs_as_strings": {
"match": "externalId",
"match_mapping_type": "long",
"mapping": {
"fields": {
"ignore_above": 256,
"type": "keyword"
},
"type": "text"
}
}
},
{
"date_as_string": {
"match": "^(holdDate|recurringDate|startDate|endDate)$",
"match_mapping_type": "date",
"match_pattern": "regex",
"mapping": {
"fields": {
"ignore_above": 256,
"type": "keyword"
},
"type": "text"
}
}
}
],
"properties": {
"doc": {
"properties": {
"products": {
"properties": {
"recurringDate": {
"enabled": false
}
}
},
"action": {
"type": "nested",
"dynamic": true
}
}
},
"recurringDate": {
"enabled": false
},
"externalId": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword"
}
}
},
"chargebackDate": {
"type": "text",
"fielddata": true,
"fields": {
"date": {
"type": "date",
"format": "yyyy-MM-dd HH:mm:ss",
"ignore_malformed": true
},
"keyword": {
"type": "keyword"
}
}
},
"acquisitionDate": {
"type": "date",
"format": "yyyy-MM-dd HH:mm:ss",
"ignore_malformed": true,
"fields": {
"text": {
"type": "text"
},
"keyword": {
"type": "keyword"
}
}
},
"timeStamp": {
"type": "date",
"format": "yyyy-MM-dd HH:mm:ss",
"ignore_malformed": true,
"fields": {
"text": {
"type": "text"
},
"keyword": {
"type": "keyword"
}
}
},
"date": {
"type": "date",
"format": "epoch_millis",
"ignore_malformed": true,
"fields": {
"text": {
"type": "text"
},
"keyword": {
"type": "keyword"
}
}
}
}
}
},
"settings": {
"index.mapping.total_fields.limit": 10000
}
}
```
> 15:43:31.012 [es-worker-1] WARN c.c.c.e.i.ElasticsearchWriter - Permanent failure to index event MUT:3/25076435448763@171[0-2151]=Order|7620bb3868f29d981c28830db598b4f3ccc0e3e5b5b3f978d230423689dda7c5; status code: BAD\_REQUEST ElasticsearchException[Elasticsearch exception [type=mapper\_parsing\_exception, reason=failed to parse [doc.chargebackDate]]]; nested: ElasticsearchException[Elasticsearch exception [type=illegal\_argument\_exception, reason=Invalid format: ""]];
> 85497c4b52ec424d48689dcf7d706; status code: BAD\_REQUEST ElasticsearchException[Elasticsearch exception [type=mapper\_parsing\_exception, reason=failed to parse [doc.recurringDate]]]; nested: ElasticsearchException[Elasticsearch exception [type=illegal\_field\_value\_exception, reason=Cannot parse "0000-00-00": Value 0 for monthOfYear must be in the range [1,12]]];
I was able to hack it so the ingest errors go away which at first made me think ok it worked yay.
```auto
"chargebackDate": {
"type": "text",
"fields": {
"date": {
"type": "date",
"format": "yyyy-MM-dd HH:mm:ss",
"ignore_malformed": true
},
"keyword": {
"type": "keyword"
}
}
},
```
But when querying and scripting the subject date fields 0 hits show up. seems to be skipping these explicitly set date fields all together.
I found this here [ignore\_malformed to support ignoring JSON objects ingested into fields of the wrong type · Issue #12366 · elastic/elasticsearch · GitHub](https://github.com/elastic/elasticsearch/issues/12366#issuecomment-490675022)
maybe It needs to be a dynamic field? idk
but didn't work.
---
### Author:  [@rjernst](https://discuss.elastic.co/u/rjernst)
#### Post date: [October 15, 2019, 11:16pm UTC](https://discuss.elastic.co/t/date-script-not-working/201873/12 "2019-10-15T23:16:13Z")
> these date fields can sometimes be empty "", or "0000-00-00"
You'll need to special case those when indexing then. You have `ignore_malformed = true`, so the value for that will be skipped when indexing, thus making the document not actually have a value for that field.
---
### Author:  [@Cain310](https://discuss.elastic.co/u/Cain310)
#### Post date: [October 17, 2019, 8:59pm UTC](https://discuss.elastic.co/t/date-script-not-working/201873/13 "2019-10-17T20:59:42Z")
Hey so trying to get to that point but after some trouble shooting I have no clue why the below is returning the one document regardless if I change the param.difference to greater then 9...errrr
```auto
DELETE test_bankable
GET test_bankable/_mapping
GET test_bankable/_search
{
"query": {
"match_all": {}
}
}
PUT test_bankable
{
"mappings": {
"doc": {
"date_detection": false,
"dynamic_templates": [
{
"dates": {
"match": ".*chargebackDate|acquisitionDate|timeStamp|recurringDate",
"match_pattern": "regex",
"mapping": {
"type": "date",
"format": "yyyy-MM-dd HH:mm:ss||dateOptionalTime"
}
}
}
]
}
}
}
PUT test_bankable/doc/2
{
"chargebackDate": "2018-08-29 06:41:37"
}
PUT test_bankable/doc/1
{
"timeStamp": "2018-08-20 06:41:37"
}
POST test_bankable/_search
{
"query": {
"bool": {
"must": {
"match_all": {}
},
"filter": {
"bool": {
"must": {
"script": {
"script": {
"source": "doc['timeStamp'].date.dayOfYear - doc['chargebackDate'].date.dayOfYear < params.difference",
"lang" : "painless",
"params": {
"difference": 10
}
}
}
}
}
}
}
}
}
GET test_bankable/_search
{
"query": {
"match_all": {}
},
"script_fields": {
"test1": {
"script": {
"lang": "painless",
"source": "doc['timeStamp'].value"
}
},
"test2": {
"script": {
"lang": "painless",
"source": "doc['chargebackDate'].value"
}
}
}
}
```
---
### Author:  [@Christian\_Dahlqvist](https://discuss.elastic.co/u/Christian_Dahlqvist)
#### Post date: [October 17, 2019, 9:05pm UTC](https://discuss.elastic.co/t/date-script-not-working/201873/14 "2019-10-17T21:05:25Z")
I do not think you can compare fields from two different documents in a script as it runs in the context of a single document.
---
### Author:  [@Cain310](https://discuss.elastic.co/u/Cain310)
#### Post date: [October 18, 2019, 2:40pm UTC](https://discuss.elastic.co/t/date-script-not-working/201873/15 "2019-10-18T14:40:39Z")
Ok even with that change results of the script are not accurate sooo weird. Must be a synstax or format issue somewhere.
```auto
DELETE test_bankable
GET test_bankable/_mapping
GET test_bankable/_search
{
"query": {
"match_all": {}
}
}
PUT test_bankable
{
"mappings": {
"doc": {
"date_detection": false,
"dynamic_templates": [
{
"dates": {
"match": ".*chargebackDate|acquisitionDate|timeStamp|recurringDate",
"match_pattern": "regex",
"mapping": {
"type": "date",
"format": "yyyy-MM-dd HH:mm:ss||dateOptionalTime"
}
}
}
]
}
}
}
PUT test_bankable/doc/2
{
"chargebackDate": "2018-08-29 06:41:37",
"timeStamp": "2018-08-20 06:41:37"
}
PUT test_bankable/doc/1
{
"timeStamp": "2018-08-25 06:41:37",
"chargebackDate": "2018-09-27 06:41:37"
}
POST test_bankable/_search
{
"query": {
"bool": {
"must": {
"match_all": {}
},
"filter": {
"bool": {
"must": {
"script": {
"script": {
"source": "doc['timeStamp'].date.dayOfYear - doc['chargebackDate'].date.dayOfYear < params.difference",
"lang" : "painless",
"params": {
"difference": 9
}
}
}
}
}
}
}
}
}
GET test_bankable/_search
{
"query": {
"match_all": {}
},
"script_fields": {
"test1": {
"script": {
"lang": "painless",
"source": "doc['timeStamp'].date.dayOfYear"
}
},
"test2": {
"script": {
"lang": "painless",
"source": "doc['chargebackDate'].date.dayOfYear"
}
}
}
}
```
---
### Author:  [@Cain310](https://discuss.elastic.co/u/Cain310)
#### Post date: [October 18, 2019, 2:46pm UTC](https://discuss.elastic.co/t/date-script-not-working/201873/16 "2019-10-18T14:46:19Z")
aha subtracting a smaller number from a greater yields a negative. MATHS!!!!
---
### Author:  [@system](https://discuss.elastic.co/u/system)
#### Post date: [November 15, 2019, 5:05pm UTC](https://discuss.elastic.co/t/date-script-not-working/201873/18 "2019-11-15T17:05:46Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.