Laravel and MySQL timezone

In

config/app.php 

'timezone' => 'UTC',

The Laravel timezone configuration unfortunately sets PHP timezone only and has no effect on the underlying MySQL instance.

@juhavehnia - have you found a solution for this? I've been dealing with this for a while.

Even if you force set the database connection to a timezone for a user using:

DB::update('SET time_zone = ?', [$request->user()->timezone]);

When you retrieve a model resource via the API, it returns the datetime string with the correct time for the timezone, but the API's data resource object says it's in UTC and is wrong.

There needs to be a way to update the underlying MySQL instance's timezone.

Is the user()->timezone a named timezone, or an offset? In mysql docs, it says:

Named time zones can be used only if the time zone information tables in the mysql database have been created and populated

The Laravel timezone configuration unfortunately sets PHP timezone only and has no effect on the underlying MySQL instance.

that's right .. but that's also a good thing :)

always expect UTC at your database level and the world becomes much easier.

you can format or convert the date in your app however you like, after you've retrieved it from the database.

I agree with @Helmchen. Convert when saving or retrieving using carbon. Everything stored as UTC in the db. It makes life easier and more straightforward.

@wesmahler I have not... I put it on the back burner hoping the community would know answer to this....

So did you also set the laravel timezone dynamically when you tested? That should force any resource object timezone to be the same. I need to play around with this a bit more. Also did you figure out a place where to set the MySQL timezone transparently so it is applies to all DB connections? In my previous apps this was done right after we know user is authenticated and can get to user’s timezone.

The only drawbacks to this approach are:

1. MySQL timezone map can differ from PHP as timezones change occasionally. Now you need to make sure that both PHP and MySQL timezones are in the sync. This was a rare case and we served truly global audience. I believe this only happened just a few times over 7 years or so.

2. You do need to load the MySQL timezone data which requires root privileges if I remember correctly. I believe PHP pulls its timezone data from the OS (at least on Linux). Therefore in cloud you may not be able to do this nor you have you any control on what timezone version has been loaded.

So, can we use timezone filed of our 'mysql' configuration to force a specific timezone?

How, for example, set 'utc' ?

I tried

'timezone' => "UTC",

But I got an error

SQLSTATE[HY000]: General error: 1298 Unknown or incorrect time zone: 'UTC'

Had the same error. so i looked for the valid values for set time_zone and got to this page.

so i used:

'timezone' => '+00:00',

and it worked.

The best way to do this is to keep everything set to UTC on the server side. You can then offload the timezone conversions to the JavaScript on the front end. Here is an example using Moment.js.

      /**
         * Convert UTC datetime to local timezone and localized format
         *
         * @returns {string}
         * @param dateString
         */

        toLocalDateTime(dateString) {
            const dateTime = moment.utc(dateString).local().toDate();
            return dateTime.toLocaleDateString() + ' ' + dateTime.toLocaleTimeString([], { hour: '2-digit', minute: '2-digit' });
        },

     /**
         * Convert local time back to UTC time date
         *
         * @returns {*}
         * @param dateTimeString
         */

        toUTC(dateTimeString) {
            return moment(dateTimeString).utc().format('YYYY-MM-DD HH:mm:ss');
        },