Wrong timestamp in laravel

Question

I am getting the wrong timestamp format/timezone in return from MySQL. In MySQL the time is accurate and correct but when I select it from MySQL I get it like this

2020-03-22T19:00:04.000000Z

However, I have this in my database table

2020-03-22 07:00:02

The query which I am using to fetch results from db is:

Stats::select('created_at','total_cases','recovered','critical','deaths','islamabad'
,'punjab','sindh','kp','balochistan','ajk','gb')->whereDate('created_at', $date)->get();

See if [this](https://stackoverflow.com/questions/930900/how-do-i-set-the-time-zone-of-mysql) is of any help. — El_Vanja, Mar 23 '20 at 00:42

score 1 · Answer 1 · answered Mar 23 '20 at 00:38

1

You have to apply date_format() to print the date returned from the database according to the format you want. Here is an example:

$date=date_create("2020-03-22T19:00:04.000000Z");
echo date_format($date,"Y/m/d H:i:s");
#output 2020/03/22 19:00:04

answered Mar 23 '20 at 00:38

toh19

1,083
10
21

You do realize OP is talking about the times not matching and not the format? – El_Vanja Mar 23 '20 at 00:41
Actually the time matches: apply `date_format()` on the first time given and you will see that is the same time given in the second part of the question – toh19 Mar 23 '20 at 00:51
I am using laravel, and the time I am fetching from database is for public api so I need to fetch it like it is in the DB – Umar Hameed Mar 23 '20 at 01:05
So what is that you want exactly: are you passing the date as a param and you don't know how to format it so it returns a result from the database or you want to display it as your first date shown in your question? – toh19 Mar 23 '20 at 01:10
The PHP time says 19:00:04, while db time says 07:00:02. I don't see how that's a match fixable by formatting. The timezone needs to be amended. – El_Vanja Mar 23 '20 at 08:02

score 1 · Answer 2 · answered Mar 23 '20 at 03:58

1

You can format your date using DATE_FORMAT function of mysql as below:

$data  =  Stats::select(DB::raw('DATE_FORMAT(created_at, "%Y-%m-%d %H:%i:%s") as formatted_dob'), 'total_cases','recovered','critical','deaths','islamabad'
    ,'punjab','sindh','kp','balochistan','ajk','gb')
    ->whereDate('created_at', $date)
    ->get();

Reference:

MySQL -> date and time function -> date_format

answered Mar 23 '20 at 03:58

Sehdev

5,486
3
11
34

This also won't take care of the timezone mismatch, but I salute the formatting done directly in MySQL. – El_Vanja Mar 23 '20 at 13:34

Wrong timestamp in laravel

2 Answers2