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

How to store a datetime in MySQL with timezone info

提问by mkosmala

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.

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

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 alwayscome 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.

我不愿意设置会话时间，因为我知道当有人有时忘记设置会话时间并且将时间全部弄错时会出现问题。而且我无权更改服务器的任何内容。

I've read: Daylight saving time and time zone best practicesand MySQL datetime fields and daylight savings time -- how do I reference the "extra" hour?and Storing datetime as UTC in PHP/MySQL

我读过： 夏令时和时区最佳实践以及 MySQL 日期时间字段和夏令时——我如何引用“额外”小时？并 在 PHP/MySQL 中将日期时间存储为 UTC

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.

但它们似乎都没有解决我的特殊问题。我不是 SQL 专家；有没有办法在设置 DATETIME 时指定时区？我一个都没见过。否则，非常感谢有关如何解决此问题的任何建议。

Edit: 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")

And I get the error:

我得到错误：

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.

该日期和时间存在于坦桑尼亚，但不存在于数据库所在的美国。

采纳答案by Matt Johnson-Pint

You said:

你说：

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

我希望它们总是以坦桑尼亚时间出现，而不是在各种合作者所在的当地时间。

If this is the case, then you should notuse UTC. All you need to do is to use a DATETIMEtype in MySQL instead of a TIMESTAMPtype.

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

From the MySQL documentation:

从 MySQL 文档：

MySQL converts TIMESTAMPvalues 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.)

MySQL 将TIMESTAMP值从当前时区转换为 UTC 进行存储，然后从 UTC 转换回当前时区以进行检索。（这不会发生在其他类型，例如DATETIME.）

If you are alreadyusing a DATETIMEtype, 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.

如果您已经在使用一种DATETIME类型，那么您一定不要在本地时间开始设置它。您将需要更少地关注数据库，而更多地关注您的应用程序代码 - 您没有在此处显示。问题和解决方案会因语言而异，因此请务必使用应用程序代码的适当语言标记问题。

回答by Matt Mc

None of the answers here quite hit the nail on the head.

这里的答案都没有一针见血。

How to store a datetime in MySQL with timezone info

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

Use two columns: DATETIME, and a VARCHARto hold the time zone information, which may be in several forms:

使用两列：DATETIME, 和 aVARCHAR来保存时区信息，可能有几种形式：

A timezoneor locationsuch as America/New_Yorkis the highest data fidelity.

阿时区或位置，例如America/New_York是最高的数据的保真度。

A timezone abbreviationsuch as PSTis the next highest fidelity.

一个时区的缩写，如PST为下一个最高的保真度。

A time offsetsuch as -2:00is the smallest amount of data in this regard.

诸如此类的时间偏移-2:00是这方面的最小数据量。

Some key points:

一些关键点：

• Avoid TIMESTAMPbecause it's limited to the year 2038, and MySQL relates it to the server timezone, which is probably undesired.
• A time offset should not be stored naively in an INTfield, because there are half-hour and quarter-hour offsets.

• 避免，TIMESTAMP因为它仅限于 2038 年，并且 MySQL 将其与服务器时区相关联，这可能是不受欢迎的。
• 时间偏移不应天真地存储在INT字段中，因为存在半小时和四分之一小时的偏移。

---

---

If it's important for your use case to have MySQL compareor sortthese dates chronologically, DATETIMEhas a problem:

如果让 MySQL按时间顺序比较或排序这些日期对您的用例很重要，则DATETIME有一个问题：

'2009-11-10 11:00:00 -0500'is before '2009-11-10 10:00:00 -0700'in terms of "instant in time", but they would sort the other way when inserted into a DATETIME.

'2009-11-10 11:00:00 -0500''2009-11-10 10:00:00 -0700'在“即时”方面是之前的，但是当插入到DATETIME.

You can do your own conversion to UTC. In the above example, you would then have
'2009-11-10 16:00:00'and '2009-11-10 17:00:00'respectively, which would sort correctly. When retrieving the data, you would then use the timezone info to revert it to its original form.

您可以自己转换为 UTC。在上面的示例中，您将分别拥有
'2009-11-10 16:00:00'和'2009-11-10 17:00:00'，它们将正确排序。检索数据时，您将使用时区信息将其恢复为原始形式。

