Yii2 并将数据存储在数据库中作为 UTC

2022-01-04 00:00:00 timezone php mysql yii2 yii

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

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

我注意到了 $defaultTimezone 这似乎表明它只是控制您的输入数据在将其传递给诸如 asTime 函数，它使用 格式化时区 将所述数据转换为正确的时间输出.

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;

推荐答案

好的，这是我发现的:

时间戳:

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 时间戳:

如上所述，这些只会存储您给它们的任何值，因为您只是将它们存储为任何旧的整数；但是如果你想以UTC格式存储时间戳，那么你可以使用@Ngô Văn Thao所说的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() 或上面提供的代码以UTC 格式存储unix 时间戳
  • 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.

相关文章