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