如何在时区中储存和显示日期？ [英] How can I store and display dates in the timezone as they were originally given?

问题描述

我有一个正在从不同时区的客户端提供数据的服务器。数据Feed包含人员，他们的出生日期和其他事件日期。为了我们的目的，如果我们可以将日期存储给我们，这将是方便的。

例如，如果客户在加利福尼亚州，并告诉我们该人的出生日期是5月31日，我们希望将其存储在数据库中，为5月31日1999年，太平洋时间。这样，无论你在哪个时区，你都可以看到这个人出生于5月31日。

同时，我们希望能够查询这些数据，以便能够找出像这个人是未成年人还是这个事件不到24小时前发生？

客户端通过基于http的休息API向我们发送数据，服务器用Java编写（使用eclipselink），数据库是postgresql是否可以满足这些要求？

通常，人们会将所有内容存储为UTC，但我觉得这将是一个坏主意，因为我们会失去原始数据的时区。

解决方案

UTC是要走的路。对于 timestamptz （带有时区的时间戳记）输入值的时区仅用作Postgres在内部计算UTC的修饰符（对于 timestamp [without时区] 任何附加的时区将被忽略！）。时区不保存，您需要另存 以了解 在世界一些事情发生了。

如果你这样做，你也可以存储本地时间戳。只是不要混淆哪个是哪个。将所有内容转换为UTC（自动为 timestamptz ），或将所有内容转换为本地时间（定义本地：本地？本地到数据库服务器？本地到用户？）。

具体而言，存储确切的时区名称（或其引用）而不仅仅是太平洋时间。这是更准确的夏令时，闰秒或其他事件。

详细说明：

• 在Rails和PostgreSQL中完全忽略时区

关于时区名称和缩写：

• 时区名称与相同的属性在应用于时间戳时产生不同的结果

关于时区处理：

• 在选择预定项目时会计Postgres中的DST


• 在PostgreSQL中保留时区timestamptz类型

I have a server that is being fed data from clients in different timezones. The data feed contains people, their date of birth and other dates of events. For our purposes, it would be convenient if we could just store the dates as their given to us.

For example, if the client is in California and it tells us the person's date of birth is May 31st, we'd like to store it in the database as May 31st 1999, pacific time. This way, no matter what timezone you're in, you can see that the person was born on May 31st.

At the same time, we want to be able to query this data to be able to figure out things like, "Is this person a minor" or "did this event happen less than 24 hours ago?

The clients are sending us data over a http based rest API. The server is written in Java (using eclipselink). The database is postgresql. Is it possible to satisfy these requirements?

Typically, people say to store everything as UTC, but I feel like that would be a bad idea because we'd lose the timezone of the original data.

解决方案

UTC is the way to go. For timestamptz (timestamp with time zone) the time zone of input values only serves as modifier for Postgres to calculate UTC internally. (For timestamp [without time zone] any appended time zone would be ignored!). The time zone is not saved. You need to save it additionally to know where in the world something happened.

If you do that, you might as well store local timestamps. Just don't get confused which is which. Either convert everything to UTC (happens automatically for timestamptz), or convert everything to local time (define "local": your local? local to the db server? local to the user?).

In particular, rather store the exact time zone name (or a reference to it) than just "pacific time". This is more exact for daylight saving time, leap seconds or other events.

Detailed explanation:

• Ignoring timezones altogether in Rails and PostgreSQL

About time zone names and abbreviations:

• Time zone names with identical properties yield different result when applied to timestamp

About time zone handling:

• Accounting for DST in Postgres, when selecting scheduled items
• Preserve timezone in PostgreSQL timestamptz type

这篇关于如何在时区中储存和显示日期？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！