I have been trying to update one column in a table by adding a 5 YEAR interval to another column, and always get syntax error exception. The following is my PHP script:
AppModelsMyModel::where('cl1', $cl1Value)
->where('cl2', ">=", $startingFrom)
->where('cl2', '<=', $endingAt)
->update([
'cl3' => DB::raw('date_add(cl2 + INTERVAL 5 YEARS)')
]);
cl1 is of data type int, cl2 and cl3 are of data type timestamp. #cl1Value
, $startingFrom
and $endingAt
are passed from other parts of the script. Ad pointed out in the answer below, there is an syntax error related to date_add()
, but even with that fixed, I still similar error message. There seems to be something wrong with how I passed the number 5.
The following is error message when I ran this script.
IlluminateDatabaseQueryException : SQLSTATE[42601]: Syntax error: 7
ERROR: syntax error at or near “5” LINE 1: … “cl3” = date_add(cl2 +
INTERVAL 5 YEARS) w…
^ (SQL: update “my_models” set “cl3” = date_add(cl2 + INTERVAL 5 YEARS) where
“cl1” = 630 and “cl2” >= 2024-09-01 00:00:01 and “cl2” <= 2024-12-15
00:00:00)
Could someone advise what I did wrong here? If I run the generated SQL script or other variant such as the following in mysql console, I don’t get syntax error.
->update(['cl3' => DB::raw('cl2 + INTERVAL 5 YEARS')])
UPDATE my_models SET cl3 = cl2 + INTERVAL 5 YEAR WHERE cl2 >= '2024-09-01 00:00:00' AND cl2 <= '2024-12-15 00:00:00' AND cl1 = 630;
Query OK, 0 rows affected (0.003 sec)
Rows matched: 0 Changed: 0 Warnings: 0
The only difference I can see is the timestamp string is escaped in the script I entered into mysql console.
I am running this from Laravel v6 + MariaDB v10.11.10.
As you are using MariaDB, so you should use TIMESTAMPADD which is a MariaDB-Specific Alternative. MariaDB provides a function TIMESTAMPADD to perform such operation:
Amend your codes like this –
AppModelsMyModel::where('cl1', $cl1Value)
->where('cl2', '>=', $startingFrom)
->where('cl2', '<=', $endingAt)
->update(['cl3' => DB::raw('TIMESTAMPADD(YEAR, 5, cl2)')]);
The cause of the problem turned out to be rather silly, MariaDB doesn’t accept temporal interval unit in plural form. Once I changed the relevant line to the following:
->update(['cl3' => DB::raw('cl2 + INTERVAL 5 YEAR')])
The script can run without problem against MariaDB v10.3, 10.5, 10.11. Just some random test setup I have handy.
If the PHP script will be used with PostgreSQL at some point, number of interval needs to be escaped with single quotation mark. The following line was tested working for both MariaDB v10.x and PostgreSQL v16.
->update(['cl3' => DB::raw("cl2 + INTERVAL '5' YEAR")])
I briefly went through MariaDB and MySQL documentation on temporal interval units. There seems to be no mention that unit cannot be expressed in plural form. But at least plural forms don’t work on my hands. I guess the requirement is implied.
I have been switching between PHP and SQL scripts, because both 5 YEARS
and 5 YEAR
work just fine with strtotime()
, then I conveniently used 5 YEAR
when testing SQL script in console. Sorry to waste time of everyone involved. Hopefully this note will help someone in the future.
EDIT: Expression should be ‘INTERVAL 5 YEAR’ and not ‘INTERVAL 5 YEARS’. Fixed below.
The syntax of the function date_add is incorrect. Replace the plus sign for a comma in the function, like this:
AppModelsMyModel::where('cl1', $cl1Value)
->where('cl2', ">=", $startingFrom)
->where('cl2', '<=', $endingAt)
->update([
'cl3' => DB::raw('date_add(cl2, INTERVAL 5 YEAR)')
]);
2