Filter UTC database records based on user time zone, how?

I hoping anyone can point me in the right direction for my struggle with selecting database records within a date range, where the database is in UTC and the date range (to and from) should be considered in the local time zone of the user.

Without the date filter, the user just sees all records in his account.

The user can add a date filter and set a from date and a to date. The corresponding route is Route::get('data/{id}/from/{from}/to/{to}', 'DataController@show')

The dates from the request should be treated as dates according to user timezone. However the data I need to fetch from the database is in UTC.

When a user timezone is UTC too, it works just fine. But for any other timezone, I'm not getting the results I expect, but often shifted a day earlier or later. I could probably work out a function to calculate hours to add or substract, but there should probably be a "Laravel way" to do this.

Any ideas?

tykus

9 years ago

Level 104

You need to convert the from and to dates received with the request from the user's timezone to UTC before querying the database:

$fromUTC = Carbon::parse($from, $user->timezone)->setTimezone('UTC');
$toUTC = Carbon::parse($to, $user->timezone)->setTimezone('UTC');

// query the database

1 like

drbob

9 years ago

Level 2

Thanks @tykus looks like this should solve it!

drbob

9 years ago

Best Answer

Level 2

One tiny change in regards to current user timezone, but got it working now. Thank again @tykus !

$fromUTC = Carbon::parse($from, $guard->user()->timezone)->setTimezone('UTC');
$toUTC = Carbon::parse($to, $guard->user()->timezone)->setTimezone('UTC');

SinghWithLaravel

6 years ago

Level 2

@dr-bob , @tykus To implement this Do i need to take timezone field in database. What if a persons keeps moving from one timezone to another ? Will i need to update timezone on every request ? Can we get timezone from laravel request ?

Please or to participate in this conversation.