如何在数据库中保存将来的日期！ [英] How to save future(!) dates in a database

问题描述

这个问题专门针对将来的日期和时间(对于过去的值，UTC无疑是首选).

我想知道是否有人建议将"未来"日期和时间保存在MySQL数据库中的最佳"方法(或者通常也是 >)，尤其是在该列可以容纳来自不同时区的时间的情况下.考虑到时区规则可能会更改UTC可能不是最佳选择.

I'd wonder if anybody had a suggestion as to the "best" way to save a future date and time in a MySQL database (or for that matter also generally), particularly in a context where the column can hold times from different timezones. Considering that timezone rules may change UTC might not be the best option.

到目前为止，我找到的最可行的选择是将其与位置(例如"America/*")一起另存为该位置的本地时间中的文本.不幸的是，此解决方案可能更容易损坏数据，并且绝对不太方便计算.

The most feasible option I found so far would be the save it as text in the location's local time, together with the location (eg. "America/*"). Unfortunately this solution might be more prone to data corruption and is definitely less convenient for calculations.

有人能想到更好的东西吗?

Could anybody think of something better?

预先感谢

推荐答案

首先，我之前已经对此进行了详尽的介绍，因此请阅读我的回答此处，以及

First, I've written about this in extensive detail before, so please read my answers here and here, as well as this blog post by Lau Taarnskov.

针对MySQL，您通常不希望在将来事件的本地时间使用 TIMESTAMP 字段，因为它会在写入时从会话的时区转换为UTC，并在读取时从UTC转换回会话的时区.即使这些是相同的时区ID(不一定是相同的)，也无法保证在您写入数据和事件之间的时区中的一个或两个时区中，时区数据不会更改发生.

With specific regard to MySQL, you generally don't want to use a TIMESTAMP field for the local time of a future event, as it will convert from the session's time zone to UTC at write time, and convert back from UTC to the session's time zone at read time. Even if these are the same time zone ids (which they don't have to be), there's no guarantee that the time zone data won't change for one or both of the time zones between when you write the data and when the event takes place.

请使用 DATETIME 字段，该字段不进行隐式时区转换.您可以得到您编写的确切值.存储事件的本地时间，并存储包含事件的时区标识符的 VARCHAR 字段.这是保留用户原始意图的唯一方法.

Instead, use a DATETIME field, which does no implicit time zone conversions. You get the exact value out that you wrote. Store the local time of the event, and store a VARCHAR field containing the time zone identifier for the event. This is the only way to retain the user's original intent.

我以前给出的答案都描述了理智和边缘案例.

Rationale and edge cases are all described in the answers I gave previously.

这篇关于如何在数据库中保存将来的日期！的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！