CODEWITHSUNDEEP
laraveltimezonephp-carbon
Kunal Rajput
Kunal Rajput

Reputation: 794

Laravel WhereDate Filter in Auth User Time zone

My default Laravel application timezone is America/Los_Angeles (pst), I'm storing all the timestamps like created_at with this timezone in database.

In the user profile, we are providing options to select a timezone. While showing the list of data for example in trip listing I'm converting & showing created at as per user selected time zone ( $date->setTimezone($user->timezone);)

For example, if the trip Id 197 has created_at 2020-06-11 23:00:00 stored in db (as per default application timezone i.e. pst) while in the listing I'm showing 2020-06-12 02:00:00 (est timezone as per user profile 3 hrs ahead).

Now everything works fine until I had to add date range (start & end date) filter in the listing. The problem is if I'm selecting start date 2020-10-12 in the filter, in result it is not getting 197 trip id because in the database it is stored as 2020-06-11 23:00:00., this 197 id record should be there in listing after filter because as per auth user timezone the trip is added on 2020-06-12. My DB query is $trips->whereDate('created_at', '>=' ,$request->start_date);. I have the only date and not time in request for filter trips I need to somehow pass timezone in this query or is there any better solution for this. The date filter should work as per user selected timezone

Upvotes: 1

Views: 1667

Answers (2)

Kunal Rajput
Kunal Rajput

Reputation: 794

if anyone faced a similar problem following is the answer I found Generally for date range filters you’ll want to make sure you’re setting the start dates time to 00:00 and the end dates time to 23:59

if($request->filled('start_date'))
{
    // $request->start_date;
    $date = Carbon::parse($request->start_date, auth()->user()->timezone)
        ->startOfDay()
        ->setTimezone(config('app.timezone'));

    $brokers->where('created_at', '>=' ,$date);
}

if($request->filled('end_date'))
{
    $end_date = Carbon::parse($request->end_date, auth()->user()->timezone)
        ->endOfDay()
        ->setTimezone(config('app.timezone'));
    $brokers->where('created_at', '<=' ,$end_date);
}

Upvotes: 5

KyleK
KyleK

Reputation: 5056

When you can have different timezones for 1 column in a table, you need dateTimeTz to store both datetime + timezone for each row.

With this, the whereDate will use the timezone stored, then you can reconvert to any other timezone on need without loss.

Upvotes: 0

Related Questions