如何使用时区信息在 MySQL 中存储日期时间

2021-11-20 00:00:00 datetime timezone mysql

我有数千张在坦桑尼亚拍摄的照片，我想将每张照片的拍摄日期和时间存储在 MySQL 数据库中.但是，服务器位于美国，当我尝试存储位于春季夏令时(美国)无效"小时内的坦桑尼亚日期时间时遇到问题.坦桑尼亚不执行夏令时，因此该时间是实际有效时间.

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.

我读过:夏令时和时区最佳做法和MySQL 日期时间字段和夏令时时间——我如何引用额外"?小时? 和在 PHP/MySQL 中将日期时间存储为 UTC

但它们似乎都没有解决我的特殊问题.我不是 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.

相关文章