我需要在 MySQL 中进行日期时间转换。如何确定客户端的时区？
我将尝试解释我的目标、我想要实现的目标以及为什么没有任何东西对我有用： 我需要将 DATETIME 存储在 MYSQL 表中。客户端应该能够通过两种方式检索该 DATETIME：
- 如果日期时间 2011-07-12 00:00:00 由时区为 UTC+2 的 user1 添加，然后时区为 UTC-3 的 user2 检索该值他应该得到日期时间 2011-07-11 19:00:00。夏令时在这里很重要。
- 如果日期时间 2011-07-12 00:00:00 是由 user1 添加的，则 user2 会获得完全相同的日期
，那就是我不需要更多了。但我找不到如何做到这一点的方法...
我想出的是将日期存储在 user1 时区中，如果使用第二种方法，则根本不执行任何转换。问题出在第一种方法上。我想我可以使用 CONVERT_TZ() 函数，但是我应该使用什么作为时区参数？在我看来， mysql.time_zone_name 表中的时区列表完全是一团糟。让用户从这个混乱的列表中选择他的时区是否实用？例如，我应该使用什么：欧洲/瓦尔沙瓦还是波兰？如果我住在克莱佩达市（欧洲，立陶宛），我是否应该选择欧洲/维尔纽斯时区？为什么这些名字这么复杂？也许还有其他 MySQL 表使用某种标准化的名称，例如 UTC+02:00 宽度 DST，而不使用，例如在 wwindows 操作系统中（遗憾的是我没有机会与其他操作系统进行比较）？我可能会像记录的那样使用时区“+02:00”的偏移量，但随后不会保留 DST 信息。或者也许有办法自动确定用户时区？
希望我说清楚了。非常感谢帮助和建议。
[编辑（回复Flimzy）]
如果我总是必须转换为用户（客户）日期，我很生气，在UTC中存储所有内容是最好的方法。但是，如果一个用户输入 DATETIME 2011.11.12 00:00:00，而另一个用户必须看到完全相同的时间并且他们的时区不匹配，那么该怎么办？所以我决定在存储和存储时区时根本不转换日期。这是我的问题。如何自动确定客户端时区？或者如何使时区名称不那么混乱？该设置对于我的应用程序非常重要，因此我不想让用户配置它，但如果没有办法做到这一点，我希望该时区列表尽可能清晰。
i'll try to explain my goal, what i'm trying to achieve and why nothing works for me:
i need to store DATETIME in a MYSQL table. The client should be able to retrieve that DATETIME in two ways:
- if the datetime 2011-07-12 00:00:00 was added by the user1 with time zone UTC+2 and then the user2 whose timeZone is UTC-3 retrieves this value he should get datetime 2011-07-11 19:00:00. The DST is important here.
- if the datetime 2011-07-12 00:00:00 was added by the user1, user2 gets the exact same date
that’s it i don't need nothing more. but i cannot find the way of how to do it...
what i came up with is to store the date in user1 timezone that lets to not perform any conversions at all if using 2nd method. the problem is with the 1st method. i guess i could use CONVERT_TZ() function, but what should i use as a timezone parameter? that list of timezones in mysql.time_zone_name table is a complete mess in my opinion. is it practical to let the user pick his timezone from this messy list? For example what should i use: Europe/Warshava or Poland? if I live in a city of Klaipeda(Europe, Lithuania) is it obvious that i should pick time zone Europe/Vilnius? Why these names are so complicated? Maybe there are other MySQL tables which use somehow standardized names like UTC+02:00 width DST and without, like in wondows os(sad I have no chance to compare to other os)? i could of cause use offset of a timezone "+02:00" like documented but then the DST information is not preserved. or maybe there are ways of determining user timezone automatically?
Hope I made myself clear. Help and any advice is very much appreciated.
[edited (reply to Flimzy)]
if i always have to convert to users(clients) date i agry that stroing everything In UTC is best approatch. But what if one user enters the DATETIME 2011.11.12 00:00:00 and the other user have to see exact same time and their timezones doesn’t match, what then? So I decided to not convert date at all when storing and store timezone together. That is my problem. How to determine the clients time zone automatically? Or how to make timezone names not so confusing? That setting is very important for my application and because of that I don’t want to let a user configure it, but if there is no way of doing that I want that that timezone list to be as clear possible.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
每当您处理多个时区时，您应该始终在插入之前将时间转换为 UTC，然后在从数据库中提取时转换为用户的时区以供显示。
即使您的用户都不使用 UTC，以 UTC 存储也会大大简化事情。
与“UTC+02:00 with DST”不同，这些名称令人困惑的原因是同一时区的不同地区对 DST 的定义不同。以我所在地区为例，美国和墨西哥位于同一时区（“美国中部”），但夏令时日期不同。美国的变化比墨西哥早大约两周。因此，仅仅说“我处于 GMT-05 夏令时”是不够的，因为这并不能传达哪些 DST 规则适用于我。
有很多地方的 DST 规则比这个例子还要复杂。这种复杂性进一步证明，将所有内容存储在 UTC 中使生活变得更加简单:)
Whenever you deal with multiple timezones, you should always convert your times to UTC before insert, then convert to the user's time zone for display when you pull from the database.
Even if none of your users use UTC, storing in UTC simplifies things greatly.
The reasons for the confusing names, as opposed to just "UTC+02:00 with DST" is that DST is defined differently in different regions within the same time area. Using an example from my part of the world, The U.S. and Mexico are in the same time zones ("U.S. Central"), but have different DST dates. The U.S. changes about 2 weeks before Mexico does. so it's not enough to say "I'm in GMT-05 with DST", because that does not convey which DST rules apply to me.
There are many places where DST rules are even more complicated than this example. And this complication just goes to further demonstrate that storing everything in UTC makes life much much simpler :)
尝试使用 TimeStamp
时间戳也会自动存储时区信息（其内置实际上）。
Try using a TimeStamp
Timestamps automatically store timezone information too(its built in actually).
我想我会使用这个 javascript 然后根据 时区检测列表 使用自定义时区名称构建我自己的列表，例如 (UTC +02:00) 赫尔辛基，基辅、里加、索非亚、塔林、维尔纽斯等，如果自动检测过程中出现问题，用户可以更改时区
i think i'll use this javascript and then according to Timezone Detection List build my own list with custom timezone names like (UTC +02:00) Helsinki, Kyiv, Riga, Sofia, Tallinn, Vilnius etc. and leave the user ability to change time zone if something went wrong in auto detection process