How can I cast all timestamp columns to CarbonImmutable?

[hikaen2]

Eloquent casts only columns named "created_at", "updated_at" to Carbon:

By default, Eloquent will cast the created_at and updated_at columns to instances of Carbon,
https://laravel.com/docs/10.x/eloquent-mutators#date-casting

I want all timestamp columns cast to CarbonImmutable by default. How can I do this?

for example:

<?php
require __DIR__ . '/vendor/autoload.php';  // composer require laravel/framework

use Carbon\CarbonImmutable;
use Illuminate\Database\Capsule\Manager as DB;
use Illuminate\Database\Eloquent\Model;

$db = new DB;
$db->addConnection(['driver' => 'sqlite', 'database' => ':memory:']);
$db->setAsGlobal();
$db->bootEloquent();
DB::statement('CREATE TABLE flights (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, departure_at TIMESTAMP, created_at TIMESTAMP, updated_at TIMESTAMP)');

class Flight extends Model
{
    protected $fillable = ['name', 'departure_at', 'created_at', 'updated_at'];
}

function p($obj): void
{
    printf("%s (%s)\n", $obj, get_debug_type($obj));
}

$t = CarbonImmutable::parse('2023-03-31T21:19:40');
Flight::create(['name' => 'flight1', 'departure_at' => $t, 'created_at' => $t, 'updated_at' => $t]);

$flight = Flight::first();
p($flight->departure_at); // => 2023-03-31 21:19:40 (string)  <- CarbonImmutable?
p($flight->created_at);   // => 2023-03-31 21:19:40 (Illuminate\Support\Carbon)  <- CarbonImmutable?
p($flight->updated_at);   // => 2023-03-31 21:19:40 (Illuminate\Support\Carbon)  <- CarbonImmutable?

I know there are $casts, but I don't want to write $casts one by one for each timestamp columns:

protected $casts = [
    'departure_at' => 'immutable_datetime',
    'created_at' => 'immutable_datetime',
    'updated_at' => 'immutable_datetime',
]; // I don't want to write this

[Angel5a]

Add to your base model a trait, that will override asDateTime() to your desired behavior. But you would be still needed to ad custom fieds to dates array. Of cause you can dynamically resolve all timestamp fields in table, but do you really need this behavior?

[hikaen2]

Thank you.
This has come a little closer to the behavior I hoped for:

trait CarbonImmutableCasting // <- added
{
    protected function asDateTime($value): CarbonImmutable
    {
        return CarbonImmutable::parse($value);
    }
}

class Flight extends Model
{
    use CarbonImmutableCasting; // <- added

    protected $fillable = ['name', 'departure_at', 'created_at', 'updated_at'];
    protected $casts = [
        'departure_at' => 'immutable_datetime', // <- hmmm..
    ];
    //protected $dates = []; <- I could not find in laravel 10
}

function p($obj): void
{
    printf("%s (%s)\n", $obj, get_debug_type($obj));
}

$t = CarbonImmutable::parse('2023-03-31T21:19:40');
Flight::create(['name' => 'flight1', 'departure_at' => $t, 'created_at' => $t, 'updated_at' => $t]);

$flight = Flight::first();
p($flight->departure_at); // => 2023-03-31 21:19:40 (Carbon\CarbonImmutable)
p($flight->created_at);   // => 2023-03-31 21:19:40 (Carbon\CarbonImmutable)
p($flight->updated_at);   // => 2023-03-31 21:19:40 (Carbon\CarbonImmutable)

