[5.2] Update column created_at, updated_at

[PheRum]

Hello.
When working with Elokuent that it updates column created_at, updated_at both when creating and updating.

When creating this is true, but why update created_at column every time I do something it updated?
In this case, it makes use of a useless $tablet->timestamps(); because you can not fix the field of creating records

Say it is a bug or by design?

or tell me how to leave the field after creating created_at intact?

[PheRum]

attribute CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP need to apply for the field updated_at
users table

Schema::create('users', function (Blueprint $table)
        {
            $table->increments('id');
            $table->string('name', 32);
            $table->string('email')->unique();
            $table->string('password', 60);
            $table->rememberToken();
            $table->timestamps();
        });

[GrahamCampbell]

This is by design.

[PheRum]

@GrahamCampbell I do not quite understand, this is a mistake or a well conceived?

[GrahamCampbell]

I'm sorry, I don't quite understand what it is you're saying is incorrect here?

[PheRum]

Please look carefully at the post #11518 (comment)

there migration, which creates a column with dates

$table->timestamps();  // => created_at and updated_at column

after migration to created_at field is added to the attribute CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP and it is wrong

example:
There is an article. I use the created_at column value as the publication date, but when updating the counter view the created_at column value is updated to the current time. But should be updated updated_at field

Controller

public function show ($id)
    {
        $news = News::findOrFail($id);
        $news->views++;
        $news->save();

        return view('news.show', compact('news'));
    }

Similarly, if the migration was to create a field with the timestamp and attribute CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP then the created_at column value will be created without attribute CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

in laravel 5.1 of the $table->timestamps(); were created alike:

  `created_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `updated_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00'

Perhaps the best idea is to return as

[GrahamCampbell]

Are you sure this change is only on 5.2? I can't see any differences between the current 5.1 dev and 5.2 dev?

[GrahamCampbell]

What database are you using please?

[GrahamCampbell]

I've investigated this for you, and this breaking change was made in 5.2, and it was definitely intentional.

[PheRum]

I deliberately rolled round of audit on its project to ensure that the error was confirmed. the only problem is to laravel 5.2

presumably there https://github.com/laravel/framework/blob/5.2/src/Illuminate/Database/Schema/Grammars/MySqlGrammar.php#L532

DataBase Mysql

please return the creation of fields with attributes that were previously

`created_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
   `updated_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00'

[GrahamCampbell]

https://www.diffchecker.com/yahgywri

[GrahamCampbell]

Yep, we changed the mysql grammar.

[GrahamCampbell]

4223167

[GrahamCampbell]

Ping @taylorotwell.

[PheRum]

results of creating a table of migration

Schema::create('users', function (Blueprint $table)
{
    $table->increments('id');
    $table->string('name', 32);
    $table->string('email')->unique();
    $table->string('password', 60);
    $table->rememberToken();
    $table->timestamps();
});

Schema::create('users', function (Blueprint $table)
{
    $table->increments('id');
    $table->string('name', 32);
    $table->string('email')->unique();
    $table->string('password', 60);
    $table->rememberToken();
    $table->timestamp('new_time');
    $table->timestamps();
});

[taylorotwell]

I'm unable to recreate this... :/

[taylorotwell]

I'm on Laravel 5.2.3 and get the following declaration:

  `created_at` timestamp NOT NULL,
  `updated_at` timestamp NOT NULL,
  PRIMARY KEY (`id`),

[PheRum]

