Auto convert Mysql timezone and back again in Laravel 5 models

Question

I am developing a Laravel 5.8 app that is connecting to 2 databases. One is the app's own database and the other is a legacy MySQL database. Sadly the legacy database stores datetimes as 'Europe/London' not UTC.

Is there a nice way to auto adapt the 'Europe/London' dates to 'UTC' in my Laravel models?

I have seen that you can add 'timezone'=>'+01:00' to the connection in 'config/database.php' but this has no affect. And I don't want to set the apps default timezone in 'config/app.php' because that will affect the app's own database which must be UTC.

If the database contains '2019-04-01 13:00:00', I would like $model->created_at to return '2019-04-01 12:00:00' (UTC equivalent) by default. Likewise, if I do $model->created_at = '2019-04-01 12:00:00'; $model->save() should save as '2019-04-01 13:00:00' in the database.

Can any one help? I'm starting to think I'll have to make a bunch of accessors and mutators for all of my datetime columns which seems like a lot of work.

https://stackoverflow.com/questions/32884388/change-timezone-in-lumen-or-laravel-5 Possible duplicate? — Lim Kean Phang, Apr 07 '19 at 13:55
Could you achieve this using Carbon to alter the insert dependant on which db it is? https://carbon.nesbot.com/docs/ :) — shane-chris-barker, Apr 07 '19 at 13:59
@LimKeanPhang thanks but that is for the entire app. I only want to doing for models using a specific database. — user1094128, Apr 07 '19 at 14:17
@shane-chris-barker maybe I just need to find a nice way to apply it to all datetime columns on each model with that database connection. I have found that this works for retrieving the dates ````protected function asDateTime($value) { $localDateTime = new \Carbon\Carbon(parent::asDateTime($value), 'Europe/London'); $localDateTime->setTimezone('UTC'); return $localDateTime; }```` — user1094128, Apr 07 '19 at 14:19

score 1 · Answer 1 · answered Apr 07 '19 at 15:04

In the end I used getAttribute and setAttribute

public function getAttribute($key){
        $value = parent::getAttribute($key);

        if(in_array($key, $this->dates)){
            $localDateTime = new \Carbon\Carbon(parent::asDateTime($value), 'Europe/London');
            $localDateTime->setTimezone('UTC');
            return $localDateTime;
        }
        else 
        {
            return $value;
        }
    }


    public function setAttribute($key, $value){
        if(in_array($key, $this->dates) && $value !== null){
            $value = new \Carbon\Carbon(parent::asDateTime($value), 'UTC');
            $value->setTimezone('Europe/London');
        }

        parent::setAttribute($key, $value);
    }

I'll be putting this in a BaseModel or trait. Also the 'Europe/London' will be in a config file.

Seems to work ok. Shame there is no built in way (that I know of).

Auto convert Mysql timezone and back again in Laravel 5 models

1 Answers1