Framework: TIMESTAMP column from MySQL does not include fractional seconds

[mhayes14]

MySQL supports fractional seconds to a precision of 6 figures on TIME, DATETIME and TIMESTAMP columns (e.g. 2014-02-10 13:54:30.123456).

However, Eloquent seems to ignore the microseconds. E.g. if the value is '2014-02-10 13:54:30.123456' in the database, Eloquent simply returns '2014-02-10 13:54:30'.

On a related note, the MySQL schema builder doesn't support specifying the fractional seconds precision for these column types.

[taylorotwell]

I believe this is set at the grammar level. If there is demand for this maybe a pull request could be submitted to make these things easier.

[BlakeLucchesi]

Was this closed because it was implemented or because its being marked as "will not fix"?

[RSully]

@BlakeLucchesi looks like it was closed but if someone else wants to implement it they are free to. Ideally if someone did that they would have tagged this issue, but who knows. If you look into it add a comment.

[BlakeLucchesi]

Thanks. After a bit of investigation it also looks like its only supported in mysql 5.6 (not sure what versions of Postgres). If I end up needing this I'll make sure to contribute with a pull request. Thanks for the follow up.

[malhal]

Saving works but reading it back out its missing the fractional seconds, I'd really like to see this implemented. I think the problem is in the PHP PDO driver because the execute statement is returning the timestamp missing the fractional seconds. I worked around the issue by making PDO think its fetching a string instead. e.g.

$q->select(DB::raw("CONCAT(mytime) as mytime")); // gives 2014-11-14 17:47:33.326594

[reshma-r-p]

may i know how i can implement it in laravel? i tried lot. But it doesnt accept milliseconds.

[zmorris]

Fractional timestamps is required to do russian doll caching properly (a simple test to POST a value and GET the same endpoint fails with second granularity because the updated_at portion of the key is often the same). This mostly works for me as of Laravel 5.2:

class BaseModel extends Model
{
    protected $dateFormat = 'Y-m-d\TH:i:s.u';

    protected function asDateTime($value)
    {
        try {
            return parent::asDateTime($value);
        } catch (\InvalidArgumentException $e) {
            return parent::asDateTime(new \DateTimeImmutable($value));
        }
    }

    public function newQuery()
    {
        $query = parent::newQuery();

        if($this->usesTimestamps()) {
            $table = $this->getTable();
            $column = $this->getDeletedAtColumn();

            $query->addSelect(DB::raw("concat($table.$column) as $column"));
        }

        return $query;
    }

    public function getUpdatedAtAttribute()
    {
        return $this->usesTimestamps() ? array_get($this->attributes, $this->getUpdatedAtColumn()) : null;
    }

}

There is a lot going on here because it gets the query with scopes applied and then adds a select for the updated_at column to the end, which overwrites any previously loaded updated_at column later while Laravel hydrates the Model from the query. For being an ugly hack, it worked surprisingly well the very first time.

Timestamps are stored internally as [Carbon][3] in Laravel:

dd(MyModel->first()->updated_at->format('Y-m-d H:i:s.u'));

Output:

2017-04-14 22:37:47.426131

Also be sure to run a migration to convert your columns to fractional timestamps. Microsecond precision raises the size of timestamps from 4 bytes to 7 bytes but this is 2017, don't let saving a byte or two by choosing millisecond precision cost you a fortune later when you find yourself serving stale cache entries:

\DB::statement("ALTER TABLE my_table MODIFY updated_at TIMESTAMP(6) NULL DEFAULT NULL");

Sadly I haven't found a way to modify the migration schema timestamps() function to do this.

More info at http://stackoverflow.com/a/43420854/539149

[leehicks]

I know this is old, but I found this to be my problem retrieving fractional seconds on timestamps. This is unfortunately still the case in PHP 7.x. The Connector.php base class sets PDO::ATTR_EMULATE_PREPARES => false. When true, the fractional seconds are there. Would love to not have to set that to true.

[lazychaser]

@leehicks what are potential issues when set to true?

[icamys]

@lazychaser I am getting two errors:

1. On model creation sometimes getting:

   In Carbon.php line 775:
       Trailing data  
   

2. If I set protected $dateFormat = 'Y-m-d\TH:i:s.u'; into model, I get:

   In Carbon.php line 775:
       Unexpected data found.  
   

Solution proposed by @zmorris works best.

Btw, here's my modified version of BaseModel class, that works in Laravel5.6:

class BaseModel extends Model
{
    protected $dateFormat = 'Y-m-d\TH:i:s.u';

    protected function asDateTime($value)
    {
        try {
            return parent::asDateTime($value);
        } catch (\InvalidArgumentException $e) {
            return parent::asDateTime(new \DateTimeImmutable($value));
        }
    }

    public function newQuery()
    {
        $query = parent::newQuery();

        if($this->usesTimestamps()) {
            $table = $this->getTable();

            $createdAt = $this->getCreatedAtColumn();
            $updatedAt = $this->getUpdatedAtColumn();
            $query
                ->select()
                ->addSelect(DB::raw("CAST($table.$updatedAt AS CHAR) as $updatedAt"))
                ->addSelect(DB::raw("CAST($table.$createdAt AS CHAR) as $createdAt"));
            ; // Using CAST instead of CONCAT as it is compatible with SQLite database
        }

        return $query;
    }
}

Here getUpdatedAtAttribute() is absent and method addSelect() is called on builders select() return value.

In migrations also should be called:

$table->timestamps(3); // Increasing timestamp precision

[doncadavona]

Hi guys, with select() statement, the timestamps' microseconds are opted:

\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

Is there a way to include the timestamps' microsecond with select('*') or select()?

[c4l3b]

PHP 7.3 will fix the bug with PDO and fractional timestamps:

https://github.com/php/php-src/commit/71c04324b7161f9ed151e9550a1a6764c5d170f7#diff-7748eb3bfdd3bf962553f6f9f2723c45R61

[pluk]

@c4l3b Hi.
Sorry, can you tell me is this same bug or new.
If the timestamp column in the database like 2020-03-04 09:07:36.716946, then everything works grate. And PDO return
["created_at"]=>
string(26) "2020-03-04 09:07:36.716946"

But if timestamp like 2020-03-04 09:00:00.000000 PDO return
["created_at"]=>
string(26) "2020-03-04 09:00:00"