Geek Answers Handbook

Yii2 and saving data in the database as UTC

I use Yii2 , and I was wondering how it decides which time zone to store data in the database how?

I noticed $ defaultTimezone , which apparently indicates that it just controls what time area your input data should be present when passing it to functions like asTime , and uses the hourly formatting time to convert the specified data to the correct time output.

But I wonder how you make sure you paste the data into your database in the right time zone so that you can trust the value of $defaultTimezone ?

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

 SET time_zone = timezonename;
+1
source share
2 answers

Ok, here is what I found about this:

Time stamp:

MySQL will automatically save this value as UTC and convert what you give it to UTC and vice versa, depending on the time zone of the mysql server.

However, some notes:

I would recommend setting your server’s time zone to UTC like this:

 SET time_zone = 'UTC';

Then it doesn’t matter if you have servers around the world, you will always receive the same data that will be transmitted on each server - it will be the same data as you transferred to it, so it will be based on your PHP time zone, so if you want it to return UTC time, then you should use myswl's NOW() function to store TIMESTAMP.

If you do not set the server’s time zone, MySQL usually relies on the SYSTEM time zone, and each server will receive a different time stamp back depending on its time zone.

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

If you have your server’s time zone set to UTC , and then you change it to something else, then when everything starts to look a little hairy.

date and datetime

As far as I know, they just store and return the exact data that you give them.

Saving unix timestamps:

As above, they will only store the value that you give them, because you just store them as old integer ; but if you want to save the timestamp in UTC, you can use UNIX_TIMESTAMP as indicated by @ Ngô Văn Thao, or if you need to do this in PHP, you can do something like this:

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

U is the unix timestamp formatting parameter in the php date function; You can use any option if you want to return the date in a different format.

So, in short ...

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

Doing the above should always ensure that you receive the relevant dates / dates in UTC.

0
source

If you want to use only one time zone for your application, you can simply add the following line to your configuration file or script entry (default is web / index.php):

 date_default_timezone_set('UTC');
0
source

More articles:


All Articles