地理位置分散的服务器，PostgreSQL和JPA [英] Geographically dispersed servers, PostgreSQL, and JPA

问题描述

我的数据库服务器的TIMEZONE是UTC.我在与数据库进行交互的各个时区中都有Java服务器.天真的我发现，如果我有一个TIMESTAMP字段，实际上我没有办法通过Java服务器的JPA代码将其设置为UTC.我想我天真地假设，如果JDBC总是将日期与TZ信息一起发送，并且数据库服务器可以根据其时区进行适当的转换.但不是. :(

My database server's TIMEZONE is UTC. I have Java servers in various time-zones that interact with the database. Naively I discovered that if I have a TIMESTAMP field that there is really no way for me to set it from the Java server's JPA code as UTC. I guess I naively assumed that if JDBC would always send dates over with TZ info and the database server could appropriately convert in or out based on its time zone. But no. :(

这是磨擦.我可能在各个时区中都有Java服务器与此数据库服务器进行交互.如果在东海岸的晚上9点和西海岸的晚上6点发生了一些事情，我希望将这两个事件同时存储在数据库中.嗯，对吗?

Here's the rub. I may have Java servers in various timezones interacting with this database sever. If something happens at 9pm on the east coast and 6pm on the west coast I would like both of those events to be stored in the database at the same time. Duh, right?

那是什么解决方案，我想我可以将所有Java服务器都放在UTC中.但是似乎应该有一个不那么繁重的解决方案.我想我也可以在每笔交易上设置时区，但是.

So what's the solution, I suppose I could put all my Java servers in UTC. But there seems like there should be a less heavy handed solution. I guess I could also SET TIME ZONE on every transaction, but ugh.

我知道我会得到一些回答，说我应该只将long存储在数据库中，并且虽然我很欣赏此回答的准确性，但我确实希望在数据库表中保留TIMEZONE和DATE字段.因此，我正在寻找其他类型的答案.谢谢.

I know I will get some answers saying I should only store longs in the database, and while I appreciate the veracity of this answer I do want to retain TIMEZONE and DATE fields in my database tables. So I am looking for other types of answers. Thanks.

推荐答案

解决方案是对这些时间点使用TIMESTAMPTZ字段.诸如何时创建用户"之类的内容永远不应该与TIMESTAMP字段一起存储，因为默认情况下它不保存TZ信息. JDBC驱动程序可以非常随意地处理这些问题.例如，考虑以下内容:

The solution is to use TIMESTAMPTZ fields for these instants in time. Something like "when was the user created" should never be stored with a TIMESTAMP field as by default it does not hold TZ information. The JDBC driver handles these quite arbitrarily. For example, consider the following:

假定您的JVM在数据库服务器位于UTC时位于America/Los_Angeles区域中.

Assume your JVM is in the America/Los_Angeles zone while your database server is in UTC.

然后创建下表:

 CREATE TABLE test (
   id   INTEGER,
   ts   TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
   tswz TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
 );

如果您是通过PSQL发出的:

If you issue from PSQL:

 INSERT INTO test(id) values(1);

您将获得"ts"和"tswz"相同的值.两者都是当前UTC时间.但是，如果使用JDBC驱动程序从Java执行SAME EXACT查询，则"ts"将是洛杉矶的当前时间，而"tswz"将是UTC时间.

You will get identical values for "ts" and "tswz". Both will be the current UTC time. However if you perform the SAME EXACT query from Java using the JDBC driver then "ts" will be the current time in Los Angeles and "tswz" will be the UTC time.

在这种情况下，我不知道驱动程序如何将JVM时区传达给服务器，因为我们默认服务器上的字段.他们说他们没有为会议设置时区，但必须这样做.不管哪种方式，如果您使用TIMESTAMPTZ字段，那么无论碰巧处于哪个时区，您都将从任何JVM获取相同的时刻.

I don't know HOW the driver conveys to the server the JVM timezone in this case because we are defaulting the field on the server. They say they do not set the time zone for the session, but they must. Either way, if you use a TIMESTAMPTZ field then you will get the same instants from any JVM regardless of what timezone it happens to be in.

这篇关于地理位置分散的服务器，PostgreSQL和JPA的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！