Adding timestamps to the SQL raw queries with Laravel Eloquent? Here is how.
Tags: laravel, javascript, database, webdev
Published: 2019-06-22 16:33:17

---
title: Adding timestamps to the SQL raw queries with Laravel Eloquent? Here is how.
published: true
description: To understand how to add the timestamps when inserting data with SQL raw queries
tags: laravel, javascript, database, webdev 
---
##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:

```php
// 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:

```php
// 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:

```php
// 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](https://fideloper.com/laravel-raw-queries) 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##
```php
CURRENT_TIMESTAMP
```

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

```php
$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:

```php
$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!