Search code examples
mysqllaravelmysql-json

Why datetime writen into json field has invalid value?

In laravel 10 app I need to write into json field info on logged user with time of logging

I do it with code :

\Log::info(varDump($timezone, ' -1 $timezone::'));
\Log::info(varDump(Carbon::now($timezone), ' -1 BEFORE EXIT Carbon::now($timezone)::'));
return KeyData::create([
    'key' => $key,
    'data' => [["user_id" => $loggedUserId, "online_from" => Carbon::now($timezone), 'temp_field' => 'temp_value']],
    'expired_at' => Carbon::now($timezone)->addSeconds($cachingTimeInSeconds)
]);

Checking logging output I see valid timezone and time in date field with timezone.

-1 $timezone:: : Europe/Kiev
[2024-05-23 10:03:10] local.INFO:  (Object of Carbon\Carbon) : -1 BEFORE EXIT Carbon::now($timezone):: : Array
(
    [ * endOfTime] =>
    [ * startOfTime] =>
    [ * constructedObjectId] => 000000000000055b0000000000000000
    [ * localMonthsOverflow] =>
    [ * localYearsOverflow] =>
    [ * localStrictModeEnabled] =>
    [ * localHumanDiffOptions] =>
    [ * localToStringFormat] =>
    [ * localSerializer] =>
    [ * localMacros] =>
    [ * localGenericMacros] =>
    [ * localFormatFunction] =>
    [ * localTranslator] =>
    [ * dumpProperties] => Array
        (
            [0] => date
            [1] => timezone_type
            [2] => timezone
        )

    [ * dumpLocale] =>
    [ * dumpDateProperties] =>
    [date] => 2024-05-23 10:03:10.253696
    [timezone_type] => 3
    [timezone] => Europe/Kiev
)

But checking data I see that value in "online_from" is invalid :

enter image description here

Table is created with migration :

    Schema::create('key_data', function (Blueprint $table) {
        $table->id();
        $table->string('key', 255);
        $table->json('data')->nullable();
        $table->dateTime('expired_at');
        $table->timestamp('created_at')->useCurrent();

        $table->index(['key', 'expired_at'], 'key_data_key_expired_at_index');
    });

Checking collation I see :

CREATE TABLE `key_data` (
`id` bigint unsigned NOT NULL AUTO_INCREMENT,
`key` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
`data` json DEFAULT NULL,
`expired_at` datetime NOT NULL,
`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
KEY `key_data_key_expired_at_index` (`key`,`expired_at`)
) ENGINE=InnoDB AUTO_INCREMENT=28 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

What is wrong and how that can be fixed ?

Solution

  • Make sure the DateTime is in the correct format (e.g. ISO 8601) before inserting it in the JSON. I think you're seeing different results because your logger may convert the DateTime into a more legible format.

    $now = Carbon::now($timezone)->toIso8601String();
$expiry = Carbon::now($timezone)->addSeconds($cachingTimeInSeconds)->toIso8601String();

return KeyData::create([
    'key' => $key,
    'data' => [["user_id" => $loggedUserId, "online_from" => $now, 'temp_field' => 'temp_value']],
    'expired_at' => $expiry
]);