Date format parsing exception

Elastic Stack Elasticsearch
1

I have create_date column as timestamp in database and need create_date value in elastic as "MM/DD/YYYY"
or "yyyy-MM-dd" . I am getting below error exception while loading data from oracle database using logstash 2.4.

SQL:
select to_date(to_char(cast(create_date as date),'MM/DD/YYYY') ,'MM/DD/YYYY') from table X.

exception.
"error"=>{"type"=>"mapper_parsing_exception", "reason"=>"failed to parse [create_date]", "caused_by"=>{"type"=>"illegal_argument_exception", "reason"=>"Invalid format: "2015-01-21T05:00:00.000Z" is malformed at "T05:00:00.000Z""}}}},

date mapping in elastic is as below.
createddt": {
"type": "date",
"format": "yyyy-MM-dd"
}

let me know how to load date in elastic.

2

Invalid format: "2015-01-21T05:00:00.000Z" is malformed at "T05:00:00.000Z""}}}},

You are passing all the way through the seconds but only specifying the date part of it (YYYY-MM-dd). In your case, you should be using a different time stamp.

The following could be the possible fit for you: (As given docs)
basic_ordinal_date_time:
A formatter for a full ordinal date and time, using a four digit year and three digit dayOfYear: yyyyDDD'T'HHmmss.SSSZ.

Please refer this link to know the best fit for your case:
https://www.elastic.co/guide/en/elasticsearch/reference/current/mapping-date-format.html#custom-date-formats

If you only want to use YYYY-MM-dd you might want to parse the time stamp and eliminate the other part except the standard date.

Let me know if this is not the case.

3

I need only date format in elastic such as YYYY-MM-dd or MM/DD/YYYY without time , used trunc in sql to get only date.
trunc(to_date(to_char(cast(create_date as date),'MM/DD/YYYY') ,'MM/DD/YYYY')).
Which date format we need to use elastic mapping for date property?

4

"yyyyMMdd"

This format (basic_date) worked for me and I think your initial format should work as well. Do give a try once with both!

5

yes I tried both mapping as shown and its not working.
trunc(to_date(to_char(cast(create_date as date),'MM/DD/YYYY') ,'MM/DD/YYYY')) as createddt

createddt": {
"type": "date",
"format": "yyyy-MM-dd||MM/dd/yyyy||strict_year_month_day"
}

6

Could you update the error message that you are getting now?

Btw, the date you are indexing should be of type string and this could be a possible issue.

Please try the above and then send the error message, if you get any!

7

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