mysql日期可以保存时区_关于mysql：如何以UTC和本地时区存储日期时间

什么是存储日期时间的实用方法，以便我可以让用户查看/查询自己当地时间的数据，同时保留有关原始日期时间的信息。

基本上，用户希望能够查询(从他们自己的本地时间)从各个时区的系统收集的数据。但偶尔，他们想知道数据是在原始系统中的18:00创建的。当来自世界不同地区的用户就同一事件进行通信时，它会有所帮助。

User1: What? We don't have any data for 20:00

User2: Dude, it says 20:00 right there on my screen.

User1: Wait, what timezone are you? What's the UTC-time?

User2: What is UTC? Is that something with computers?

User1: OMFG! *click*

我正在寻找有关如何存储数据的建议。

我正在考虑以UTC格式存储所有日期时间并添加包含原始时区名称的附加列，其形式允许我使用mysql CONVERT_TZ或Java中的对应项。然后，应用程序将用户输入的日期转换为UTC，我可以轻松查询数据库。所有日期也可以轻松转换为应用程序中的用户本地时间。使用原始时区列我也可以显示原始日期时间。

但是，这意味着对于我所拥有的每个日期时间，我需要一个额外的列...

start_time_utc datetime

start_time_tz  varchar(64)

end_time_utc   datetime

end_time_tz    varchar(64)

我在这里走在正确的轨道上吗？

是否有人使用这些数据分享他们的经验？

(我将使用MySQL 5.5 CE)

更新1

数据将以xml文件的形式提供，其中每个条目在某个本地时区具有日期时间。因此，只有一个插入过程，在一个地方运行。

一旦加载到数据库中，它将在一些Web应用程序中呈现给不同时区的用户。对于大多数用例，感兴趣的数据也来自与查看数据的用户相同的时区。对于一些更复杂的用例，一系列事件是互连的并跨越多个时区。因此，用户希望能够谈论事件，以便在另一个时间调查可能的原因/后果。不是UTC，不是他们当地的时间。

对于手头的问题，我相信你的解决方案很好。

MySQL是否有DateTimeOffset，如MSSQL(指定时间戳及其与UTC的偏移量的数据类型)？ 如果没有，那么您的解决方案是最接近的等价物，仅在所需的字段数量上有所不同(1对2)。

不，MySQL不支持TIMESTAMP WITH TIMEZONE(这是ANSI数据类型)

@horse，我正在寻找类似的东西(我在Oracle中进行过实验)但在MySQL中找不到任何东西。 您对MySQL中的"timezoned"数据有经验吗？

不，我没有。 幸运的是，我不需要经常使用MySQL。

@Ronnis，很抱歉复活，但你到底做了什么？

@epoch，我们最终存储了一对列，就像在我的例子中一样。

@Ronnis，谢谢你，现在我需要找到500多个表的架构，如果你有任何输入，我的问题在这里：stackoverflow.com/questions/28146521/…:)

由于用户可以居住在不同的时区，甚至可以从一个时区移动到另一个时区，最佳做法是以UTC格式存储日期和时间，并在显示时转换为用户的时区。

手册中有一节仅供参考，关于时间戳：

TIMESTAMP values are converted from

the current time zone to UTC for

storage, and converted back from UTC

to the current time zone for

retrieval. (This occurs only for the

TIMESTAMP data type, not for other

types such as DATETIME.) By default,

the current time zone for each

connection is the server's time. The

time zone can be set on a

per-connection basis, as described in

Section 9.6,"MySQL Server Time Zone

Support". As long as the time zone

setting remains constant, you get back

the same value you store. If you store

a TIMESTAMP value, and then change the

time zone and retrieve the value, the

retrieved value is different from the

value you stored. This occurs because

the same time zone was not used for

conversion in both directions. The

current time zone is available as the

value of the time_zone system

variable.

http://dev.mysql.com/doc/refman/5.5/en/timestamp.html

因此，您可以在客户端上使用：SET time_zone = timezone;来设置时区。 然后，所有查询都会将时间戳转换为正确的时区。 除了设置时区(我认为甚至可能是JDBC连接字符串中的参数)之外，无需在Java中执行任何复杂的操作

我仍然需要存储原始时区，以便在另一个本地时区显示(参见对话框)。

也许我误会了。 就像Eldad所说的那样，将原始时区存储在列中是您唯一的选择......

您可以随时将zulu时间作为所有计算的基础。