jprante elasticsearch jdbc river changing the date value

Question

I am trying to index mysql records in elasticsearch using the jprante's elasticsearch jdbc river. I just noticed that the value in the date field is getting changed in the index.

Mapping:

content_date:{
  "type":"date"
}

content_date field for a record in mysql -> 2012-10-06 02:11:30

after running the jdbc river....

content_date field for same record in elasticsearch -> 2012-10-05T20:41:30Z

River:

curl -XPUT 'localhost:9200/_riv_index/_riv_type/_meta' -d '{
    "type" : "jdbc",
    "jdbc" : {
        "driver" : "com.mysql.jdbc.Driver",
        "url" : "jdbc:mysql://localhost:3306/db",
        "user" : "user",
        "password" : "password",
        "sql" : "select * from table where id=2409",
        "poll" : "1d",
        "versioning" : false
    },
    "index" : {
        "index" : "myindex",
        "type" : "mytype"
    }
}'

Change in date format is acceptable, but why is the date value getting changed? The river is adding utc time difference to the mysql record's date and saving it in elasticsearch. How do I stop this time conversion?

I am struggling with same issue..Anyone how to solve it http://serverfault.com/questions/674399/elasticsearch-river-jdbc-inserting-duplicate-record-in-mysql-db/674681#674681 — Prashant Lakhera, Mar 11 '15 at 18:29

score 0 · Accepted Answer · answered Oct 19 '12 at 08:33

From the Elasticsearch POV, here's what docs said :

The date type is a special type which maps to JSON string type. It follows a specific format that can be explicitly set. All dates are UTC. Internally, a date maps to a number type long, with the added parsing stage from string to long and from long to string.

Not sure that you can change it.

score 0 · Answer 2 · answered Mar 11 '15 at 19:45

Solution for this issue is to use timezone in jdbc block

"timezone" : "TimeZone.getDefault()"

Also I am saving date and time in separate field in mysql DB

| date | date | YES | | NULL | |
| time | time | YES | | NULL | |

Elasticsearch uses Joda timeformat to save date. Hence it's automatically converting my date to datetime.

In the date field, since I don't have time, it is automatically adding zero's to it.

Since I need to display data via Kibana that why I need this split..I converted format of date and time as varchar(20) as a workaround(bad idea I know) and its working fine now ..

jprante elasticsearch jdbc river changing the date value

2 Answers2