How Can I Set the Default Value of a Timestamp Column to the Current Timestamp with Laravel Migrations?

by

Given it’s a raw expression, you should use DB::raw() to set CURRENT_TIMESTAMP as a default value for a column:

$table->timestamp('created_at')->default(DB::raw('CURRENT_TIMESTAMP'));

This works flawlessly on every database driver.

As of Laravel 5.1.25 (see PR 10962 and commit 15c487fe) you can now use the new useCurrent() column modifier method to achieve the same default value for a column:

$table->timestamp('created_at')->useCurrent();

Back to the question, on MySQL you could also use the ON UPDATE clause through DB::raw():

$table->timestamp('updated_at')->default(DB::raw('CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP'));

Again, as of Laravel 8.36.0 (see PR 36817) you can now use the new useCurrentOnUpdate() column modifier method together with the useCurrent() modifier to achieve the same default value for a column:

$table->timestamp('updated_at')->useCurrent()->useCurrentOnUpdate();

Gotchas

  • MySQL

    Starting with MySQL 5.7, 0000-00-00 00:00:00 is no longer considered a valid date. As documented at the Laravel 5.2 upgrade guide, all timestamp columns should receive a valid default value when you insert records into your database. You may use the useCurrent() column modifier (from Laravel 5.1.25 and above) in your migrations to default the timestamp columns to the current timestamps, or you may make the timestamps nullable() to allow null values.

  • PostgreSQL & Laravel 4.x

    In Laravel 4.x versions, the PostgreSQL driver was using the default database precision to store timestamp values. When using the CURRENT_TIMESTAMP function on a column with a default precision, PostgreSQL generates a timestamp with the higher precision available, thus generating a timestamp with a fractional second part – see this SQL fiddle.

    This will led Carbon to fail parsing a timestamp since it won’t be expecting microseconds being stored. To avoid this unexpected behavior breaking your application you have to explicitly give a zero precision to the CURRENT_TIMESTAMP function as below:

      $table->timestamp('created_at')->default(DB::raw('CURRENT_TIMESTAMP(0)'));

    Since Laravel 5.0, timestamp() columns has been changed to use a default precision of zero which avoids this.

Thanks to @andrewhl for pointing out the Laravel 4.x issue in the comments.

Thanks to @ChanakaKarunarathne for bringing out the new useCurrentOnUpdate() shortcut in the comments.

More Related Contents:

  1. Laravel 5 PDOException Could Not Find Driver
  2. Laravel Check If Related Model Exists
  3. Laravel 5.2 – Use a String as a Custom Primary Key for Eloquent Table becomes 0
  4. How to bind parameters to a raw DB query in Laravel that’s used on a model?
  5. How to change / Custom password field name for Laravel 4 and Laravel 5 user authentication [duplicate]
  6. belongsToMany relationship in Laravel across multiple databases
  7. Laravel 5.4 on PHP 7.0: PDO Exception – Could not find driver (MySQL)
  8. Laravel Eloquent get results grouped by days
  9. Change name of Laravel’s created_at and updated_at
  10. SQLSTATE[HY000] [2002] php_network_getaddresses: getaddrinfo failed: nodename nor servname provided, or not known
  11. How to create a database-driven multi-level navigation menu using Laravel
  12. Eloquent groupBy make “SQLSTATE[42000]” with valid SQL query in Laravel 5.3
  13. find in set in laravel ? example
  14. Laravel 5 – redirect to HTTPS
  15. Laravel : Syntax error or access violation: 1055 Error
  16. How to validate array in Laravel?
  17. Laravel orderBy on a relationship
  18. How to alias a table in Laravel Eloquent queries (or using Query Builder)?
  19. MVC (Laravel) where to add logic
  20. Handling expired token in Laravel
  21. Ajax post request in laravel 5 return error 500 (Internal Server Error)
  22. Laravel named route for resource controller
  23. Laravel $q->where() between dates
  24. Laravel nested relationships
  25. ErrorBag is always empty in Laravel 5.2
  26. Laravel 5.2 not reading env file
  27. getting the value of an extra pivot table column laravel
  28. disable csrf in laravel for specific route
  29. Laravel 5 Clear Views Cache
  30. Create a Insert… Select statement in Laravel

Leave a Comment