# Error in parsing date field
**URL:** https://discuss.elastic.co/t/error-in-parsing-date-field/233986
**Category:** Logstash
**Created:** [May 23, 2020, 9:43am UTC](https://discuss.elastic.co/t/error-in-parsing-date-field/233986 "2020-05-23T09:43:48Z")
**Posts on this page:** 10
**Page:** 1
### Author:  [@Gauti](https://discuss.elastic.co/u/Gauti)
#### Post date: [May 23, 2020, 9:43am UTC](https://discuss.elastic.co/t/error-in-parsing-date-field/233986/1 "2020-05-23T09:43:48Z")
Hi All,
I'm trying to ingest data from database into elasticsearch, for which i have written mapping in kibana as
```
PUT agent5
{
"mappings" : {
"properties": {
"logged_date": {
"type": "date",
"format": "YYYY-MM-dd HH:mm:ss.SSSS"
}
}
}
}
```
I have also tried **"format": "YYYY-MM-dd HH:mm:ss.SSSSXXX"** still getting date parsed error.
Original date field is database is like - **2020-05-20 17:39:04.4766667**
Any suggestions please.
Thanks
Gautham
---
### Author:  [@Rahul\_Kumar4](https://discuss.elastic.co/u/Rahul_Kumar4)
#### Post date: [May 23, 2020, 11:09am UTC](https://discuss.elastic.co/t/error-in-parsing-date-field/233986/2 "2020-05-23T11:09:40Z")
> [@Gauti](#):
>
> Any suggestions please.
A few questions here:
1. Does the index to which that mapping is applied to matches the index generated in Elasticsearch?
2. Do you get `mapping_parser_exception` in Elasticsearch ?
3. How are you polling the data from your database and indexing into Elasticsearch? If you are using Logstash then you can do a date type conversion using the date filter to match the datatype of your log\_date field in your mapping. I am suspecting you have some records in your database that does not match that format and which causes the Error.
---
### Author:  [@Gauti](https://discuss.elastic.co/u/Gauti)
#### Post date: [May 23, 2020, 12:10pm UTC](https://discuss.elastic.co/t/error-in-parsing-date-field/233986/3 "2020-05-23T12:10:15Z")
Here are the answers @Rahul_Kumar4
> [@Rahul\_Kumar4](#):
>
> Does the index to which that mapping is applied to matches the index generated in Elasticsearch?
Yes, i have given same index name
> [@Rahul\_Kumar4](#):
>
> Do you get `mapping_parser_exception` in Elasticsearch ?
Not sure where to check this, when i give "PUT index name" and give the mapping format i dont get any eoors.
> [@Rahul\_Kumar4](#):
>
> How are you polling the data from your database and indexing into Elasticsearch?
I'm using JDBC plugin in logstash to poll the data. I have given date format in logstash as well, still no luck
Logstash confi gfile:
```
input {
jdbc {
jdbc_driver_library => "C:\Users\sqljdbc.jar"
jdbc_connection_string => "jdbc:sqlserver://server.database.windows.net:1433;database=testdb
jdbc_driver_class => "com.microsoft.sqlserver.jdbc.SQLServerDriver"
jdbc_user => "user"
jdbc_password =>" *****"
statement => "SELECT * , CONCAT(date,' ', time) AS logged_date FROM table1"
schedule => "*/5 * * * *"
}
}
filter {
date {
match => ["logdate", "YYYY-MM-dd HH:mm:ss.SSS"]
}
}
output {
elasticsearch {
hosts => ["localhost:9200"]
index => "jdbc1"
}
#stdout { codec => rubydebug }
}
```
Kibana mapping:
```
PUT jdbc1
{
"mappings" : {
"properties": {
"logged_date": {
"type": "date",
"format": "YYYY-MM-dd HH:mm:ss.SSS"
}
}
}
}
```
---
### Author:  [@Rahul\_Kumar4](https://discuss.elastic.co/u/Rahul_Kumar4)
#### Post date: [May 23, 2020, 8:55pm UTC](https://discuss.elastic.co/t/error-in-parsing-date-field/233986/4 "2020-05-23T20:55:07Z")
> [@Gauti](#):
>
> ` match => ["logdate", "YYYY-MM-dd HH:mm:ss.SSS"]`
Your derived field in your SQL query in JDBC plugin is `logged_date` which matches your field name in the mapping but you are matching on `logdate` in the `match` statement in `date` filter. Change that to `logged_date`.
---
### Author:  [@Gauti](https://discuss.elastic.co/u/Gauti)
#### Post date: [May 23, 2020, 9:08pm UTC](https://discuss.elastic.co/t/error-in-parsing-date-field/233986/5 "2020-05-23T21:08:50Z")
I just noticed....tat was actually a typo....the field name was correctly given in logstash config.
I have one more index with similar format I was able to ingest it....my only problem here is with the milliseconds which is of 7 digits😒
Thanks
Gautham
---
### Author:  [@Badger](https://discuss.elastic.co/u/Badger)
#### Post date: [May 24, 2020, 1:16am UTC](https://discuss.elastic.co/t/error-in-parsing-date-field/233986/6 "2020-05-24T01:16:48Z")
> [@Gauti](#):
>
> my only problem here is with the milliseconds which is of 7 digits😒
If you have seven digits of subsecond precision then you need to use .SSSSSSS in the date filter pattern
---
### Author:  [@Gauti](https://discuss.elastic.co/u/Gauti)
#### Post date: [May 24, 2020, 10:50am UTC](https://discuss.elastic.co/t/error-in-parsing-date-field/233986/7 "2020-05-24T10:50:55Z")
@Badger its working, but the problem is i'm getting a 5.30hrs delay from my original time.
Thanks
Gautham
---
### Author:  [@Badger](https://discuss.elastic.co/u/Badger)
#### Post date: [May 24, 2020, 11:35am UTC](https://discuss.elastic.co/t/error-in-parsing-date-field/233986/8 "2020-05-24T11:35:37Z")
elasticsearch always stores times as UTC. kibana will adjust them to the browser's timeone by default. If your log entries are not in UTC use the timezone option on the date filter to tell logstash what timezone they are in.
---
### Author:  [@Gauti](https://discuss.elastic.co/u/Gauti)
#### Post date: [May 24, 2020, 12:07pm UTC](https://discuss.elastic.co/t/error-in-parsing-date-field/233986/9 "2020-05-24T12:07:36Z")
@Badger All of a sudden i'm getting my date as 1970 not sure from where it is taking this value.
Any idea about this?
Thanks
Gautham
---
### Author:  [@system](https://discuss.elastic.co/u/system)
#### Post date: [June 21, 2020, 12:07pm UTC](https://discuss.elastic.co/t/error-in-parsing-date-field/233986/10 "2020-06-21T12:07:43Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.