Search code examples
phpmysqldatabaselaravelscheduler

Incorrect TIMESTAMP value Laravel DB

I have a laravel project set up on localhost where I created a command in the scheduler which is the following:

DB::table('relic')->where('created_at', '<', 'NOW() - INTERVAL 1 MONTH')->delete();

I tested running the scheduler command through artisan and I get this output:

Illuminate\Database\QueryException : SQLSTATE[HY000]: General error: 1525 Incorrect TIMESTAMP value: 'NOW() - INTERVAL 1 MONTH' (SQL: delete from `relic` where `created_at` < NOW() - INTERVAL 1 MONTH)

I tested the commands
SELECT * FROM relic WHERE created_at < CURRENT_TIMESTAMP() - INTERVAL 1 MONTH;
and
SELECT * FROM relic WHERE created_at < NOW() - INTERVAL 1 MONTH;
in mysql itself and both worked properly.

What am I missing?

Solution

  • The Carbon library is included with Laravel, and it is the "standard" way to deal with dates and times. You can write your query as follows:

    DB::table('relic')
  ->where('created_at', '<', \Carbon\Carbon::now()->subMonth())
  ->delete();

    If you would like to use your syntax you can do so with a whereRaw statement like so:

    DB::table('relic')
  ->whereRaw('created_at < NOW() - INTERVAL 1 MONTH')
  ->delete();