My preference is that if the DB column is of type TIMESTAMP, it is automatically cast to CarbonImmutable.
If I have 100 custom timestamp fields, I don't want to add them one by one to $casts.
To achieve this, I believe the Model needs to have the column type information (https://www.php.net/manual/en/pdostatement.getcolumnmeta.php), but from what I've seen, the Model doesn't seem to have the column type information.
Is there any reason for this?

[Sophist-UK]

I suspect that you should probably consider this requirement in two parts (and create two separate Traits for them - and perhaps create packages in case these would be more generally useful):

1. Cast attributes declared as datetime to CarbonImmutable rather than Carbon.
2. Auto-cast SQL date/time fields to datetime without having to manually declare the cast (and not auto-cast if an entry in $casts exists for the field).

1. Cast attributes declared as datetime to CarbonImmutable rather than Carbon.

If you set $casts to 'datetime' does it use your asDateTime override function (which I expect) and return an CarbonImmutable object? If you omit the $casts definition in the example above, what does the departure_at field get cast to?

The asDateTime function is defined here and has a bit more logic than your override. Would it be better to get the parent value and then if it is a Carbon object, return instead the CarbonImmutable cast of it?

2. Auto-cast SQL date/time fields to datetime

The code to auto-convert TimeStamps to Carbon can be found in function getDates here. This seems to me to be potentially a good function to override to do the auto-cast.

The actual logic for casting (which calls getDates and asDateTime) is here in function transformModelValue.

So, it seems to me that if you override getDates if you are able to iterate over the PDO query result columns using PDOStatement::getColumnMeta and return all columns that are TIMESTAMP/DATE/TIME/DATETIME/YEAR/NEWDATE then this would probably do what you want, however I am unclear how you get hold of the PDO query (which is buried several layers deeper in Laravel and executed substantially earlier) which you need to make the PDOStatement::getColumnMeta call. I haven't looked into this, but there are no calls to getColumnMeta in Laravel/Eloquent, so I suspect that you would need to enhance Laravel so that you can tag an Eloquent request so that Eloquent gathers the getColumnMeta data and stores for later retrieval through a subsequent Eloquent API call.

Note: For performance reasons you might want to cache the resulting array once it is created, because transformModelValue and hence getDates is called for every column individually and getting/iterating over the PDOStatement::getColumnMeta results might well be a performance hit on big tables and big result sets. You just need to be careful to ensure that you iterate every time a new result-set is retrieved because a new SQL call might return different columns.

Note: See also here which states that Dates are cast by default as a string (though I am not sure where this actually happens).

[hikaen2]

If you set $casts to 'datetime' does it use your asDateTime override function (which I expect) and return an CarbonImmutable object?

yes. $casts 'datetime' still returns CarbonImmutable.

If you omit the $casts definition in the example above, what does the departure_at field get cast to?

Omitting $casts will get departure_at as a string.

The asDateTime function is defined here and has a bit more logic than your override.

My asDateTime() is just one line for experimentation. I think it needs more logic for practical use, but I don't know the details at the moment.

I am unclear how you get hold of the PDO query (which is buried several layers deeper in Laravel and executed substantially earlier) which you need to make the PDOStatement::getColumnMeta call.

yes. I think this is exactly where it gets difficult. There may also be performance issues.

[Sophist-UK]

There may also be performance issues.

I don't think that getting the Column Meta will be a significant performance problem - that should take less time than the query itself.

But:

1. The default must be NOT to collect the metadata in order to preserve backwards performance compatibility, so you need both a way to indicate to the DB execution that it should collect and save (possibly a subset of) the metadata, and a way for the Laravel application to retrieve that data.

2. Since Eloquent calls Query Builder and Query Builder calls DB, that means you need to add the APIs for this to all three of these layers.

3. There may well be other functionality of a broadly similar nature that could be added at the same time e.g. EXPLAIN. This might then be used by e.g. DebugBar to be able to trigger getting the EXPLAIN table and then show the details.

4. As indicated above, you will need to ensure that the Eloquent functionality to do the casting is not adversely impacted by repeatedly iterating through the metadata to check whether a particular column needs to be auto-cast using asDateTime.

[Angel5a]

For sure I won't recommend it doing this way.
But you can inherit your own database driver based on standard one and override select method, by replacing return $statement->fetchAll(); with something like:

$values = $statement->fetchAll();
for($i = 0; $i < $statement->columnCount(); $i++) {
    $columnMeta = $statement->getColumnMeta($i);
    if ($columnMeta['native_type'] == 'TIMESTAMP') {
        foreach ($values as &$row) {
            $row->{$columnMeta['name']} = new \Carbon\CarbonImmutable($row->{$columnMeta['name']});
        }
    }
}
return $values;

Bind it into app container under the name 'db.connector.mymysql', and edit config/database.php to use your 'mymysql' driver, so any query for a whole app (which uses new driver) will get auto-conversion.
At a first glance it works:

> $user = User::first();
= App\Models\User {#4705
    id: 1,
    name: "Name",
    email: "[email protected]",
    email_verified_at: Carbon\CarbonImmutable @1680639651 {#3742
      date: 2023-04-04 20:20:51.0 UTC (+00:00),
    },
    #password: "$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi",
    #remember_token: null,
    created_at: Carbon\CarbonImmutable @1680639651 {#4074
      date: 2023-04-04 20:20:51.0 UTC (+00:00),
    },
    updated_at: Carbon\CarbonImmutable @1680640190 {#4698
      date: 2023-04-04 20:29:50.0 UTC (+00:00),
    },
  }

> $user->email_verified_at = $user->email_verified_at->addHour(1);
= Illuminate\Support\Carbon @1680643251 {#4701
    date: 2023-04-04 21:20:51.0 UTC (+00:00),
  }

> $user->save();
= true

> $user = User::first();
= App\Models\User {#4697
    id: 1,
    name: "Name",
    email: "[email protected]",
    email_verified_at: Carbon\CarbonImmutable @1680643251 {#4694
      date: 2023-04-04 21:20:51.0 UTC (+00:00),
    },
    #password: "$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi",
    #remember_token: null,
    created_at: Carbon\CarbonImmutable @1680639651 {#4702
      date: 2023-04-04 20:20:51.0 UTC (+00:00),
    },
    updated_at: Carbon\CarbonImmutable @1680640215 {#4543
      date: 2023-04-04 20:30:15.0 UTC (+00:00),
    },
  }

But it's a big no-no-no. NO!

[JurianArie]

You can put \Illuminate\Support\Facades\Date::use(\Carbon\CarbonImmutable::class);

In the boot of a service provider

[Sophist-UK]

Oooo. That's clever.

[hikaen2]

thanks! it works:

\Illuminate\Support\Facades\Date::use(\Carbon\CarbonImmutable::class); // <- added

class Flight extends Model
{
    protected $fillable = ['name', 'departure_at', 'created_at', 'updated_at'];
    protected $casts = [
        'departure_at' => 'immutable_datetime', // <- still needed
    ];
}

function p($obj): void
{
    printf("%s (%s)\n", $obj, get_debug_type($obj));
}

$t = CarbonImmutable::parse('2023-03-31T21:19:40');
Flight::create(['name' => 'flight1', 'departure_at' => $t, 'created_at' => $t, 'updated_at' => $t]);

$flight = Flight::first();
p($flight->departure_at); // => 2023-03-31 21:19:40 (Carbon\CarbonImmutable)
p($flight->created_at);   // => 2023-03-31 21:19:40 (Carbon\CarbonImmutable)
p($flight->updated_at);   // => 2023-03-31 21:19:40 (Carbon\CarbonImmutable)

It would be perfect if I could cast based on the DB column type.

[Sophist-UK]

Again, I would suggest that it would still work if you $cast departure_at to 'datetime' - and I think that this is a more Laravel way to do things - consistently using 'datetime' casts for all attributes, but redirecting all of them to CarbonImmutable.