Use timezone in date range scope

Hello! I need to use specific timezone in my app different from general app.timezone setting. This setting is also set in the config file

'timezone' => 'UTC',

'app_timezone' => 'Europe/Moscow'

This is necessary in order for dates to be saved with the UTC time zone and displayed in the app with a different timezone if necessary. For that I use Eloquent mutator in my model like this

public function getCreatedAtAttribute($value)
{
     return Carbon::createFromTimestamp(strtotime($value))
        ->timezone(config('app.app_timezone'));
}

And it works fine. But I also have dates range scope in model and this mutation dosen't take effect in this case

public function scopeByPeriod(Builder $query, $start, $end)
{
    return $query
        ->whereDate('created_at', '>=', $start)
        ->whereDate('created_at', '<=', $end);
}

Tell please, how can I convert UTC created_at timestamps to specific timezone in this dates range scope case.

bobbybouwmann

4 years ago

Best Answer

Level 88

Well, in this case, the getCreatedAtAttribute accessor is only applied whenever you retrieve a model from the database. In the scopeByPeriod method you're still working on database level where the timezone is still used based on what is stored in the database.

The best solution here is to convert the $start and $end dates from Europe/Moscow to UTC when comparing them in the query.

public function scopeByPeriod(Builder $query, $start, $end)
{
	$start = Carbon::parse($start)->setTimezone('UTC');
	$end = Carbon::parse($end)->setTimezone('UTC');

	return $query
		->whereDate('created_at', '>=', $start)
		->whereDate('created_at', '<=', $end);
}

Another solution is using convert_tz in a raw query but that also makes it more complicated.

The general idea when working with timezones is that you should stick to the default (UTC) and convert everything to UTC when working with data. Only convert it back to the correct timezone when displaying the value. This way you have the least headaches with timezones ;)

1 like

itohin

4 years ago

Level 10

@bobbybouwmann Thank you very match! I dont now why but changing the start/end timezone don't work in my case. Finally I use convert_tz like this

public function scopeByPeriod(Builder $query, $start, $end)
{
    return $query
        ->whereRaw("DATE(CONVERT_TZ(created_at,'+00:00','+03:00')) >= '" . $start->format('Y-m-d') . "'")
        ->whereRaw("DATE(CONVERT_TZ(created_at,'+00:00','+03:00')) <= '" . $end->format('Y-m-d') . "'");
}

It works well)

1 like

Please or to participate in this conversation.