my current timezone is Asia/Karachi and when i retrieve table data from mysql it gives me (actual time – 5 hours)
for eg:
mysql column value: ‘2021-04-21 01:34:57’
and when i retrieve from laravel DB::table(‘table_name’)->get()->toArray();
it gives following :2021-04-20 20:34:57
and changing my timezone doesn’t change anything either.
so is there something else i’m missing ?
btw i created following route for checking my current timezone
$app->get('/timezone', function () {
return date_default_timezone_get();
});and it gives same what is saved in my env i.e(Asia/Karachi) but this doesn’t change the result i get from mysql even if i change it to some other timezone like Asia/Kolkata.
I tried researching on this but didn’t get any suitable answer.
Answers:
Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.
Method 1
i tried alot of things to solve this issue like
adding env variables APP_TIMEZONE="Asia/Karachi" (did not work)
also i tried adding DB_TIMEZONE="+05:00" (this worked in my local but not on stagging)
finally i saw somewhere that someone else had exact same issue resolving this and he did it by adding the hours using carbon which also worked for me
'posted_at' => Carbon::parse($record->posted_at)->addHours(5)->toDateTimeString(),
i know this is not the optimal solution but this was the only solution that worked so i had to go with it.
Method 2
https://dev.mysql.com/doc/refman/8.0/en/datetime.html
If you store a TIMESTAMP value, and then change the time zone and retrieve the value, the retrieved value is different from the value you stored. This occurs because the same time zone was not used for conversion in both directions. The current time zone is available as the value of the time_zone system variable.
As far as I know, you have a couple things to consider:
The timezone of your Laravel/Lumen app
This can be found found in config/app.php -> timezone.
The timezone of your MySQL server
You can retrieve this using:
mysql> SELECT @@global.time_zone, @@session.time_zone;
The timezone of the server itself
If the mySQL query above returns SYSTEM, it means it uses the system timezone setting, which for Debian/Ubuntu etc you can check using:
cat /etc/timezone
From my experience, you can most often leave the system/mySQL timezones intact and only set the correct timezone in your Laravel config. I know it caused me a headache the first time I had to figure out how this actually worked.
All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0