Elasticsearch timezone issue with Laravel

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.


Backend developer from Berlin, building https://arbeitnow.com/

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store