Adding timestamps to the SQL raw queries with Laravel Eloquent? Here is how.

Using Laravel Eloquent

When adding the data into the database with Laravel Eloquent, timestamps will normally be inserted automatically if you have declared the timestamps column on the migration script.

And the migration script looks like this:

// create_person_table.php

class CreatePersonTable extends Migration
{
    public function up()
    {
        Schema::create('person', function (Blueprint $table) {
            $table->increments('id');
            $table->string('person_name');

            $table->integer('company_id');
            $table->foreign('company_id')->references('id')->on('companies');

            $table->timestamps();
            $table->softDeletes();
        });
    }

    public function down()
    {
        Schema::dropIfExists('person');
    }
}

and to add a new person from the controller:

// PersonController.php

namespace App\Http\Controllers;

use App\Person;

class PersonController extends Controller
{
    public function store(Request $request)
    {
        $newPerson = new Person;
        $newPerson->person_name = $request->person_name;
        $newPerson->company_id = $request->company_id;
        $newPerson->save();

        return redirect()
            ->route('members.folder.show', $request->id);
    }
}

And the timestamps(created_at & updated_at) will be created for you automatically in the database.

Using SQL Raw Queries

But what if there are some cases where you have to manually insert your data into the database without using the Laravel Eloquent, especially when you have a complicated SQL query?

Let's take a simple example by converting the example above into a SQL query instead:

// PersonController.php

namespace App\Http\Controllers;

use App\Person;

class PersonController extends Controller
{
    public function store(Request $request)
    {
       $person = DB::select(
            DB::raw("INSERT INTO person (person_name, company_id) VALUES (:personName, :companyId);"),
            array(
                "personName" => $request->person_name,
                "companyId" => $request->company_id.
            )
        );

        return $person;
    }
}

FYI: Read this article to understand the best practise when writing raw queries in laravel in order to prevent SQL injection

Since we didn't declare the created_at and updated_at columns on the INSERT section, you will then see the value null on both of the columns in the database.

So how do we add the timestamps manually into our database with the SQL raw query? Do we simply just hardcode the date or?

Solution

CURRENT_TIMESTAMP

First you have to add the created_at and updated_at columns on the INSERT section:

$person = DB::select(
            DB::raw("INSERT INTO person (person_name, company_id, created_at, updated_at) VALUES (:personName, :companyId);"),
            array(
                "personName" => $request->person_name,
                "companyId" => $request->company_id.
            )
        );

and add the CURRENT_TIMESTAMP to the values section:

$person = DB::select(
            DB::raw("INSERT INTO person (person_name, company_id, created_at, updated_at) VALUES (:personName, :companyId, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);"),
            array(
                "personName" => $request->person_name,
                "companyId" => $request->company_id.
            )
        );

Now run your code and add a record. You will then see the timestamps appear on the created_at and updated_at columns in your database!

Blog

Adding timestamps to the SQL raw queries with Laravel Eloquent? Here is how.

Sheary Tan

Using Laravel Eloquent

Using SQL Raw Queries

Solution

Join Our Newsletter. No Spam, Only the good stuff.

Related