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?
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();