Migration to create a new expiry column with the default date one year from now

I've seen a few threads on how to set the default of a timestamp column to the current datetime using CURRENT_TIMESTAMP. However, I am looking for how to set the default to the CURRENT_TIMESTAMP + 1 year.

I tried this but didn't have any success:

Schema::table('users', function (Blueprint $table) {
            $table->timestamp('expires')->default(DB::raw('NOW() + INTERVAL 1 YEAR'))->after('updated_at');
        });

Received this error:

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '+ INTERVAL 1 YEAR after updated_at' at line 1

Any idea on how to set the default for this column to be the one year from the current date?

sherwinmdev

8 years ago

Level 6

@cvanderlinden

i would do it during the record creation and/or update using carbon

$expire_date = Carbon::now()->addyear();

where $expire_date takes the current datetime value and adds a year. i haven't tried the code but it may spit out a different format not matching the database column but you can use carbon to set the format as well.

you can also set triggers in the database but i haven't done that in a while. many ways to do it.

tykus

8 years ago

Level 104

MySQL doesn't support what you're looking for:

With one exception, the default value must be a constant; it cannot be a function or an expression. This means, for example, that you cannot set the default for a date column to be the value of a function such as NOW() or CURRENT_DATE. The exception is that you can specify CURRENT_TIMESTAMP as the default for a TIMESTAMP or DATETIME column. See Section 11.3.5, “Automatic Initialization and Updating for TIMESTAMP and DATETIME”.

source https://dev.mysql.com/doc/refman/5.7/en/create-table.html

robrogers3

8 years ago

Level 37

@tykus rocks!

@w1n78 is correct.

@robrogers3 you are ok.

the solution is easy. class User { protected static function boot() { parent::boot();

    static::updated(function ($user) {
        $user->update(['expires' => Carbon::now()->addYear()->toDateTimeString()])
    });

    static::created(function ($thread) {
        $user->update(['expires' => Carbon::now()->addYear()->toDateTimeString()])
    });
}

}

Boom you got your own trigger.

2 likes

AegisOrta

4 years ago

Level 1

@robrogers3 Hi robrogers3, I was searching for this solution for sometime finally found your comment, could you please tell me where to put this code?

Please or to participate in this conversation.