DEV Community

Sheary Tan
Sheary Tan

Posted on

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');
    }
}

Enter fullscreen mode Exit fullscreen mode

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);
    }
}

Enter fullscreen mode Exit fullscreen mode

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;
    }
}
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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.
            )
        );
Enter fullscreen mode Exit fullscreen mode

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.
            )
        );
Enter fullscreen mode Exit fullscreen mode

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!

Top comments (0)