Laravel not saving the timezone offset on a datetime field

user_5493542 0
Viewed 694

The problem - timezone not being saved

I have a datetime column on my MYSQL database called starts_at. When I try to save a Carbon date with a timezone e.g. 2021-12-31 13:00:00.0 UTC (+00:00), the record successfully saves.

But when I query the record again, I see that the timezone was not saved.

Instead I see "2021-12-31 13:00:00" i.e. the UTC (+00:00) was not saved.

I want it to be explicit that the datetimes in this column are in UTC.

Code snippet

Here is an example of what is happening:


// 1. Create a Carbon object that has a timezone of UTC
$first_of_jan_sydney = Carbon::create(2022, 1, 1, 0, 0, 0, 'Australia/Sydney');
$first_of_jan_sydney->setTimezone('UTC');

// => Carbon\Carbon @1640955600 {#4471
//      date: 2021-12-31 13:00:00.0 UTC (+00:00),
//    }


// 2. Set the starts_at field to the Carbon object
$food_item_availability = FoodItemAvailability::first();
$food_item_availability->starts_at = $first_of_jan_sydney;
$food_item_availability->save();

// 3. When I requery the starts_at column, I see that the timezone (UTC) was not saved
FoodItemAvailability::first()->starts_at; // "2021-12-31 13:00:00"

Migration

In the migration that created the table, I used dateTimeTz e.g.

$table->dateTimeTz('starts_at')->nullable();

Config

In my app.php, I have set the timezone e.g.

'timezone' => 'UTC',

In my database.php, I have set the timezone e.g.

        'mysql' => [
            // ...
            'timezone'  => '+00:00',
        ],
php-carbon laravel timezone mysql php
1 Answers

dateTimeTz and timestampTz are sadly not supported by all DB engines, MySQL does not for instance. So you have to store it in 2 columns from your side.

Actually it can help having a better control application-side and avoid relying on DB for timezone handling which is IMO better.

I recommend:

$table->dateTime('starts_at')->nullable();
$table->string('starts_at_timezone')->nullable();

Here I use CarbonImmutable instead of Carbon so the timezone isn't modified on the original object. You can also use ->copy() but CarbonImmutable is generally the safest way to work with dates:

$first_of_jan_sydney = CarbonImmutable::parse('2022-01-01 00:00 Australia/Sydney');


$food_item_availability = FoodItemAvailability::first();
$food_item_availability->starts_at_timezone = $first_of_jan_sydney->tzName;
$food_item_availability->starts_at = $first_of_jan_sydney->utc();
$food_item_availability->save();

// Reload later:
$item = FoodItemAvailability::first();
CarbonImmutable::parse($item->starts_at, 'UTC')->tz($item->starts_at_timezone);

PS: Keep UTC and +00:00 as global settings, this part is fine. If you keep it, you can use CarbonImmutable::parse($item->starts_at)->tz($item->starts_at_timezone); without having to specify explicitly the 'UTC' as timezone parameter.

user_29913190
Related