Correct time-zone for carbon on many records

Question

So i'm trying to set correct time-zone on Carbon::today() for getting all of the results created for this day. Let's explain a little bit more.

I have a command that archive the Notes when created_at column is today, so I get all Notes that are created today and check them in foreach based on Building model timezone

Database structure:

Buildings:
    -id
    -name
    -timezone

Notes:
    -id
    -name
    -created_at
    -updated_at
    -building_id (reference id on buildings)

My code:

$notes = Note::where('archived', null)
        ->whereDate('created_at',Carbon::today())
        ->get();


foreach($notes as $note) {
    // Set the current timezone 
    date_default_timezone_set($note->building->timezone)

    $note->update([
        'archived' =>1,
        'archived_at'=>Carbon::now()
    ]) // update archive status etc...
}

I want Carbon::today() to be with the current timezone of the record building.

I've tried with query something like that but still the issue is not solved:

select
    *
from
    `notes`
    inner join `buildings` on `notes`.`building_id` = `buildings`.`id`
where
    date(`notes`.`created_at`) = CONVERT_TZ('NOW()', 'UTC', `building`.`timezone`);

Is there any way to do that? Thanks. :)

Answer 1

date_default_timezone_set is as the name says for the default, you should not change the default timezone multiple times during the same process.

Actually it should just always stay UTC so you easily scale and share it outside, then you can simply get now or today for a given timezone:

Carbon::today($note->building->timezone)
Carbon::now($note->building->timezone)

But a better solution is to have also your dates stored as UTC in your DB, so you convert created_at from timezone to UTC instead of converting NOW from UTC to building.timezone, it would a better approach for data consistency in your DB.