Elastic search automatically changing the date type field year value to 1970

[dhshaw (Dharmendra)]

I am using Elastic search version 7.2 and my date field mapping is
"agent_utc_time": {
"type": "date",
"format": "MM/dd/YYYY HH:mm:ss || MM/dd/YYYY || YYYY-MM-dd"
},

I am inserting the date value as: "agent_utc_time":"07/24/2019 00:00:00"

After inserting/adding, when searching the field I see the date year value looks like ( agent_utc_time: Jul 24, 1970 @ 20:00:00.000)
Year got updated to 1970, not sure what's going wrong?

Thanks!

Dharmendra

[spinscale (Alexander Reelsen)]

can you share a fully reproducible example, including index/mapping creation (optional pipelines) and a sample document and a sample query that you tried this out with?

Thank you!

[dhshaw (Dharmendra)]

Thanks Alexander for your response!

I have created mapping as-

https://:9200/dsdg_events1
Method: PUT
Header: "Content-Type: application/json"
Body:
{
"settings" : {
"number_of_shards" : 3,
"number_of_replicas" : 2
},
"mappings": {
"dynamic":false,
"properties": {
"agent_utc_time": {
"type": "date",
"format": "MM/dd/YYYY HH:mm:ss || MM/dd/YYYY || YYYY-MM-dd"
},
"agent_version": {
"type": "keyword"
},
...
...there are many other fields
}
}
}

---

Creating the index using java code here:

url = new URL("https://:9200/_bulk");
conn = (HttpsURLConnection) url.openConnection();
conn.setDoInput(true);
conn.setDoOutput(true);
conn.setRequestProperty("Accept", "application/json");
conn.setRequestProperty("Content-Type", "application/json; charset=UTF-8");
conn.setRequestMethod("POST");
OutputStreamWriter writer = new OutputStreamWriter(conn.getOutputStream(), "UTF-8");
writer.write(<>);
writer.close();
int responseCode = conn.getResponseCode();

Bulk Request Body:
{ "index" : { "_index" : "dsdg_events1", "_id" : "CEB493CA-EC73-103C-9DC8-806E7F0EE8F7" } }
{"agent_utc_time":"07/25/2019 00:00:00", "agent_version":"7.5.1","computer_type":"Windows","machine_domain_name":"partners", .....}

When I search the doc on Kibana : agent_utc_time Jul 24, 1970 @ 20:00:00.000

[spinscale (Alexander Reelsen)]

can you please create a curl/dev-tools reproducible example that I can copy & paste for testing. Extracting the information from your snippets, modifying them and then putting them into a curl command takes a lot of time in a voluntarily run forum.

Thanks a lot!

[dhshaw (Dharmendra)]

Hi Alexander,

Looks like date is getting index properly on Elastic search. CURL commands:

Create mapping:
curl -X PUT -H "Content-Type: application/json" -d '{"settings" : {"number_of_shards" : 3,"number_of_replicas" : 2},"mappings": {"dynamic":false,"properties": {"agent_utc_time": {"type": "date","format": "MM/dd/YYYY HH:mm:ss || MM/dd/YYYY || YYYY-MM-dd"},"agent_version": {"type": "keyword"},"application": {"type": "keyword"},"app_type": {"type": "keyword"},"event_id": {"type": "keyword"}}}}' -k https://:9200/dsdg_events2

Insert doc:
curl -X POST -H "Content-Type: application/json; charset=UTF-8" -d '{"agent_utc_time":"07/25/2019 00:00:00","agent_version":"7.5.1.0065","application":"outlook.exe","app_type":"Email","event_id":"CEB493CA-EC73-103C-9DC8-806E7F0EE8F7"}' -k https://:9200/dsdg_events2/_doc/CEB493CA-EC73-103C-9DC8-806E7F0EE8F7

Get the doc:
curl -X GET -k https://icam-es7-02:9200/dsdg_events2/_doc/CEB493CA-EC73-103C-9DC8-806E7F0EE8F7

{"_index":"dsdg_events2","_type":"_doc","_id":"CEB493CA-EC73-103C-9DC8-806E7F0EE8F7","_version":1,"_seq_no":0,"_primary_term":1,"found":true,"_source":{"agent_utc_time":"07/25/2019 00:00:00","agent_version":"7.5.1.0065","application":"outlook.exe","app_type":"Email","event_id":"CEB493CA-EC73-103C-9DC8-806E7F0EE8F7"}}

Here everything looks perfect but when I create index pattern on Kibana with the option "I don't want to use the Time Filter" selected on time filter field(attached screenshot):

And after if I search for the indexed doc on kibana I see the date as Jul 24 1970 as attached screen shot bellow

Even I create index pattern on Kibana using Time Filter "field name: agent_utc_time" when I search the document on Kibana it's showing wrong date year, day and time as attached screen shot below

Not sure if I am doing something wrong.

I am a Full Stack Java developer and new to Elastic search, I am sorry for simple and basic questions.

Thanks a lot!

Regards,
Dharmendra

[spinscale (Alexander Reelsen)]

just tested it locally under a 7.5.0 instance and the index pattern worked.

Can you share the mapping of that index?

[dhshaw (Dharmendra)]

I have already shared the mapping curl command above.
sharing it here again. I am using version 7.2

Create mapping:
curl -X PUT -H "Content-Type: application/json" -d '{"settings" : {"number_of_shards" : 3,"number_of_replicas" : 2},"mappings": {"dynamic":false,"properties": {"agent_utc_time": {"type": "date","format": "MM/dd/YYYY HH:mm:ss || MM/dd/YYYY || YYYY-MM-dd"},"agent_version": {"type": "keyword"},"application": {"type": "keyword"},"app_type": {"type": "keyword"},"event_id": {"type": "keyword"}}}}' -k https://:9200/dsdg_events2

Not sure if it's a bug on this version #7.2.

[spinscale (Alexander Reelsen)]

sorry for not being clear. With sharing I meant, that receiving the mapping actually returned the same mapping.

[spinscale (Alexander Reelsen)]

I think I found the problem. You should use yyyy instead of YYYY - which is a week based year and presumably not what you are after. See https://docs.oracle.com/javase/8/docs/api/java/time/format/DateTimeFormatter.html

[dhshaw (Dharmendra)]

Thanks a lot Alexander for your time to debugging and helping me.