如何使用时区信息在MySQL中存储datetime [英] How to store a datetime in MySQL with timezone info

问题描述

我在坦桑尼亚拍摄了数千张照片，我想将每张照片的日期和时间存储在MySQL数据库中。然而，服务器位于美国，当我尝试存储在春季夏令时间（在美国）的无效小时内的坦桑尼亚日期时间时，我遇到问题。坦桑尼亚不做DST，所以时间是一个实际有效的时间。

I have thousands of photos that were taken in Tanzania and I want to store the date and time each photo was taken in a MySQL database. The server, however, is located in the U.S. and I run into problems when I try to store a Tanzanian date-time that falls within the "invalid" hour during spring Daylight Savings time (in the U.S.). Tanzania doesn't do DST, so the time is an actually valid time.

其他复杂情况是来自许多不同时区的合作者将需要访问存储在数据库中的日期时间值。我希望他们总是以坦桑尼亚时代的形式出现，而不是在当地的各个合作者的时间。

Additional complications are that there are collaborators from many different timezones who will need to access the date-time values stored in the database. I want them to always come out as Tanzanian time and not in the local times that various collaborator are in.

我不愿意设定会话时间，因为我知道当有人忘记设置一个会话时间并且把所有时间都出错时，会有问题。我没有权限更改服务器的任何内容。

I'm reluctant to set session times because I know that there will be problems when someone sometime forgets to set a session time and gets the times out all wrong. And I do not have authority to change anything about the server.

我已经阅读：
夏令时和时区最佳做法和
我们的日期时间字段和夏令时间 - 如何引用额外小时？和
将datetime作为UTC存储在PHP / MySQL

但是他们似乎都没有解决我的特殊问题。我不是SQL专家有没有办法在设置DATETIME时指定时区？我没看过。否则，任何关于如何处理这个问题的建议是非常感激的。

But none of them seems to address my particular problem. I'm not an SQL expert; is there a way to specify timezone when setting DATETIMEs? I haven't seen one. Otherwise, any suggestions on how to approach this issue is greatly appreciated.

编辑：
这是一个我遇到的问题的例子。我发送命令：

Here's an example of the problem I'm running into. I send the command:

INSERT INTO Images (CaptureEvent, SequenceNum, PathFilename, TimestampJPG) 
VALUES (122,1,"S2/B04/B04_R1/IMAG0148.JPG","2011-03-13 02:49:10")

我收到错误：

Error 1292: Incorrect datetime value: '2011-03-13 02:49:10' for column 'TimestampJPG'

这个日期和时间存在于坦桑尼亚，但不在美国，数据库是。

This date and time exists in Tanzania, but not in the U.S., where the database is.

推荐答案

你说：

我希望他们总是像坦桑尼亚时间一样出现，而不是在当地的各个合作者的时间。

I want them to always come out as Tanzanian time and not in the local times that various collaborator are in.

如果是这种情况，那么你应该不使用UTC。所有你需要做的是在MySQL中使用 DATETIME 类型，而不是 TIMESTAMP 类型。

If this is the case, then you should not use UTC. All you need to do is to use a DATETIME type in MySQL instead of a TIMESTAMP type.

从MySQL文档：

MySQL将当前时区中的 TIMESTAMP 值转换为UTC进行存储，并从UTC返回到当前时区进行检索。 （不适用于其他类型，例如 DATETIME 。）

MySQL converts TIMESTAMP values from the current time zone to UTC for storage, and back from UTC to the current time zone for retrieval. (This does not occur for other types such as DATETIME.)

如果您已经已经使用 DATETIME 类型，那么您必须不要按当地时间进行设置。您将需要更少地关注数据库，更多地关注您的应用程序代码 - 您没有在这里显示。问题和解决方案将根据语言有很大差异，因此请务必使用适用的应用程序代码来标记问题。

If you are already using a DATETIME type, then you must be not setting it by the local time to begin with. You'll need to focus less on the database, and more on your application code - which you didn't show here. The problem, and the solution, will vary drastically depending on language, so be sure to tag the question with the appropriate language of your application code.

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