Yii2并将数据作为UTC存储在数据库中 [英] Yii2 and storing data in database as UTC

问题描述

我正在使用Yii2，我想知道它是如何确定将数据存储在数据库中的哪个时区的?

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

我注意到 $ defaultTimezone 似乎表明它只是在将 input 数据传递给诸如

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.

但是我想知道如何确保它在正确的时区中将数据插入数据库中，以便可以信任$defaultTimezone的值?

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?

是否需要为MySQL运行类似的内容:

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

SET time_zone = timezonename;

推荐答案

好，这是我发现的以下内容:

Ok, here is what I have discovered regarding this:

时间戳:

MySQL会自动将该值存储为UTC，并将根据mysql服务器时区将您提供的值转换为UTC并返回.

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.

但是，一些注意事项:

我建议将服务器时区设置为UTC，如下所示:

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

SET time_zone = 'UTC';

那么，如果您的服务器遍布世界各地，您将始终在每个服务器上获得相同的数据回传-这将与传递给它的数据相同，因此这将基于您的PHP时区，因此，如果希望它回传 UTC时间，则应使用NOW() myswl函数存储TIMESTAMP.

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.

如果您未设置服务器时区，则MySQL通常会依赖SYSTEM时区，并且每台服务器都会根据其时区获得不同的时间戳.

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.

如果您想将UTC更改实施到现有的 系统中，那么它的行为将有所不同.无论您通过什么，它似乎总是将日期返回为UTC.

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.

如果您将服务器时区设置为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.

日期和日期时间

据我所知，这些只是存储并返回您提供给他们的确切数据.

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

存储unix时间戳:

如上所述，它们将存储您赋予它们的任何值，因为您将它们存储为任何旧的integer； 但是如果要将时间戳存储在UTC中，则可以按照@NgôVănThao的说明使用UNIX_TIMESTAMP，或者如果需要在PHP中进行操作，则可以执行以下操作:

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');

U表示php date函数中的unix时间戳格式设置；您可以使用任何格式化选项，您想以其他格式返回日期.

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.

简而言之...

• 使用SET time_zone = 'UTC'
将服务器时间设置为UTC
• 将数据插入TIMESTAMP字段时使用NOW()
• 使用UNIX_TIMESTAMP()或上面提供的代码将unix timestamps存储在UTC中

• 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

执行上述操作应始终确保您以UTC格式获取相关的时间/日期.

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

这篇关于Yii2并将数据作为UTC存储在数据库中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！