以 UTC 存储时间总是一个好主意，还是在这种情况下以本地时间存储更好? [英] Is it always a good idea to store time in UTC or is this the case where storing in local time is better?
问题描述
Generally, it is the best practice to store time in UTC and as mentioned in here and here.
假设有一个重复发生的事件，比如结束时间总是在同一本地时间，比如 17:00，无论该时区是否开启或关闭夏令时.并且还要求在特定时区的 DST 打开或关闭时不要手动更改时间.还要求每当任何其他系统通过 API(即 GetEndTimeByEvent)询问结束时间时，它始终以 UTC 格式发送结束时间.
Suppose there is a re-occurring event let's say end time which is always at the same local time let's say 17:00 regardless of whether there is Daylight saving is on or off for that time zone. And also there is a requirement not to change the time manually when DST turns ON or OFF for particular time zone. It is also a requirement that whenever end time is asked by any other systems through API (i.e. GetEndTimeByEvent) it always sends the end time in UTC format.
方法 1:如果决定以UTC存储，它可以存储在数据库表中，如下所示.
Approach 1: If it is decided to store in UTC it can be stored in the database table as below.
Event UTCEndTime
=====================
ABC 07:00:00
MNO 06:00:00
PQR 04:00:00
对于第一个事件 ABC，UTC 的结束时间是 07:00 am，如果转换为在 2012 年 7 月 1 日从 UTC 显示为本地时间，则将导致本地时间为 17:00，如果转换为 10 月 10 日-2012(该时区的 DST 开启日期)然后将导致下午 6 点，这是不正确的结束时间.
For the first event ABC, end time in UTC is 07:00 am which if converted to display from UTC to local time on 1-July-2012 it will result into 17:00 local time and if converted on 10-Oct-2012 (the date when DST is ON for the time zone) then will result into 6 pm which is not correct end time.
我能想到的一种可能方法是将 DST 时间存储在附加列中，并在时区启用 DST 时使用该时间.
One possible way I could think is to store DST time in the additional column and using that time when the timezone has DST ON.
方法 2:但是，如果它存储为本地时间，例如对于事件 ABC，它将始终为任何日期的 17:00，因为没有从 UTC 到本地时间的转换.
Approach 2: However, if it is stored as Local time as below for example for event ABC it will be always 17:00 on any date as there is no conversion to from UTC to local time.
Event LocalEndTime
=======================
ABC 17:00:00
MNO 16:00:00
PQR 14:00:00
应用层将本地时间转换为UTC时间，通过(API GetEndTimeByEvent)发送给其他系统.
And an application layer converts local time to UTC time to send to other systems through (API GetEndTimeByEvent).
在这种情况下，以 UTC 格式存储时间是否仍然是一个好主意?如果是，那么如何获得恒定的当地时间?
推荐答案
我认为为了回答这个问题，我们应该考虑使用 UTC 来存储时间戳的好处.
I think that in order to answer that question, we should think about the benefits of using UTC to store timestamps.
我个人认为这样做的主要好处是时间总是(大部分)保证一致.换句话说，无论何时更改时区或应用 DST，您都不会及时返回或来回.这在文件系统、日志等中特别有用.但是它在您的应用程序中必要吗?
I personally think that the main benefit to that is that the time is always (mostly) guaranteed to be consistent. In other words, whenever the timezone is changed, or DST applied, you don't get back or forth in time. This is especially useful in filesystems, logs and so on. But is it necessary in your application?
想想两件事.首先，关于DST时钟偏移的时间.您的事件是否有可能发生在凌晨 2 点到 3 点之间(时钟转换完成的那一天)?那应该怎么办?
Think of two things. Firstly, about the time of DST clock shift. Is it likely that your events are going to occur between 2 AM and 3 AM (on the day the clock shift is done)? What should happen then?
其次，应用程序是否会受到实际时区变化的影响?换句话说，您是否打算使用它从伦敦飞往华沙，并适当地更改您的计算机时区?在这种情况下会发生什么?
Secondly, will the application be subject to actual timezone changes? In other words, are you going to fly with it from London to Warsaw, and change your computer timezone appropriately? What should happen in that case?
如果您对这两个问题的回答都是否，那么您最好使用当地时间.它将使您的应用程序更简单.但如果你至少一次回答是，那么我认为你应该多考虑一下.
If you answered no to both of those questions, then you're better with the local time. It will make your application simpler. But if you answered yes at least once, then I think you should give it more thinking.
这就是数据库的全部.另一件事是应用程序内部使用的时间格式，这应该取决于您实际将使用该时间做什么.
And that was all about the database. The other thing is the time format used internally by the application, and that should depend on what actually you will be doing with that time.
您提到它通过 API 公开时间.应用程序会在每次请求时查询数据库吗?如果您将时间在内部存储为 UTC，则您要么需要这样做，要么确保在 DST/时区更改时调整/修剪缓存时间.
You mentioned it exposing the time via an API. Will the application query the database on every request? If you store the time internally as UTC, you will either need to do that or otherwise ensure that on DST/timezone change the cached times will be adjusted/pruned.
它会对时间本身做些什么吗?比如打印事件将在 8 小时后 或暂停大约在那个时间?如果是，那么 UTC 可能会更好.当然，您需要考虑所有上述问题.
Will it do anything with the time itself? Like printing the event will occur in 8 hours or suspending itself for circa that time? If yes, then UTC will probably be better. Of course, you need to think of all the forementioned issues.
这篇关于以 UTC 存储时间总是一个好主意，还是在这种情况下以本地时间存储更好?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
