Laravel not retrieving milliseconds in DateTime columns

Question

In Laravel, I have some columns with milliseconds. Note that some other columns do not have milliseconds (e.g. created_at, updated_at).

Here is my migration:

Schema::create('brands', function (Blueprint $table) {
    $table->increments('id');
    $table->dateTime('sync_date_time', 3);
    $table->timestamps();
});

My model is simply:

class Brand extends Model {}

Yet when I have a record with milliseconds (e.g. 2018-12-19 01:40:46.512) , and execute:

$brand->sync_date_time;

It always returns a string without the milliseconds portion (e.g. 2018-12-19 01:40:46).

Why is this happening, and how can I fix this issue?

Note that this is not a Carbon issue, as I am not using Carbon for this field at all.

I saw a similar question here:

Laravel timestamps to show milliseconds

that claims this is in fact a PDO issue and suggest using a string or timestamp. But it's quite an old answer, so I was hoping there is a better answer.

Possible duplicate of [Laravel timestamps to show milliseconds](https://stackoverflow.com/questions/31227069/laravel-timestamps-to-show-milliseconds) — Maraboc, Jan 29 '19 at 15:39
just writing @cfnerd 's answer. format("m-d-Y H:i:s.u") must work. — Ali Özen, Jan 29 '19 at 15:42
@cfnerd Firstly I haven't even introduced Carbon into this. So the `format` method is not available. But even if I add dateTime in the `casts` property, that just returns the milliseconds as 0. — Yahya Uddin, Jan 29 '19 at 16:10
It's not that I can't use Carbon. The returned attribute is a string. I haven't even casted it to any other type. So casting the string `2018-12-19 01:40:46` to DateTime, and then formatting it won't work. — Yahya Uddin, Jan 29 '19 at 16:17

Yahya Uddin · Accepted Answer · 2021-12-16T18:21:22.950

This issue has nothing to do with Laravel, but due to a known bug in PDO with newer versions of MySQL.

This issue exists in PHP 7.2 and has been fixed in PHP 7.3.

So to fix this issue, just upgrade to PHP 7.3.

If you want to auto-cast to a Carbon date, I found the following does NOT work when it comes to setting, saving and toArray:

protected $casts = [
   'sync_date_time' => 'datetime:Y-m-d H:i:s.v',
];

Instead I found that you must have nothing in casts for the column, and add the following methods to the model:

public function getSyncDateTimeAttribute(string $value): Carbon
{
    return Carbon::createFromFormat('Y-m-d H:i:s.v', $value);
}

public function setSyncDateTimeAttribute(Carbon $value): void
{
    $this->attributes['fidel_created'] = $value->format('Y-m-d H:i:s.v');
}

But remember you still need to upgrade to PHP 7.3!

please fix the second method name (set...). thanks – mwallisch Dec 16 '21 at 08:48 — mwallisch, Dec 16 '21 at 08:48
@mwallisch done. Good spot – Yahya Uddin Dec 16 '21 at 18:21 — Yahya Uddin, Dec 16 '21 at 18:21

CecileV · Answer 2 · 2021-11-17T14:42:15.573

I had the same bug with Lumen 8.

I've change this code in my model class :

protected $casts = [
    'last_activity_at' => 'datetime',
];

to

    public function getLastActivityAtAttribute(?string $value): ?Carbon
    {
        return empty($value) ? null : Carbon::createFromFormat('Y-m-d H:i:s.v', $value);
    }

    public function setLastActivityAtAttribute(Carbon $value): void
    {
        $this->attributes['last_activity_at'] = $value->format('Y-m-d H:i:s.v');
    }

Milliseconds save well now.

I tried this code too but it doesn't work :

protected $casts = [
    'last_activity_at' => 'datetime:Y-m-d H:i:s.v',
];

score -1 · Answer 3 · answered Jan 29 '19 at 16:58

-1

Put this at the top of your model:

protected $dates = ['sync_date_time'];

Laravel will automatically cast this to a Carbon instance which you can then format simply with:

$brand->sync_date_time->format('Y-m-d H:i:s.u')

You can also try setting the protected $dateFormat variable at the top of your model too.

answered Jan 29 '19 at 16:58

Tried that but the milliseconds just shows up as zeros – Yahya Uddin Jan 29 '19 at 17:30
And they're definitely 100% not zeros in the database correct? Since they'll be defaulted if not provided when saved. – Jan 29 '19 at 17:31
100% not zero's in DB. Try it your self with any new version of MySQL. It looks like a known issue. – Yahya Uddin Jan 29 '19 at 17:32
Could you go in to mysql and run a query to select that record with the datetime? Something like `select sync_date_time from table where x = y` and does it show the microseconds? – Jan 29 '19 at 17:38
1

Yes. The issue can be fixed by upgrading to PHP 7.3. This is a bug in PDO. – Yahya Uddin Jan 29 '19 at 17:52

Laravel not retrieving milliseconds in DateTime columns

3 Answers3

Linked