1

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.

double-beep
  • 5,031
  • 17
  • 33
  • 41
Sasmita Novitasari
  • 191
  • 3
  • 7

2 Answers2

0

You must set your time zone in the config\app.php file If you have set your time zone inside the file, this will only happen on your side, but if you define it in the config file with return, your problem will be solved.

ex:

'timezone' => 'Asia/Jakarta',

Alireza Mirfakhraei
  • 1
0

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 :)

Mohammad Reza
  • 21
  • 3