Laravel return different timestamp from database using eloquent

Question

I'm using

Laravel Framework 9.37.0

PHP 8.1.10

Using postman for get and add data

I try to get data from MySQL database using eloquent

$investment = Investment::where('user_id', $user_id)->orderBy('created_at', 'DESC')->get();
return $investment;

That code return different timestamp from my database

This is timestamp from my database

created_at = 2022-11-01 11:26:55

updated_at = 2022-11-01 11:26:55

But this is timestamp from my postman

created_at = 2022-11-01 04:26:55

updated_at = 2022-11-01 04:26:55

Result is different 7 Hours. I Already set my config/app.php "timezone" to "Asia/Jakarta". I try to set in UTC and return different timestamp too

This is my model

class Investment extends Model
{
    use HasFactory;

    protected $casts = [
        'created_at' => 'datetime:Y-m-d H:i:s',
        'updated_at' => 'datetime:Y-m-d H:i:s',
    ];

    public function user()
    {
        return $this->belongsTo(User::class,'user_id');
    }

    public function plan()
    {
        return $this->belongsTo(PlanList::class, 'plan_id');
    }
}

Timestamp is correct when I add data, it is only wrong when I GET data. But if using DB::table()... timestamp is correct.

Can someone explain why this returns a different timestamp when using eloquent?

I try to change timezone to UTC but still return different timestamp.

Answer 1

let me explain what happen.

When you return eloquent instance to a JSON response (API or something else), the Carbon instance will always convert in the ISO-8601 string and given in GMT timezone, as it's the standard and very recommended way to exchange date information between systems.

First set timezone and then try this ways:

1. Using DATE_FORMAT

$investment =Investment::select('user_id' , DB::raw('DATE_FORMAT(created_at, "%Y-%m-%d %H:%i:%s") as created_on'))->get(); 
return $investment;

NOTE: don't use created_at as the alias name, because the result will be the same!

2. Using serializeDate in your model:

class Investment extends Model{

use HasFactory;
use DateTimeInterface;

protected function serializeDate(DateTimeInterface $date)
{
    return $date->format('Y-m-d H:i:s');
}

public function user()
{
    return $this->belongsTo(User::class, 'user_id');
}

public function plan()
{
    return $this->belongsTo(PlanList::class, 'plan_id');
}
}

Note: #2 is a clear way and doesn't need to change the column's name!

I hope these ways help you :)