在数据库中存储时区的正确方法? [英] Proper way to store a timezone in a database?
问题描述
让我解释一下:我并不是在问将特定日期时间的时区存储在数据库中的正确方法.我说的是时区本身.例如:
Let me explain: I'm not asking about the proper way to store a specific datetime's timezone in a database. I'm talking about timezones themselves. FOR EXAMPLE:
我有一个名为"users"的MySQL表.现在，在此表上，我希望有一列包含用户居住时间的时区(由用户提供，将从列表中选择).我正在使用PHP，它有一个像这样的时区字符串列表:
I have a MySQL table named 'users'. Now, on this table, I wish to have a column that contains the timezone of wherever the user lives (this is provided by the user, it will be chosen from a list). I'm working with PHP, which has a list of timezone strings like these:
现在，显而易见的解决方案(至少对我来说)是在用户"表中创建VARCHAR列，然后将PHP使用的时区字符串存储在该列中.
Now, the obvious solution (at least for me) would be to create a VARCHAR column in the 'users' table, then store the timezone string used by PHP in said column.
现在，我考虑一下，这将假定我将始终使用PHP与该数据库表进行交互，虽然现在是正确的，但将来可能不是这样.并且(如果我错了，请纠正我)PHP的时区字符串可能不适用于其他编程语言，也许其他语言有自己的常量"来处理时区.
Now that I think about it, that would assume I'll always use PHP to interact with that database table, which while true now, might not be so in the future. And (correct me if I'm wrong) PHP's timezone strings probably don't work for other programming languages, maybe other languages have their own 'constants' for timezone handling.
那么，如何处理在DB列中保存用户的时区首选项?任何想法都值得赞赏.
How would you approach saving a user's timezone preference in a DB column, then? Any ideas are certainly appreciated.
注意:关于PHP时区的有用之处在于，即使DST有效，它们也会自动将其考虑在内，这非常了不起.因此，您可以看到我对使用它们的兴趣，而不仅仅是存储数字偏移量.
Note: the useful thing about PHP's timezones is that, even if DST is in effect, they automatically take it into account, which is awesome. So you can see my interest in using them instead of just storing a numeric offset.
推荐答案
(如果我错了，请纠正我)PHP的时区字符串可能不适用于其他编程语言
And (correct me if I'm wrong) PHP's timezone strings probably don't work for other programming languages
好的，我会的. :)
PHP实现了 IANA/Olson/TZDB/ZoneInfo数据库(一样).这是最接近行业标准的东西，并且几乎在每种编程语言和平台中都可以实现.
PHP implements the IANA/Olson/TZDB/ZoneInfo database (lots of names for the same thing). This is the closest thing there is to an industry standard, and it's implemented in just about every programming language and platform.
唯一不支持它的操作系统是Microsoft Windows，因为Microsoft维护自己的时区数据库.因此，对于以Windows为中心的平台(如.Net)，如果要支持以下内容，则必须使用库那种时区.
The only OS that doesn't support it natively is Microsoft Windows, because Microsoft maintains their own time zone database. So for platforms that are Windows-centric, like .Net, there are libraries that you have to use if you want support for that style of timezone.
在时区标签Wiki 或是的-您只需将时区ID字符串存储为varchar.那是最好的事情.
So yes - you just store the timezone id string as a varchar. That's the best thing to do.
这篇关于在数据库中存储时区的正确方法?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
