Sql query syntax inside laravel migration

  laravel, sql

I have a column start_time of type date and a column duration_temp of type Integer and I made a new column duration of type timestamp I want to fill duration column with those values:
only the date without hours, minutes and seconds from start_time and add the value of "duration_temp" as minutes to this date.
example: start_time = 2021-08-17 13:28:34
and duration_temp = 3
then I want duration = 2021-08-17 00:00:00 +3(minutes) = 2021-08-17 00:03:00.

I tried to do it this way

public function up()
        Schema::table('testassignment', function (Blueprint $table) {
            DB::statement('UPDATE testassignment SET duration = Select DATEADD(mi, + duration_temp*60,DATEADD(hh, select DATEDIFF(hh, 0, start_time), 0))');

but I’m getting the error

Syntax error or access violation: 1064 You have an error in your SQL

I know that there is something wrong with the syntax of the code but I couldn’t figure it out, any tip would be appreciated.
I’m using DATEADD function twice in the code, the inner one to extract only the date from start_time and the outer DATEADD function is to add the minutes that in duration_temp to the extracted date from start_date

Source: Laravel

Leave a Reply