CODEWITHSUNDEEP
laraveltimestamplaravel-5
Davuz
Davuz

Reputation: 5286

Why does Laravel 5 auto update my date field?

I want to update a field (status) on the model. I would retrieve from DB and assign a new value to status column. But after the model saved, I saw another date field (published_at) also changed to same as updated_at. The action run when user click on a link as http://localhost/dashboard/gallery/publish/1.

I don't know why the published_at updated auto and same as updated_at?

Here is Controller code:

<?php 
class GalleryController extends Controller
{
    /**
     * Approve to publish the gallery on the web.
     *
     * @param  int  $id
     * @return Response
     */
    public function getPublish($id)
    {
        $gallery = Gallery::whereId($id)->firstOrFail();       
        $gallery->status = Gallery::STT_PUBLISH;
        $gallery->save();
        return redirect( route('backend::gallery.edit',[$gallery->id]) )->with('status', 'Done');
    }
}
?>

and Gallery Model:

<?php
namespace App\Models;

use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\SoftDeletes;

class Gallery extends Model
{
  use SoftDeletes;
  protected $table = 'gallery';
  protected $fillable = ['title', 'slug', 'content', 'category_id', 'type'];
  protected $guarded = ['published_at','creator_id','thumbnail'];
  protected $dates = ['deleted_at', 'published_at'];
}
?>

and Migration func:

public function up()
    {
        Schema::create('gallery', function (Blueprint $table) {
            $table->increments('id')->unsigned();
            $table->string('slug')->unique();   
            $table->tinyInteger('type')->unsigned();
            $table->integer('category_id')->unsigned()->default(0); 
            $table->string('thumbnail');   
            $table->string('title');   
            $table->text('content');
            $table->integer('status')->unsigned()->default(0);
            $table->timestamp('published_at');
            $table->integer('creator_id')->unsigned();
            $table->timestamps();            
            $table->index(['slug']);
            $table->softDeletes();
        });
        Schema::table('gallery', function (Blueprint $table) {
            $table->foreign('category_id')->references('id')->on('category');
            $table->foreign('creator_id')->references('id')->on('users');
        });
    }

UPDATE

I see this config in Migration function makes problem, here: $table->timestamp('published_at');

And this statement creates SQL like that:

CREATE TABLE `gallery` ( 
    ...
     `published_at` Timestamp NOT NULL ON UPDATE CURRENT_TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    ...
)

So, how to set up a timestamp field, which is not auto update on current time?

UPDATE 2

Done, I modify the migration, just use dateTime instead of timestamp.

Use it, $table->dateTime('published_at');, this statement will not auto update on CURRENT_TIMESTAMP.

Upvotes: 17

Views: 12844

Answers (6)

Abderrahman Fodili
Abderrahman Fodili

Reputation: 143

this drove me crazy it's not a Laravel thing just go to your PHP my admin -> database -> table -> structure you will find that this column has an Extra attribute of "on update current_time_stamp" Click change then go to attributes change it to the empty option and save
enter image description here

Upvotes: 0

cytsunny
cytsunny

Reputation: 5030

This is not a problem of Laravel, but a "feature" of MySQL.

According to the reference manual MySQL 5.6

TIMESTAMP and DATETIME columns have no automatic properties unless they are specified explicitly, with this exception: If the explicit_defaults_for_timestamp system variable is disabled, the first TIMESTAMP column has both DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP if neither is specified explicitly.

The official site removed reference manual for older version, but when I google for the problem, from the sources that also mention about the problem, it seems that the feature was there for older version of MySQL too.

Therefore, instead of changing $table->timestamp('published_at'); to $table->dateTime('published_at');, you may also try to solve the problem by

  1. giving a default value to the timestamp. e.g. $table->timestamp('published_at')->default(DB::raw('CURRENT_TIMESTAMP')); or $table->timestamp('published_at')->nullable() (where the default value is null)
  2. moving the line later then $table->timestamps(); so that pulibhsed_at is not the firs timestamp. The default created_at and updated_at is nullable (i.e. having default value null) an will not trigger the "feature"

Upvotes: 3

ICT Diensten 072
ICT Diensten 072

Reputation: 21

There is no need to change migration to 

$table->dateTime('published_at');

Just make it

$table->timestamp('published_at')->nullable();

To display the date, you can use something like this in your model

    public function getShortDateTimeFromTimestamp($value)
    {
        if(isset($value))
        {
            $date = date_create();

            date_timestamp_set($date, $value);

            return date_format($date, 'd-m-Y H:i');
        }
        return false;
    }

In the view

        Published {{ $post->getShortDateTimeFromTimestamp($post->published_at) }}

To use current timestamp when storing/updating the resource, you can use

published_at = \Carbon\Carbon::now();

Upvotes: 1

Tobias Schulz
Tobias Schulz

Reputation: 121

This Post is old but for anyone who got this problem here is the simplest solution. In your migration the timestamp field just has to be nullable. Than there will no auto update trigger on this field.

$table->timestamp('published_at')->nullable();

No need for using $table->dateTime() instead of $table->timestamp().

Upvotes: 12

Gabriel Ferreira Rosalino
Gabriel Ferreira Rosalino

Reputation: 559

Alternative solution is to create a migration that run this query to remove the ON UPDATE trigger of the column:

ALTER TABLE queued_posts CHANGE scheduled_publish_time scheduled_publish_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP

Upvotes: -1

Joseph
Joseph

Reputation: 2712

It looks like what is happening is that MySQL (or whichever database you are using) is updating the date, not Laravel.

You need to change:

$table->timestamp('published_at');

to:

$table->dateTime('published_at');

Then do a php artisan migrate:refresh to rollback and recreate your tables.

Upvotes: 15

Related Questions