One recommendationwhich I quite like is to have threecolumns:

我非常喜欢的一项建议是有三列：

• local_time DATETIME
• utc_time DATETIME
• time_zone VARCHAR(X)where X is appropriate for what kind of data you're storing there. (I would choose 64 characters for timezone/location.)

• local_time DATETIME
• utc_time DATETIME
• time_zone VARCHAR(X)其中 X 适合您在那里存储的数据类型。（我会为时区/位置选择 64 个字符。）

An advantage to the 3-column approach is that it's explicit: with a single DATETIMEcolumn, you can't tell at a glance if it's been converted to UTC before insertion.

3 列方法的一个优点是它是明确的：对于单个DATETIME列，您无法一眼看出它在插入之前是否已转换为 UTC。

---

---

Regarding the descent of accuracy through timezone/abbreviation/offset:

关于通过时区/缩写/偏移量的准确性下降：

• If you have the user's timezone/locationsuch as America/Juneau, you can know accurately what the wall clock time is for them at any point in the past or future (barring changes to the way Daylight Savings is handled in that location). The start/end points of DST, and whether it's used at all, are dependent upon location, so this is the only reliable way.
• If you have a timezone abbreviationsuch as MST, (Mountain Standard Time) or a plain offset such as -0700, you will be unable to predict a wall clock time in the past or future. For example, in the United States, Colorado and Arizona both use MST, but Arizona doesn't observe DST. So if the user uploads his cat photo at 14:00 -0700during the winter months, was he in Arizona or California? If you added six months exactly to that date, would it be 14:00or 13:00for the user?

• 如果您有用户的时区/位置，例如America/Juneau，您可以准确地知道他们在过去或未来的任何时间的挂钟时间（除非改变该位置处理夏令时的方式）。DST 的起点/终点，以及是否使用它，取决于位置，因此这是唯一可靠的方法。
• 如果您有一个时区缩写，例如 MST，（山地标准时间）或一个简单的偏移量，例如-0700，您将无法预测过去或未来的挂钟时间。例如，在美国，科罗拉多州和亚利桑那州都使用 MST，但亚利桑那州不遵守 DST。那么如果用户14:00 -0700在冬季上传他的猫照片，他是在亚利桑那州还是加利福尼亚州？如果您在该日期准确添加六个月，是为用户14:00还是13:00为用户？

These things are important to consider when your application has time, dates, or scheduling as core function.

当您的应用程序将时间、日期或日程安排作为核心功能时，考虑这些事情很重要。

---

---

References:

参考：

• MySQL Date/Time Reference
• The Proper Way to Handle Multiple Time Zones in MySQL
  ^{(Disclosure: I did not read this whole article.)}

• MySQL 日期/时间参考
• 在 MySQL 中处理多个时区的正确方法
  ^{（披露：我没有阅读整篇文章。）}

回答by Hai Phan

MySQL stores DATETIME withouttimezone information. Let's say you store '2019-01-01 20:00:00' into a DATETIME field, when you retrieve that value you're expected to knowwhat timezone it belongs to.

MySQL 存储没有时区信息的DATETIME 。假设您将 '2019-01-01 20:00:00' 存储到 DATETIME 字段中，当您检索该值时，您应该知道它属于哪个时区。

So in your case, when you store a value into a DATETIME field, make sure it is Tanzania time. Then when you get it out, it will be Tanzania time. Yay!

因此，在您的情况下，当您将值存储到 DATETIME 字段时，请确保它是坦桑尼亚时间。然后等你拿出来，就是坦桑尼亚时间了。好极了！

Now, the hairy question is: When I do an INSERT/UPDATE, how do I make sure the value is Tanzania time? Two cases:

现在，棘手的问题是：当我执行 INSERT/UPDATE 时，如何确保该值是坦桑尼亚时间？两种情况：

1. You do INSERT INTO table (dateCreated) VALUES (CURRENT_TIMESTAMP or NOW()).

2. You do INSERT INTO table (dateCreated) VALUES (?), and specify the current time from your application code.

1. 你做INSERT INTO table (dateCreated) VALUES (CURRENT_TIMESTAMP or NOW())。

