Laravel eloquent and raw sql return different timestamp

user_10201641 0
Viewed 466

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();
    });
}
eloquent laravel php
2 Answers

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.

user_92876280

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

user_129872350
Related