Laravel will just store the date in the model doing $date->format('Y-m-d H:i:s') which just uses the original hour/time of the date, but without preserving the timezone information.
Then when it retrieves it, because it's just a string with no timezone info, it will cast it to a carbon date with the app timezone (generally UTC)
This creates a disparity because you do not get the same value from the getter than the one you sent to the setter, if your date had a different timezone than the app
In simpler words, this is basically what happens
Carbon\Carbon::parse(Carbon\Carbon::parse('2022-11-08 00:00', 'America/Montreal')->format('Y-m-d H:i:s'), 'UTC');
Carbon\Carbon @1667865600 {#4115
date: 2022-11-08 00:00:00.0 UTC (+00:00), // As you can see it is UTC,
// which is ok because the database does not store the timezone information,
// but the time is 2022-11-08 00:00 and should be 2022-11-08 05:00:00 in UTC
}
// This would yield the correct result
Carbon\Carbon::parse(Carbon\Carbon::parse('2022-11-08 00:00', 'America/Montreal')->setTimezone('UTC')->format('Y-m-d H:i:s'), 'UTC');
This is a very controversial problem in laravel which doesn't have a sane and expected handling of dates in model, which should have been to convert to the app timezone before casting the date to a string without timezone info, it was marked as "intended behavior"
To palliate this, you can make your own model extension overriding the setAttribute method and extend from this class instead to convert all the dates automatically to your app timezone
<?php
namespace App;
use DateTimeInterface;
use Carbon\CarbonInterface;
use Illuminate\Database\Eloquent\Model as BaseModel;
class Model extends BaseModel
{
/**
* Set a given attribute on the model.
*
* @param string $key
* @param mixed $value
* @return mixed
*/
public function setAttribute($key, $value)
{
if ($value instanceof CarbonInterface) {
// Convert all carbon dates to app timezone
$value = $value->clone()->setTimezone(config('app.timezone'));
} else if ($value instanceof DateTimeInterface) {
// Convert all other dates to timestamps
$value = $value->unix();
}
// They will be reconverted to a Carbon instance but with the correct timezone
return parent::setAttribute($key, $value);
}
}
Also do not forget to set your database timezone to your app timezone, otherwise if you store dates in timestamp instead of datetime you may get errors when trying to insert a date because the date may fall in a daylight saving time
In your config/database.php
'connections' => [
'mysql' => [
//...
'timezone' => '+00:00', // Should match app.timezone
//...
If you didn't do that before, you will need to migrate all your dates, here is a migration that does just that
<?php
use Illuminate\Database\Migrations\Migration;
use Illuminate\Support\Facades\DB;
class ConvertAllTimestampsToUtc extends Migration {
public static function getAllTimestampColumns(): array
{
$results = DB::select(
"SELECT TABLE_NAME, COLUMN_NAME from information_schema.columns WHERE DATA_TYPE LIKE 'timestamp' AND TABLE_SCHEMA LIKE :db_name", [
'db_name' => DB::getDatabaseName()
]);
return collect($results)->mapToGroups(fn($r) => [$r->TABLE_NAME => $r->COLUMN_NAME])->toArray();
}
public static function convertTzOfTableColumns($table, $columns, $from = '+00:00', $to = 'SYSTEM')
{
$q = array_map(fn($col) => "`$col` = CONVERT_TZ(`$col`, '$from', '$to')", $columns);
DB::update("UPDATE `$table` SET " . join(', ', $q));
}
/**
* Run the migrations.
*/
public function up(): void
{
foreach (self::getAllTimestampColumns() as $table => $cols) {
self::convertTzOfTableColumns($table, $cols);
}
}
/**
* Reverse the migrations.
*/
public function down(): void
{
foreach (self::getAllTimestampColumns() as $table => $cols) {
self::convertTzOfTableColumns($table, $cols, 'SYSTEM', '+00:00');
}
}
};
