After noticing something funny in my query results with Elasticsearch, I digged down to see what was happening. A lot of head scratching, cups of coffee after — the issue made sense.
My Laravel project has an elasticsearch query to fetch all records created in the last hour
‘range’: { ‘created_at’: { ‘gte’: ‘now-1h’ } }
I executed this on Kibana’s dev tools and the results with my sample data set was fine. Once I started syncing the data from MySQL to an Elasticsearch Index, the appropriate result set wasn’t being retrieved.
Turns out that Elasticsearch always stores the timestamps in UTC [https://www.elastic.co/guide/en/elasticsearch/reference/current/mapping-date-format.html] where as the timezone in my Laravel project is set to CET which is +2 UTC.
Be careful here though — it isn’t as simple as changing the timezone on your app (you probably shouldn’t!) and you also cannot change the timezone on Elasticsearch. now is not influenced [https://www.elastic.co/guide/en/elasticsearch/reference/current/query-dsl-range-query.html#range-query-time-zone] by the time_zone parameter in a range query.
//In MySQL record — created_at 2021–04–04 22:12:09
//In Elasticsearch Index 2021–04–04T20:12:09.000000Z
For now, all I can think of is to account for the +2 UTC and hard code it to now-3h which works for now. Ideally, all of the dates synced should be converted to UTC and stored which would solve this issue.