I use laravel 5.2.5 and MySql Server 10.1.9-MariaDB and always get such a result (((

  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00'

[mikerockett]

I am able to reproduce this from the standard migrations (5.2.5)

Users

Resets

[ozgurkaragoz]

Hi Taylor,

I am using Laravel 5.2.5 too and i have a same problem.

I was using 5.1.27 and that problem is not exist. I upgraded to 5.2.5 then run php artisan migrate:refresh and created_at field properties changed.

Migration command:

$table->timestamps();

SQL:

created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
updated_at timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
deleted_at timestamp NULL DEFAULT NULL

[vlakoff]

I can reproduce the issue (Laravel 5.2.5, MySQL 5.5.45, apparently not affected by strict mode setting).

Interestingly, the SQL code generated by Laravel looks fine:

array(2) {
  [0]=>
  string(322) "create table `users` (`id` int unsigned not null auto_increment primary key, `name` varchar(32) not null, `email` varchar(255) not null, `password` varchar(60) not null, `remember_token` varchar(100) null, `created_at` timestamp not null, `updated_at` timestamp not null) default character set utf8 collate utf8_general_ci"
  [1]=>
  string(60) "alter table `users` add unique `users_email_unique`(`email`)"
}

So it's MySQL who adds the default values and the ON UPDATE CURRENT_TIMESTAMP extra.
To those who haven't yet, would you state your MySQL versions please?

See: MySQL 5.5 - Automatic Initialization and Updating for TIMESTAMP

[mikerockett]

@vlakoff, I'm on 5.6.21.

[mikerockett]

Any news on this?

Using

$table->timestamp('created_at')->default(0);
$table->timestamp('updated_at')->default(0);

in the meantime.

[GrahamCampbell]

Does this work on mysql 5.7, since laravel 5.2 only officially supports mysql 5.7 now?

[mikerockett]

@GrahamCampbell - Was not aware of this. Should the docs not be updated?

[mikerockett]

@GrahamCampbell - Just upgraded my server, which now uses MariaDB 10.1.9. Issue persists.

[jgrossi]

@vlakoff I had no MySQL error using $table->timestamp('created_at')->useCurrent();. :-)

[wojciechmruk]

They solved this problem. Just composer update.

[Pezhvak]

@jarodrejestracyjny i don't see any new commits, what do you mean by 'they solved this problem?'

[imtiazPabel]

I think ->nullableTimestamps() instead of ->timestamps() is the better solution right now.And we can wait for next release,to got a good news.

[arashlaghaeiyahoo]

hi every body i fixed problem create_at please do it: vendor/laravel/framework/srs/illuminate/database/schema/blueprint.php line:792

public function timestamps()
{
$this->timestamp('updated_at');

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

}
please do it exactly i write. and fix this problem.

[tobia]

I don't quite understand the status of this bug.

I just tried the two migrations from the default install (using both 5.2.22 and 5.2.x-dev) on MySQL 5.5 (which is still the default in Debian stable and most other distributions) and the result is this:

CREATE TABLE `users` (
    ...
    `created_at` timestamp NULL DEFAULT NULL,
    `updated_at` timestamp NULL DEFAULT NULL,
    ...
);

CREATE TABLE `password_resets` (
    ...
    `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    ...
);

All 3 columns came out wrong.

@GrahamCampbell I think this bug should be reopened.

[rap2hpoutre]

@arashlaghaei if you think it solves the problem, you can open a pull request. Do not forget to explain in it why you have done this fix, what does it fix, etc.

[tobia]

I just tried performing the same migrations on PostgreSQL and the timestamp columns are created without any option or trigger to auto-populate them. They are just plain timestamp columns with NULL default value:

CREATE TABLE users (
    ...
    created_at timestamp(0) without time zone,
    updated_at timestamp(0) without time zone,
    ...
)

CREATE TABLE password_resets (
    ...
    created_at timestamp(0) without time zone NOT NULL
    ...
)

So are timestamp columns supported by any driver?

[marsnys]

Over a year since this issue has been opened.
Currently running Laravel v5.3.28 in combination with MySQL 5.6.33, and still encountering the same issues.

Using the default function for adding created_at and updated_at fields in my tables, it leaves me with a 'on update CURRENT_TIMESTAMP' on the first encountered timestamp of the table. Which isn't even close to the right one.

How, after all this time, is this still an issue?

EDIT: The issue only occurs when using another Column of the Type 'TIMESTAMP' besides the 'updated_at' and 'created_at' ones in the table.

[Nikita240]

I'm having a bit of a different issue here.

I have project that started out in 5.1, and has since been upgraded to 5.3.

Most of the tables were created in 5.1, so they have the old "not nullable" timestamps(). I also have some tables which were created in 5.3, and they have the new "nullable" timestamps(), and they work fine.

The problem is, when I try to run a migration to ALTER, the OLD pre-5.2 tables.

                                                                                    
  [Illuminate\Database\QueryException]                                              
  SQLSTATE[42000]: Syntax error or access violation: 1067 Invalid default value fo  
  r 'created_at' (SQL: alter table `products` add `brand_id` int unsigned null)     
                                                                                    

                                                                                    
  [Doctrine\DBAL\Driver\PDOException]                                               
  SQLSTATE[42000]: Syntax error or access violation: 1067 Invalid default value fo  
  r 'created_at'                                                                    
                                                                                    

                                                                                    
  [PDOException]                                                                    
  SQLSTATE[42000]: Syntax error or access violation: 1067 Invalid default value fo  
  r 'created_at'  

Even though the fields I'm changing have nothing to do with timestamps, I'm unable to alter the table.

Additionally, I cannot migrate the timestamps to the new "nullable" ones because:

Not really sure what to do about this.

[antonkomarev]

@Nikita240 What version of MySQL are you using?

[Nikita240]

@a-komarev v5.6.27

[Nikita240]

Since timestamps() actually functions correctly and this is more of an "upgrade" issue, I decided to put this in a separate issue.

#17313

[rahulkhannagar]

In your migration file please define first the "update_at" column then define the "create_at" column. In Laravel first coming column with default time_stamp value automatically set the attribute "on update". Now your migration file looks like below:

$table->timestamp('update_at');
$table->integer('created_by');
$table->timestamp('creation_at');

[Tofandel]

Wow still had this issue on mysql5.7 and laravel 7.x...

Just one timestamp in the schema

$table->timestamp('date')

Makes it CURRENT_TIMESTAMP on update.. I was very surprised because this behavior overwrites data.. This should not happen by default, only if explicitly enabled

Eg

$table->timestamp('date')->onUpdate('CURRENT_TIMESTAMP');

[kwk9892]

You can try using this

$table->timestamp('date_modified')->useCurrentOnUpdate()->nullable();

[LowSociety]

Four and a half years later I ended up here because of the on update CURRENT_TIMESTAMP issue. I'll try to bring some light onto what is happening and why it's kind of my fault, not at all Laravel's fault and somewhat MySQL's fault:

I have a migration that kind of looks like this:

$table->id();
...
$table->timestamp('expires_at');

Laravel will compile this into the correct SQL:

create table... (`id` bigint unsigned not null auto_increment primary key, `expires_at` timestamp not null) ...

MySQL has this little thing in their documentation though:

The first TIMESTAMP column in a table, if not explicitly declared with the NULL attribute or an explicit DEFAULT or ON UPDATE attribute, is automatically declared with the DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP attributes.

That's why on update CURRENT_TIMESTAMP is added to the column. This behavior can apparently be turned off by turning on the explicit_defaults_for_timestamp flag in MySQL. I'm not doing that.

The real problem for me is that I am using the timestamp instead of the datetime column type though. The main difference seems to be how MySQL persists timezones as UTC and can convert them back on a per-connection basis. My app already expects UTC when the data is retrieved though, so I don't have to worry about that. I'll switch to datetime.

Besides, this thing from the MySQL documentation kind of does worry me:

[...] the range for TIMESTAMP values is '1970-01-01 00:00:01.000000' to '2038-01-19 03:14:07.999999'

[warmwhisky]

I cannot for the life of me find out how to add the updated at column with "on update CURRENT_TIMESTAMP"

Its just so much quicker to use PHPmyadmin and do it manu8ally instead of trying 10 different ways, creating 10 different migration files and deleting the darn table 10 times only to find I still cannot add an updated_at column with auto update.

How is this not in the documentation?

[Esirei]

I cannot for the life of me find out how to add the updated at column with "on update CURRENT_TIMESTAMP"

Its just so much quicker to use PHPmyadmin and do it manu8ally instead of trying 10 different ways, creating 10 different migration files and deleting the darn table 10 times only to find I still cannot add an updated_at column with auto update.

How is this not in the documentation?

$table->timestamp('created_at')->nullable();
$table->timestamp('updated_at')->default(DB::raw('CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP'));

[ARehmanMahi]

Set timestamps automatically update on create/update Laravel 8

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

I would not suggest auto population at database end.
DB time could be different than web/app server causing mismatch.
App timezone setting can be changed at some point, also causing difference.
Better to handle dates from application, like Laravel is doing by default as well.

[kingmaker-agm]

@LowSociety was right. It was an unwanted unintended default on the MySQL database side.

It screwed me up big time and had wasted 2 days to debug the Issue.
#11518 (comment)