Cannot format datetime column with microseconds or milliseconds.

[doncadavona]

Bug report

I could not display my table's created_at column like "26 July 2018 18:37:58.123456" with format "j F Y H:i:s.u". Backpack displays the result as "26 July 2018 18:37:58.000000" despite my table in the database already storing my timestamps stored with microseconds following the workaround here: https://github.com/laravel/framework/issues/3506#issuecomment-383877242.

What I did:

Added my created_at column in addColumns():

[
'name' => "created_at",
'label' => "Date & Time",
'type' => "datetime",
'format' => 'j F Y H:i:s.u',
],

What I expected to happen:

Backpack should display created_at column as "26 July 2018 18:37:58.123456"

What happened:

Backpack displays created_at column as "26 July 2018 18:37:58.000000"

What I've already tried to fix it:

Followed this: https://github.com/laravel/framework/issues/3506#issuecomment-383877242 Played with php artisan tinker and run \App\User::first()->created_at->format('u') displays the microseconds as expected: 123456.

Backpack, Laravel, PHP, DB version:

• Backpack CRUD: 3.4
• Laravel: 5.6
• PHP: 7.2.2
• DB (mysql): 5.7.21

[tabacitu]

Hi @doncadavona ,

We use jenssegers/date to show the date in a readable translatable format everywhere. It's an extension of Carbon, so everything in Carbon should work with it, but this might be the problem. Could you please try creating a different column type for this purpose that uses Carbon:: instead of Date:: - if that fixes it, it means it's because of this package.

Hope it helps. Cheers!

[doncadavona]

Thank you for responding @tabacitu , I tried, created a view column type and used Carbon::parse('created_at)->format('u') and $entry->created_at->format('u')but it still returns 000000 instead of of 123456 as stored in the Database. I've found out thou, that my current jessengers/date uses nesbot/carbon version 1.0. But the later Carbon 1.12.0 states in the docs https://carbon.nesbot.com/docs/:

As of 1.12.0 Carbon now supports microseconds during instantiation or copy operations as well as by default with the format() method.

My current Laravel version requires nesbot\carbon 1.25.*. But when I dd(\App\User::first()->created_at->format('u')) inside my UserCrudController's setup(), it does return 123456. But when I do that in my custom view column, it returns 000000.

// UserCrudController.php
...
public function setup()
{
  dd(
    \App\Models\Scan::first()->created_at->format('u'),
    \Jenssegers\Date\Date::parse(\App\Models\Scan::first()->created_at)->format('u')
  );

  // Outputs: '123456' and '000000'
}

So I'm guessing Backpack could be overriding/re-casting the dates inside the views as Jenssegers\Date\Date.

[doncadavona]

Just found out it's caused by Eloquent's select('*') statement, which is used in Backpack's Backpack\CRUD\CrudPanel's setModel().

Played with php artisan tinker:

\App\User::select('*')->get()->first()->created_at->format('u') returns 000000 \App\User::all()->first()->created_at->format('u') returns 123456 \App\User::first()->created_at->format('u') returns 123456

Related to this now: https://github.com/laravel/framework/issues/3506#issuecomment-63113585

[doncadavona]

So here's my temporary workaround to display microseconds in my column:

Created a custom view column in $this->crud->addColumn():

[
'name' => "created_at",
'label' => "Date & Time",
'type' => "view",
'view' => 'users.columns.created_at',
'format' => 'j F Y H:i:s.u',
],

resources\users\columns\created_at.blade.php

<span data-order="{{ $entry->{$column['name']} }}">
    @if (!empty($entry->{$column['name']}))
    {{ \App\User::find($entry->id)->created_at->format(($column['format'] ?? config('backpack.base.default_datetime_format'))) }}
    @endif
</span>

It's ugly and slow thou.

[tabacitu]

Wow. Thank you so much for keeping us in the loop with this @doncadavona . Yes, the solution isn’t very sexy, I agree… so we can’t implement it in Backpack by default. But since very very few people need the miliseconds, I think it’s going to be fine - we’ll just direct people here, for your solution, if they do.

Also, it looks like PHP 7.3 will solve this - that’s good news :-)