2. 您这样做INSERT INTO table (dateCreated) VALUES (?)，并从您的应用程序代码中指定当前时间。

CASE #1

情况1

MySQL will take the current time, let's say that is '2019-01-01 20:00:00' Tanzania time. Then MySQL will convert it to UTC, which comes out to '2019-01-01 17:00:00', and store thatvalue into the field.

MySQL 将采用当前时间，假设是 '2019-01-01 20:00:00' 坦桑尼亚时间。然后 MySQL 会将其转换为 UTC，即 '2019-01-01 17:00:00'，并将该值存储到该字段中。

So how do you get the Tanzania time, which is '20:00:00', to store into the field? It's not possible. Your code will need to expect UTC time when reading from this field.

那么如何将坦桑尼亚时间（即“20:00:00”）存储到字段中呢？这是不可能的。从该字段读取时，您的代码需要预期 UTC 时间。

CASE #2

案例#2

It depends on what type of value you pass as ?. If you pass the string '2019-01-01 20:00:00', then good for you, that's exactly what will be stored to the DB. If you pass a Date object of some kind, then it'll depend on how the db driver interprets that Date object, and ultimate what 'YYYY-MM-DD HH:mm:ss' string it provides to MySQL for storage. The db driver's documentation should tell you.

这取决于您作为 传递的值的类型?。如果您传递字符串 '2019-01-01 20:00:00'，那么对您有好处，这正是将存储到数据库的内容。如果您传递某种类型的 Date 对象，那么它将取决于db 驱动程序如何解释该 Date 对象，以及它最终提供给 MySQL 用于存储的 'YYYY-MM-DD HH:mm:ss' 字符串。数据库驱动程序的文档应该告诉你。

回答by álvaro González

All the symptoms you describe suggest that you never tell MySQL what time zone to use so it defaults to system's zone. Think about it: if all it has is '2011-03-13 02:49:10', how can it guess that it's a local Tanzanian date?

您描述的所有症状都表明您永远不会告诉 MySQL 使用哪个时区，因此它默认为系统区域。想一想：如果它只有'2011-03-13 02:49:10'，它怎么能猜到这是一个当地的坦桑尼亚约会呢？

As far as I know, MySQL doesn't provide any syntax to specify time zone information in dates. You have to change it a per-connection basis; something like:

据我所知，MySQL 没有提供任何语法来指定日期中的时区信息。您必须在每个连接的基础上更改它；就像是：

SET time_zone = 'EAT';

If this doesn't work (to use named zones you need that the server has been configuredto do so and it's often not the case) you can use UTC offsets because Tanzania does not observe daylight saving timeat the time of writing but of course it isn't the best option:

如果这不起作用（要使用命名区域，您需要将服务器配置为这样做，但通常情况并非如此）您可以使用 UTC 偏移量，因为坦桑尼亚在撰写本文时不遵守夏令时，但当然这不是最好的选择：

SET time_zone = '+03:00';

回答by Geoff

I once also faced such an issue where i needed to save data which was used by different collaborators and i ended up storing the time in unix timestamp form which represents the number of seconds since january 1970 which is an integer format. Example todays date and time in tanzania is Friday, September 13, 2019 9:44:01 PMwhich when store in unix timestamp would be 1568400241

我曾经也遇到过这样一个问题，我需要保存不同合作者使用的数据，我最终以 unix 时间戳形式存储时间，该形式表示自 1970 年 1 月以来的秒数，这是一种整数格式。示例今天在坦桑尼亚的日期和时间是Friday, September 13, 2019 9:44:01 PM当存储在 unix 时间戳中时1568400241

Now when reading the data simply use something like php or any other language and extract the date from the unix timestamp. An example with php will be

现在在读取数据时，只需使用 php 或任何其他语言之类的东西，并从 unix 时间戳中提取日期。一个 php 示例将是

echo date('m/d/Y', 1568400241);

This makes it easier even to store data with other collaborators in different locations. They can simply convert the date to unix timestamp with their own gmt offset and store it in a integer format and when outputting this simply convert with a

这甚至可以更轻松地与不同位置的其他协作者存储数据。他们可以简单地将日期转换为具有自己的 gmt 偏移量的 unix 时间戳并将其存储为整数格式，并在输出时简单地转换为