Use Timezone in DB:raw queries

user_16306396 0
Viewed 404

By default my laravel app is storing the dates in UTC timezone and depending on the user profile I am changing the timezone via Model accessor and everything is working fine.

The only issue is with DB::raw queries where the timezone is not changing. Here is the example of my query

  $sales=Sales::select(DB::raw('hour(created_at) AS hour'), DB::raw('COUNT(id) AS sales'))           
        ->where('created_at','>=',Carbon::now()->startOfDay())
        ->get()->groupBy(function($data) use($timezone){               
            return  Carbon::parse($data->created_at)->timezone($timezone)->format('H');
        });

Now here the hour is coming in UTC timezone instead of user timezone. How can I get the hour in user timezone here?

laravel php
2 Answers

your $data->created_at this is already a carbon instance and it attached time zone UTC

but in your code your creating a new carbon instance from user timezone which is wrong Carbon::parse()->timezone() it create new carbon instance from database time and add to user timezone

so it means it get date and time 06-24-2021 and attach to user timezone

use

return  $data->created_at->timezone($timezone)->format('H');

this instead here $data->created_at is carbon instance attached with UTC time zone so 06-24-2021 with UTC time zone

then you call ->timezone($timezone)->format('H') then it convert the timezone UTC to user timezone

user_108344660

You are not converting the hour. You are just converting the created_at but not the hour selected.

Try this:

$sales = Sales::select(DB::raw('MAX(created_at) as created_at'), DB::raw('COUNT(id) AS sales'))           
        ->where('created_at', '>=', Carbon::now()->startOfDay())
        ->get()
        ->map(function($data) use($timezone) {
            $data->hour = Carbon::parse($data->created_at)
                ->timezone($timezone)
                ->format('H');

            return $data;
        })
        ->groupBy(function($data) {               
            return $data->hour;
        });
user_140663110
Related