Lumen show different time than what is saved is MYSQL DB

user_15493255 0
Viewed 349

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.

lumen laravel timezone mysql
2 Answers

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.

user_154932550

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.

user_13463670
Related