Laravel timestamps to show milliseconds

Question

I need to store updated_at timestamp with high precision on a laravel application, using the format "m-d-Y H:i:s.u" (including milisseconds)

According to laravel documentation, I can customize the date format by setting the $dateFormat property on a class, but...

The main problem is that Laravel's schema builder adds a column of type timestamp in the database when I use $table->nullableTimestamps() And according to mysql documentation, columns of type TIMESTAMP only allow the precision up to seconds..

Any ideas on how I could achieve that?

Answer 1

You can't because the PHP PDO driver doesn't support fractional seconds in timestamps. A work around is to select the timestamp as a string instead so the PDO driver doesn't know its really a timestamp, simply by doing $query->selectRaw(DB::raw("CONCAT(my_date_column) as my_date_column")) however this means you can't use the default select for all fields so querying becomes a real pain. Also you need to override getDateFormat on the model.

// override to include micro seconds when dates are put into mysql.
protected function getDateFormat()
{
    return 'Y-m-d H:i:s.u';
}

Finally in your migration rather than nullableTimestamps, outside of the Schema callback do:

DB::statement("ALTER TABLE `$tableName` ADD COLUMN created_at TIMESTAMP(3) NULL");

Note this example was for 3 decimal places however you can have up to 6 if you like, by changing the 3 to a 6 in two places, in the alter table and in the sprintf and also adjusting the multiplier * 1000 to 1000000 for 6.

Hopefully some day PHP PDO will be updated to fix this, but its been over 5 years and nothings changed so I don't have my hopes up. In case you are interested in the details, see this bug report: http://grokbase.com/t/php/php-bugs/11524dvh68/php-bug-bug-54648-new-pdo-forces-format-of-datetime-fields I found that link in this other answer which might help you more understand the issue: https://stackoverflow.com/a/22990991/259521

PHP is really showing its age lately, and I would consider this issue one of my reasons for considering moving to the more modern Node.js.

Answer 2

Based on malhal's answer, I was able to get fractional timestamp reading to work here. Pasting the answer here for convenience:

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;
    }
}

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 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.