'datetime' casted attribute returning current date from am empty string in the db

[nabilhassen]

Laravel Version

11.5.0

PHP Version

8.3.4

Database Driver & Version

SQLite 3.19.1 for Windows 11

Description

When an empty string is persisted on a datetime column in the database and that column is casted by 'datetime' cast, you get current datetime instead of null as the output.

Steps To Reproduce

1. create a migration which includes a datetime column as such:
   $table->dateTime('approved_at')->nullable();
2. On the model class, cast the attribute using 'datetime' cast as such:
   protected function casts(): array { return [ 'approved_at' => 'datetime', ]; }
3. Let us assume that we have a Post model and store some date as such:
   Post::create(['title' => 'Laravel', 'approved_at' => '']);
4. And finally, let's try to access the 'approved_at' field as such:
   $post->approved_at;

Expected result: null or ''
Outcome: current datetime (i.e. same as now() method)

[Tofandel]

Shouldn't it be Post::create(['title' => 'Laravel', 'approved_at' => null]); if your field is nullable

[nabilhassen]

Shouldn't it be Post::create(['title' => 'Laravel', 'approved_at' => null]); if your field is nullable

@Tofandel

Using Livewire for example, if you set a date on the standard html datepicker and then clear it, then an empty string is passed instead of null.

I don't know what happens with just regular blade code, didn't try it.

[Tofandel]

If you use the validator with nullable rule and retrieve the validated fields that should convert empty strings to null

[ghost]

This is news for me.... so nullable strings can't be empty strings from laravel's perspective....
This is one argument against using validated array then in this case.
And this is a monolithic approach... Is this the same in lumen?

[nabilhassen]

Already did that, no difference.

[crynobone]

This seems to be a Livewire issue instead of a bug for Laravel Framework. Feel free to continue the discussion for now.

[nabilhassen]

@crynobone @Tofandel

$post = Post::create(['title' => 'Laravel', 'approved_at' => '']); and approved_at is datetime casted.
Shouldn't $post->approved_at be null or empty string ? It is returning current datetime instead!!!

I'm curious:
Isn't this a bug? If not, what is it?
And what does this have to do with Livewire?

[Tofandel]

Did you make sure that you don't have attributes in the mysql column like ON CREATE CURRENT_DATETIME?

[nabilhassen]

As soon as I remove the datetime cast, it works.

[ghost]

Try Carbon::parse('')->toDateTimeString(). See if it returns now.
I remember strange behavior of this parse in the past, when if I would parse the 'a' character I would get now.
This is one of the reasons that made me NEVER use casts and when comparing, I cast anything (except null) that comes from db to string and compare string with string (or null).

[Tofandel]

You can use setApprovedAtAttribute and do some logic there to convert empty string to null and the rest Carbon::parse it as a workaround but yes it does sound like a bug, though one with 0% chance of being fixed because touching the date casts is completely frowned upon now with the potential breaking change

[ghost]

The issue comes from Carbon. Carbon::parse('') returns now.

https://play.phpsandbox.io/nesbot/carbon

This is why I never cast anything and use anything that comes from db as string, int or null

[wizzymore]

This is because you are using SQLite and you are not forcing strict types on the table.

In MySQL and Postgres '' is not a valid date, but in SQLite as by default types are just a suggestion and they are not enforced it will be saved as empty string and parsed as such.

If you plan to use SQLite in product enforce strict types in your table or validate the input better.
What is happening is not a problem with Laravel.

[nabilhassen]

It is neither SQLite's nor Laravel's issue. It is actually Carbon's behavior. Check out #51507 (comment)

[wizzymore]

Yes, indeed, there is no bug present here for this issue. What i wanted to tell you was why the behaviour mentioned by marius-mcp was happening as normally you should not be able to save empty string dates in the database.

[ghost]

@wizzymore null behaves in the same way.

[wizzymore]

@wizzymore null behaves in the same way.

Nulls from DB with a datetime cast get casted as a null and are not passed to carbon so the unexpected behaviour doesn't happen there