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 using Laravel Scout 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 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 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.