DEV Community

Sheary Tan
Sheary Tan

Posted on

1 2

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!

Image of Wix Studio

2025: Your year to build apps that sell

Dive into hands-on resources and actionable strategies designed to help you build and sell apps on the Wix App Market.

Get started

Top comments (0)

Image of AssemblyAI

Automatic Speech Recognition with AssemblyAI

Experience near-human accuracy, low-latency performance, and advanced Speech AI capabilities with AssemblyAI's Speech-to-Text API. Sign up today and get $50 in API credit. No credit card required.

Try the API

👋 Kindness is contagious

Dive into an ocean of knowledge with this thought-provoking post, revered deeply within the supportive DEV Community. Developers of all levels are welcome to join and enhance our collective intelligence.

Saying a simple "thank you" can brighten someone's day. Share your gratitude in the comments below!

On DEV, sharing ideas eases our path and fortifies our community connections. Found this helpful? Sending a quick thanks to the author can be profoundly valued.

Okay