Yii2 and storing data in database as UTC

Question

I'm using Yii2 and I was wondering how it decides what timezone to store the data in the database as?

I have noticed the $defaultTimezone which seems to indicate it simply controls what timezone your input data is supposed to be when passing it to functions such as the the asTime function and it uses the formatter timezone to convert said data into the correct time output.

But I'm wondering how do you make sure it is inserting the data into your database in the right timezone so then the value of $defaultTimezone can be trusted?

Is there a need to run something like this for MySQL:

SET time_zone = timezonename;

Answer 1

I found a solution here: https://gist.github.com/SilverFire/6f98d2605a6574bd02f7

It worked for me very well, specially because I work with mysql inside docker container.

In your "config/db.php" add the config below, but remember to replace 'America/Manaus' with your UTC:

return [

    // ... your database config goes here, and after:

    'on afterOpen' => function($event) {
        // $event->sender refers to the DB connection
        $event->sender->createCommand("SET time_zone = 'America/Manaus'")->execute();
    }
];

Answer 2

If you wish to use only one timezone for your application you can just add the following line to your config file or the entry script (web/index.php by default):

date_default_timezone_set('UTC');

Answer 3

Ok, here is what I have discovered regarding this:

timestamp:

MySQL will automatically store this value as UTC and will convert what you give it to UTC and back based on the mysql server time zone.

However, some notes:

I would recommend to set your server timezone to UTC with something like this:

SET time_zone = 'UTC';

Then it won't matter if you have servers all over the world you will always get the same data passed back on each server - this will be the same data as you passed to it, so this will be based on your PHP timezone, so if you want it to pass back a UTC time then you should use the NOW() myswl function to store the TIMESTAMP.

If you don't set the server timezone MySQL will generally rely on the SYSTEM timezone and each server would get a different timestamp back based on their timezone.

If you want to implement a UTC change into an existing system, then it will act differently. No matter what you pass it, it seems it will always return the date as UTC.

If you had your server timezone set to UTC and then you change it to something else then that's when things start to look a little hairy.

date and datetime

As far as I'm aware these just store and return the exact data you give them.

Storing unix timestamps:

As above these will just store whatever value you give them since you are just storing them as any old integer; but if you want to store the timestamp in UTC then you can use UNIX_TIMESTAMP as stated by @Ngô Văn Thao or if you need to do it in PHP then you can do something like this:

$tz = new DateTimeZone('UTC'); 
$dt = new DateTime("now", $tz);
$utc_timestamp = $dt->format('U');

The U represents the unix timestamp formatting option within the php date function; you can use any of the formatting options if you want to return the date in another format.

So in short...

• Set your server time to UTC using SET time_zone = 'UTC'
• Use NOW() when inserting data into TIMESTAMP fields
• Use UNIX_TIMESTAMP() or the provided code above to store your unix timestamps in UTC

Doing the above should always make sure you get the relevant times/dates back in UTC format.