如何在MySQL中存储带有时区信息的日期时间
如何在MySQL中存储带有时区信息的日期时间
我有数千张在坦桑尼亚拍摄的照片，我想将每张照片拍摄的日期和时间存储在MySQL数据库中。然而，服务器位于美国，在我尝试存储落在美国夏令时“无效”小时内的坦桑尼亚日期时间时遇到问题。坦桑尼亚不实行夏令时，因此该时间实际上是有效的时间。\n额外的复杂性在于，有来自许多不同时区的合作者需要访问存储在数据库中的日期时间值。我希望它们始终显示为坦桑尼亚时间，而不是各个合作者所在地的本地时间。\n我不愿意设置会话时间，因为我知道当有人有时忘记设置会话时间并导致时间完全错误时会出现问题。而且我没有权力改变服务器的任何内容。\n我已经阅读了以下内容：\nDaylight saving time and time zone best practices以及\nMySQL datetime fields and daylight savings time -- how do I reference the \"extra\" hour?以及\nStoring datetime as UTC in PHP/MySQL\n但是它们似乎都没有解决我的特定问题。我不是一个SQL专家；在设置DATETIME时是否有指定时区的方法？我没有看到。否则，非常感谢对如何解决这个问题的任何建议。\n编辑：\n这是我遇到的问题的一个例子。我发送命令：\n
INSERT INTO Images (CaptureEvent, SequenceNum, PathFilename, TimestampJPG) VALUES (122,1,"S2/B04/B04_R1/IMAG0148.JPG","2011-03-13 02:49:10")
\n然后我收到错误信息：\n
Error 1292: Incorrect datetime value: '2011-03-13 02:49:10' for column 'TimestampJPG'
\n这个日期和时间在坦桑尼亚存在，但在数据库所在的美国却不存在。
在MySQL中，DATETIME类型没有包含时区信息。假设你将'2019-01-01 20:00:00'存储到DATETIME字段中，当你检索该值时，你需要知道它所属的时区。
因此，在你存储值到DATETIME字段时，请确保它是坦桑尼亚时间。然后当你取出它时，它将仍然是坦桑尼亚时间。
那么，关键问题是：当我进行INSERT/UPDATE时，如何确保值是坦桑尼亚时间呢？有两种情况：
CASE #1
如果你使用INSERT INTO table (dateCreated) VALUES (CURRENT_TIMESTAMP or NOW())进行操作，MySQL会获取当前时间，假设是坦桑尼亚时间的'2019-01-01 20:00:00'。然后MySQL会将其转换为UTC时间，结果是'2019-01-01 17:00:00'，并将该值存储到字段中。
那么如何将坦桑尼亚时间'20:00:00'存储到该字段中呢？这是不可能的。当从该字段中读取时，你的代码需要以UTC时间为基准。
CASE #2
这取决于你在INSERT INTO table (dateCreated) VALUES (?)中传递的值的类型。如果你传递的是字符串'2019-01-01 20:00:00'，那么很好，这就是将存储到数据库中的值。如果你传递的是某种日期对象，则取决于数据库驱动程序如何解释该日期对象，并提供给MySQL存储的'YYYY-MM-DD HH:mm:ss'字符串。你可以查阅数据库驱动程序的文档以了解详细信息。
实际上，CASE #1是错误的。MySQL不会将datetime列的值转换为UTC时间。它们会按原样存储。
实际上这取决于服务器的时区设置。很多人在云服务器上使用UTC时区，这种情况下答案是正确的。
当我重新阅读我所写的内容时，我意识到可能会让人困惑。也许最重要的一点是MySQL只会为DATETIME字段存储字符串'YYYY-MM-DD HH:mm:ss'。无论你给MySQL提供什么样的字符串，它就会存储什么样的值。如果你提供的是一个非字符串对象，则需要找出该对象被转换为哪个日期时间字符串。
如何在MySQL中存储带有时区信息的日期时间
问题的原因：
1. 在MySQL中，TIMESTAMP类型的数据只能存储到2038年，且与服务器时区相关，这可能不是我们想要的。
2. 时间偏移量不应该简单地存储在INT字段中，因为存在半小时和四分之一小时的偏移量。
解决方法：
使用两列存储日期时间和时区信息：
1. 一个DATETIME列存储日期时间。
2. 一个VARCHAR列存储时区信息，可以是具体的时区或地点（如America/New_York），时区缩写（如PST），或时间偏移量（如-2:00）。
如果需要让MySQL对这些日期进行比较或排序，可以将日期时间转换为UTC时间，并在检索数据时使用时区信息将其恢复为原始形式。
另外，一种推荐的方法是使用三列：
1. local_time列存储本地时间。
2. utc_time列存储UTC时间。
3. time_zone列存储时区信息。
通过使用三列的方法，可以明确地知道是否已将日期时间转换为UTC时间。
在处理多个时区时，需要考虑准确性的降低：
1. 如果有用户的时区/地点信息，可以准确地知道他们在过去或未来的任何时刻的当地时间。
2. 如果只有时区缩写或时间偏移量，无法预测过去或未来的当地时间。
参考资料：
- MySQL Date/Time Reference
- The Proper Way to Handle Multiple Time Zones in MySQL
以上是如何在MySQL中存储带有时区信息的日期时间的解决方法。通过使用两列或三列来分别存储日期时间和时区信息，可以实现准确的时间存储和检索。
如何在MySQL中存储带有时区信息的日期时间
有人提出了以下问题：
我希望日期时间始终以坦桑尼亚时间而不是各种合作者所在的本地时间显示。
如果是这种情况，那么您不应该使用UTC。您只需要在MySQL中使用DATETIME类型而不是TIMESTAMP类型即可。
根据MySQL文档：
MySQL将TIMESTAMP值从当前时区转换为UTC进行存储，并从UTC转换回当前时区进行检索。（对于DATETIME等其他类型不会发生这种情况。）
如果您已经使用DATETIME类型，那么您首先可能没有按照本地时间进行设置。您需要更多关注您的应用程序代码，而不是数据库-您没有在这里展示代码。问题和解决方法将根据语言的不同而有很大的差异，因此请确保使用适用于您应用程序代码的正确语言标签。
我正在使用DATETIME。但是，如果我尝试设置一个在美国不存在但在坦桑尼亚存在的时间，数据库会抛出错误。我已经编辑了我的问题以提供我遇到的问题的示例。我的应用程序代码恰好是Python，但现在它实际上只是一种围绕SQL代码的包装器。我很乐意在Python中进行转换，但不确定应该做什么转换。
那么，您能否展示您的Python代码，这样我们就可以看到您是如何构建SQL查询的？您可能只是在Python中处理了无法识别和识别的日期时间。并且您确定您的TimestampJPG列是mysql数据库模式中的DATETIME类型而不是TIMESTAMP类型吗？
糟糕。你是对的。我将数据库中的所有其他TIMESTAMP更改为DATETIME，但不知怎么忘了这一个。问题解决了。谢谢。
为什么你说PostgreSQL在这里会更好？它也会在存储之前将时间戳转换为UTC。
MariaDB开发者正在努力实现带有时区的DATETIME：jira.mariadb.org/browse/MDEV-11829
如果我们收到一个传统数据库，其中没有关于时区的信息，我们如何向用户显示真实的日期？我认为保存时区信息是必要的。