存储时间信息:需要时区吗? [英] Storing time information: Timezone required?

问题描述

我很想知道我在考虑的是不好的做法，还是因为这是一个具体而刻意的选择，所以实际上是一个不错的主意. 我想存储在特定城市中发生的事件的日期信息.我想将该数据存储为UTC时间戳. 简单地存储时间戳和城市ID/国家/地区ID(与特定时区相关联)，而不是为每个事件存储时区，不是一个好主意吗? 我问是因为时区可以更改，但是城市ID在数据库中永远不会更改.在时区更改(不太可能)事件中将服务器与最新时区同步之后，该事件将是独立的，并且不受更改影响.但是，假设某个时区更改了其边界，那么以前在该时区中发生的事件可能不在该时区之内. 这样做似乎不明智吗?我只是想知道，我一直在寻找最佳实践，但是在这种情况下，这实际上似乎是个不错的主意.这样做之所以特别有效，是因为应用程序设计模型永远不会改变-事件始终会与特定城市相关联.

I'm curious to know if what I'm considering is bad practice, or if since this is a specific and deliberate choice it is actually a decent idea. I want to store information for dates for events that occur in specific cities. I want to store that data as UTC timestamps. Wouldn't it be a good idea to simply store the timestamp and the city id/country id (which is associated with a specific timezone), rather than storing the timezone for each event? I ask because timezones can change, but city IDs would never change in the DB. Once the server is synced with the latest timezone in the (unlikely) event of a timezone change, the event would be independent and unaffected by that change. However, say a timezone changes its boundaries, then events that occurred in that timezone previously could be outside of it. Does it seem unwise to do this? I'm just wondering, and I've been scouring for best practices but in this case this actually seems like an OK idea. This works particularly because the application design model would never change- events will ALWAYS be associated with a specific city.

基本流程为:

• 带有日期/位置的事件数据以ISO-8601 YYYY-MM-DD字符串之类的标准格式进入系统.

• Event data with date/location comes into the system in a standard format like ISO-8601 YYYY-MM-DD string.

系统将日期转换为UTC时间戳，并使用该时间戳和事件的城市ID将日期与事件一起存储.

System converts date to UTC timestamp and stores the date with the event using that timestamp and the city ID for the event.

当用户请求查看该事件时，系统会提取与该事件相关的时间戳和城市信息，并使用城市的时区来相应地格式化显示的日期.

When a user requests to view that event, the system pulls the timestamp and city information associated with that event, and uses the city's timezone to format the date accordingly on display.

这是一个可怕的主意吗?这样做有好处吗?存储TZ偏移的概念是否与消除此问题的想法相同?

Is this a terrible idea? Is there a benefit to this, and is the concept of storing the TZ Offset the same idea to eliminate this issue?

推荐答案

无论采用哪种方式，它都会根据更改的内容以不同的方式失败.

Whichever way you do it, it will fail in different ways depending on what is changing.

1. 如果您在相应时区中将时间戳记存储为2013-12-29 12:34:56 America/New_York，则如果布朗克斯突然以不同的偏移量开始自己的时区America/New_York_Bronx并且您的事件恰好在布朗克斯中，则此操作将失败.

1. If you store timestamps in the according timezone as 2013-12-29 12:34:56 America/New_York, this will fail if, say, the Bronx suddenly starts their own timezone America/New_York_Bronx with a different offset and your event happened to be in the Bronx.

确定发生这种情况的可能性以及失败的严重程度.

Decide how likely this is and how bad a failure would be.

如果将时间戳记存储在UTC中，并且事件发生的时区正在重新定义其偏移量(例如，夏令时的日期偏移或完全偏移到其他偏移量)，则事件时间可能与实际时间有所不同该位置的时钟时间.如果您将事件的2013-12-29 12:34:56 UTC存储在德国柏林的13:34:56，并且柏林转移了夏令时，那么2013-12-29 12:34:56 UTC现在可能对应于柏林当地时间14:34:56，而该事件实际上仍在发生在当地时间13:34.

If you store timestamps in UTC and the timezone in which the event is happening is redefining their offset (e.g. shifting DST dates around, or entirely shifting to a different offset), the event time may differ from the actual wall clock time at that location. If you store 2013-12-29 12:34:56 UTC for an event at 13:34:56 in Berlin, Germany, and Berlin shifts their DST around, 2013-12-29 12:34:56 UTC may now correspond to 14:34:56 Berlin local time, while the event is still actually happening at 13:34 local time.

确定发生这种情况的可能性以及失败的严重程度.

Decide how likely this is and how bad a failure would be.

如果存储UTC时间戳并将其链接到实际位置，然后再链接到时区，则可以解决这两个问题.但是为此，您将必须存储精确的物理位置，而不仅仅是纽约"，否则，您将只剩下第一种情况，还有一个中间步骤.如果您确实存储了准确的物理位置，并且拥有将该位置解析为时区的精确方法，并且使时区数据库保持最新状态，则可以处理几乎所有的更改方案.

If you store the UTC timestamp and link it to a physical location which you then link to a timezone, you can counteract both problems. But for this you'll have to store the precise physical location, not just "New York", otherwise you just have case 1. with one more intermediate step. If you do store the precise physical location and have a precise way to resolve this location to a timezone and you keep your timezone database up to date, you can handle pretty much all change scenarios.

确定这有多实用，以及这种额外的精度对您来说有多少价值.

Decide how practical this is and how much this extra precision is worth to you.

这篇关于存储时间信息:需要时区吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！