CODEWITHSUNDEEP
phplaraveleloquent
stalwart1014
stalwart1014

Reputation: 471

Laravel eloquent and raw sql return different timestamp

I'm getting different time in the created_at column from laravel eloquent and raw sql. The raw sql gives the correct time while the eloquent is wrong. I've already setup the timezone properly and clear config.

Controller

public function index()
{
    //returns correct time created_at: "2020-12-08 19:44:30"
    $timelines = DB::select('select * from timelines where user_id = ?', [auth()->user()->id]);
    
    //returns wrong time created_at: "2020-12-08T11:44:30.000000Z"
    $timelines = Timeline::where('user_id',auth()->user()->id)->get();

    return $timelines;
}

Model

class Timeline extends Model
{
    use HasFactory;

    protected $guarded = [];

}

Database

{
    Schema::create('timelines', function (Blueprint $table) {
        $table->id();
        $table->integer('user_id')->unsigned()->foreign('user_id')->references("id")->on("users")->onDelete("cascade");
        $table->integer('source_id');
        $table->string('description');
        $table->timestamps();
    });
}

Upvotes: 2

Views: 974

Answers (2)

Abilogos
Abilogos

Reputation: 5030

The Raw Database returns correct time because DBMS time zone is correct.

but in Eloquent, created_at field converts into an Carbon::class instance by default.

and Carbon::class will use timezone configuration of your app to convert to real time.

so change in laravel ./config/app.php :

'timezone' => 'Asia/Kuala_Lumpur',

because i suppose you are in +8 Timezone based on your profile location, and your reporting time log are 8 hours difference from UTC the default time zone config in Laravel.

Upvotes: 3

Rudra
Rudra

Reputation: 714

You can add a mutator to change format returned by eloquent

public function getCreatedAtAttribute($value) {
     return date('Y-m-d H:i:s',strtotime($value));
}

add this function in your Timeline model.

you can also modify function by using carbon methods

for more info you can refer to laravel docs. https://laravel.com/docs/8.x/eloquent-mutators#introduction

Upvotes: 1

Related Questions