Laravel not saving the timezone offset on a datetime field-CodePudding

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',
        ],

CodePudding user response：

Please use this in your database Migration table

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

CodePudding user response：

Laravel provides us with dateTimeTz to store DateTime with timezone. Please see the link below.

https://laravel.com/docs/8.x/migrations#:~:text=char('name', 100);-,dateTimeTz(),-The

I hope it solves your problem.

CodePudding user response：

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.