在数据库中存储时区信息

Question

我将使用MySQL和PHP，并需要在数据库中存储时区。我查阅了一些资料，发现需要使用像America/Los_Angeles这样的形式，而不是PST、Pacific Standard Time、PST8PDT、+02.00等格式。

我应该将整个字符串America/Los_Angeles存储下来，还是像https://dev59.com/UWgu5IYBdhLWcg3wGjZF#11580459所描述的c#那样使用某种整数ID？如果是整个字符串，最大字符数是多少？

- user1032531

Answer 1

时区字符串如America/Halifax和Asia/Kolkata指的是所谓的zoneinfo数据库中的条目。阅读此文。https://www.iana.org/time-zones它目前由Internet Assigned Numbers Authority维护。由于维护者需要跟踪夏令时规则和其他时间政治变化，因此它经常更改。

在类UNIX系统上，这些条目存储在文件系统目录层次结构中，而不是通过某个代码编号。这是我建议的成功实现。您可以查看一下它是如何工作的。

您问道：

我是否存储整个America/Los_Angeles字符串？

是的。

是否有某种整数ID？

否，如果您希望应用程序和数据库具有未来可靠性。MySQL中保存zoneinfo数据的部分中有一个time_zone_id，但没有人保证保持这些数字不变。

目前（2021年E版，截至2022年3月仍然有效）数据库中最长的字符串为posix/America/Argentina/ComodRivadavia，长度为38个字符。 mysql.time_zone_name表定义了一个64个字符的Name列。使用VARCHAR（64）来存储此信息是合理的；这与名称在系统中存储方式相匹配。顺便说一下，所有名称都使用7位ASCII编码，因此可以使用ascii_general_ci排序规则来定义存储它们的列。这应该是用户偏好时区列的良好定义。

time_zone VARCHAR(64) 
          NOT NULL 
          DEFAULT 'UTC' 
          COLLATE ascii_general_ci

WordPress提供用户可用时区名称的下拉列表，并将用户的选择存储为文本。这是一个值得模仿的好例子。

http://www.iana.org/time-zones

- O. Jones

---

在2022年，偶然发现一个SO答案，它提供了我想知道的一切，甚至包括VARCHAR(64)，这感觉真奇妙...致敬并表示非常感谢。 - Nicholas Franceschina

Answer 2

请参考MySql日期时间

MySQL将TIMESTAMP值从当前时区转换为UTC进行存储，并在检索时从UTC转换回当前时区。(对于DATETIME等其他类型，不会发生这种情况。)默认情况下，每个连接的当前时区是服务器的时间。可以根据每个连接设置时区。只要时区设置保持不变，您获取的值与所存储的值相同。

您应该在表中添加一个新列来保存您的时区，当提取信息时，请将其转换为您的时区。

- Laurentiu

---

请描述你所提到的“在表格中添加新列以保存时区”的步骤。 - user1032531

如果您需要在数据库转换日期时间之前知道确切的时区，那么您需要保留它，因为数据库会自动将您的日期时间转换为服务器时区并存储它。 - Laurentiu

我的意思是，我应该使用 VARCHAR(32) 来存储它吗？这样对于所有时区来说足够大吗？另外，请确认一下是否存在用于时区的整数 ID。 - user1032531

是的，你应该将它存储为字符串，可以在这里查看 https://dev59.com/sV7Va4cB1Zd3GeqPMbmW - Laurentiu

关于长度的问题，是32吗？这里有一个确认：https://dev59.com/k0_Ta4cB1Zd3GeqPDsE1 - Laurentiu