在 PHP & 中使用时区MySQL [英] Working with timezone in PHP & MySQL

问题描述

我应该如何处理时区.只为用户存储偏移量是否安全?或者我也应该有区域/位置?当我比较维基百科和 PHP 中的偏移值时，有些不匹配.我应该相信哪个?

How should I go about dealing with timezone. Is it safe to just store the offset for the user? Or should I also have the Area/Location? When I compared the offset values in Wikipedia and PHP, some doesn't match. Which should I trust?

最后我应该如何在 PHP 中使用它.我可以做一个时间 - 服务器偏移 + 用户偏移"吗?

Lastly how should I go about with it in PHP. Can I just do a "Time - Server Offset + User Offset"?

推荐答案

如果您关心保留所有数据，区域和位置很重要.存储日期的最佳方式(在我看来)是存储 UTC 时间戳 + 位置.

Area and location is important if you care about retaining all the data. The best way (in my opinion) to store dates is to store a UTC timestamp + the location.

对于某些计算，仅偏移量可能就足够了，但如果您有时间戳，并且想知道诸如+1 天"之类的确切时间，则这还不够.由于这在不同国家/地区对夏令时的规定不同.

Just the offset may be enough for certain calculations, but it could not be enough if you have a timestamp, and want to know the exact time for something like "+1 day". As this varies in different countries with different rules for daylight savings time.

因此，如果您想绝对确定您不会丢失信息"并且将来无法进行基于时间的计算，请存储 UTC 时间戳和 olson id(例如:欧洲/阿姆斯特丹).

So if you want to be absolutely certain you are not 'losing information' and unable to do time-based calculations in the future, store the UTC timestamp and the olson id (e.g.: Europe/Amsterdam).

回答你的第二个问题，如果你有这两条信息，你可以很容易地用DateTime重构它:

To answer your second question, if you have these two pieces of information, you can easily reconstruct it with DateTime:

$dt = new DateTime('@' . $timeStamp);
// Now convert it to the users timezone
$dt->setTimeZone(new DateTimeZone('Europe/Berlin'));

// Now you have a 'DateTime' object which you can easily display with the ->format function.

添加

我个人更喜欢将时间戳存储为整数.TIMESTAMP 类型会自动转换，我觉得让 PHP 应用程序处理这个更好，这对于我认为您的用例(用户的简单本地化)特别有意义.

I personally prefer to store timestamps as integers. The TIMESTAMP type does automatic conversion, and I feel it's better to let the PHP application handle this, this makes especially sense for what I think your use-case is (simple localization for users).

使用 DATETIME 也可以，但存储要求比仅使用整数高得多.如果您确实更喜欢 DATETIME，请尝试在您的应用程序中制定一项规则，将每个值始终存储为 UTC，因为永远不会有任何混淆，尤其是在您当地时区的 DST 转换和法律更改方面.

Using DATETIME works too, but the storage requirements are much higher than just using an integer. If you do prefer DATETIME, try to make it a rule within your application to store every value always as UTC, as there is never any confusion especially in relation to DST transitions and law changes in your local timezone.

如果您只想显示基于用户本地时区计算的 Web 应用程序上的时间，则偏移量是无用的.大多数国家/地区的抵消额每年会发生两次变化，并且几乎每年都会发生一两个国家/地区的变化.

If you simply want to show times on your web application calculated based on the users' local timezone, the offset is useless. The offset changes twice a year for most countries, and almost every year one or two country changes when this happens.

如果您使用 PHP 很棒的 DateTime 和 DateTimeZone 对象，您只需要位置.

You only need the location if you use PHP's awesome DateTime and DateTimeZone objects.

最后一点建议:

人们倾向于混淆 PHP 应用程序中的日期和时间，并以多种不同的格式(字符串、整数等)发送这些值并混合 GMT 和 UTC.试着给自己制定一个规则，只在函数参数和返回值中发送 DateTime 对象，这样你就可以输入提示，并且变量的格式永远不会有任何疑问.它将是值得.

People tend to confuse dates and times in PHP applications and sending around these values in many different formats (strings, ints, etc) and mix GMT and UTC. Try to make it a rule for yourself to only ever send around DateTime objects in function arguments and return values, so you can typehint and there is never any doubt in what format a variable is in. It will be worth it.

这篇关于在 PHP & 中使用时区MySQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！