Date issue in elasticsearch

[aliyesami (sami)]

I loaded an relation table data into elasticsearch . the max date on relation side is not matching with the max date on elastic side although the total number of records match .

SQL> select max(pur_trans_date) from tab3;

MAX(PUR_TRANS_DATE)

02-mar-2016 23:59:57

SQL> select /*+ parallel(10) / count() from tab3;

COUNT(*)

61429218

[root@hadoop5 ~]# curl -XGET "http://hadoop1:9200/purchase/_search"?pretty -H 'Content-Type: application/json' -d'

{ "size":0,
"aggs":{
"max_date":{ "max":{ "field":"pur_trans_date"}}
}
}'
{
"took" : 4,
"timed_out" : false,
"_shards" : {
"total" : 5,
"successful" : 5,
"skipped" : 0,
"failed" : 0
},
"hits" : {
"total" : 61429218,
"max_score" : 0.0,
"hits" :
},
"aggregations" : {
"max_date" : {
"value" : 1.456981197E12,
"value_as_string" : "2016-03-03T04:59:57.000Z"
}
}
}

[cbuescher (Christoph)]

This looks like a time zone issues. Elasticsearch stores dates as long values in UTC time zone. Depending on how you load the data from your relational db and how it is stored there (as long timestamps? as Strings that need to get parsed?) it might end up differently in ES. I would check your data ingestion process along those lines.