这些问题在Stack Overflow和其姊妹网站
https://dba.stackexchange.com/上已经被多次提及。下次在发帖前请认真搜索。以下是简要回顾。
首先,您必须理解
offset-from-UTC和
time zone之间的区别。偏移量仅是与
UTC相差的小时数、分钟数和秒数。时区是特定地区人们使用的偏移量过去、现在和未来变化的历史记录。因此,使用时区总是优先于仅使用偏移量。
有两种不同的类型可用于保存时间戳:TIMESTAMP和TIMESTAMPTZ
并非完全如此。由SQL标准定义的实际类型为
TIMESTAMP WITH TIME ZONE
和
TIMESTAMP WITHOUT TIME ZONE
。其他名称是Postgres特定的同义词。出于清晰起见,建议使用标准名称。日期时间处理已经令人困惑了,不需要额外记住结尾处的
z
的歧义性。
SQL规范几乎没有涉及日期时间处理的主题。因此,在数据库实现之间行为差异很大。
Postgres的工作方式实际上非常简单。
对于类型为
TIMESTAMP WITH TIME ZONE
的列,任何具有UTC偏移或时区的输入都会自动调整为
UTC。在调整后,原始值的偏移/区域信息被丢弃。 "with time zone"实际上意味着“考虑到传入数据的时区”,而不是“存储带有时区”。
如果您必须知道原始偏移/区域,请在单独的列中自行存储。我建议使用ISO 8601标准格式的偏移或
时区的正确名称将其存储为文本。如果输入缺少任何区域/偏移指示器,则应用会话的当前默认时区,然后将其调整为UTC-正如我模糊地记得的那样; 您永远不应该传递缺少区域/偏移的输入!
对于没有时区的
TIMESTAMP WITHOUT TIME ZONE
列,忽略任何输入中的区域/偏移信息(如果有)。同样,在检索此值时,它没有区域/偏移。这种类型没有区域/偏移概念。当您的意图是存储时间轴上的时刻,不要使用此类型。此类型用于关于26-27个小时范围内
潜在时刻的模糊想法,例如“圣诞节从2018年12月25日午夜后开始”。除非您附加“在日本”,“在印度”或“在法国”(从而创建另一种类型的值,
TIMESTAMP WITH TIME ZONE
),否则这样的语句没有真正的意义。此类型还用于预约未来几周以上的时间,当时政客可能会突然更改其地区的偏移量时(他们意外地经常这样做,并且没有事先警告)。
注意:令人困惑的是,某些工具或驱动程序可能会将会话当前默认时区应用于任一类型的值。这包括pgAdmin。在我看来,这是一个可怕的反功能。出于善意,但是这样一个工具/驱动程序坐在您和Postgres之间,不应该注入其关于传输数据的“意见”。这样做会产生这样的错觉:检索到的数据携带了数据库内部的该区域,而事实上恰恰相反(实际上携带UTC或无区域/偏移量)。如果您的工具进行了这样的调整,则很可能由您的Postgres会话中的区域/偏移设置控制,如此处所述。
在处理日期时间的最佳实践中,应该使用UTC来思考、工作、存储、记录和交换数据。将其他区域视为该主题的简单变化。只有在业务逻辑或向用户展示时需要调整到时区。忘记你自己的地方时区。在桌子上放置第二个设置为UTC的时钟-说真的。
数据库现在设置了America/Sao_Paulo时区
服务器操作系统的默认时区应该是无关紧要的。作为程序员,永远不要依赖这样的默认值,因为它已经超出了您的控制,并且很容易更改。
在Java中,JVM具有自己的当前默认时区,与主机操作系统分开。JVM的当前默认值可以由JVM内部任何应用程序的任何线程中的任何代码在运行时随时更改。因此,永远不要依赖于当前默认值。始终通过传递可选参数明确指定所需/期望的时区。
如果时区B的另一个分销中心的主管检查此事件,则应看到如下内容:正如上文所述,数据库方面应该使用UTC。将其调整为用户期望的时区是用户界面任务,而不是数据库任务。就像国际化一样,您可以在数据库中存储某种键查找值,以在用户界面侧本地化为某种人类语言。
"TIMESTAMPTZ"仅保存时区的偏移量,而不是时区本身。
不,不正确。正如上文所述,“带时区的时间戳”类型在调整为UTC进行存储后会
丢弃偏移/区域信息。没有偏移量,没有区域,只有UTC时刻存储在列中 - 基本上是自
纪元参考以来微秒计数。
在我们的数据库中将所有类型从“TIMESTAMP”更改为“TIMESTAMPTZ”,并在保存时间戳的每个事件的插入中,应使用创建事件的中心的时区来保存事件的时区偏移量。
如果您要说您已经记录了来自各种时区的日期时间值到没有时区的“TIMESTAMP”列中,则会出现糟糕的混乱。您无法可靠地清理它,不能完全确定精度,因为您实际上不知道传递给数据库的输入的原始意图是什么区域/偏移量。您可以
猜测存储数据的原始意图,但永远不可能确定。
请向您的老板和利益相关者解释,这不是您造成的混乱。请解释说,设置此数据库和应用程序的人相当于将各种货币(如日元、加元、英镑和欧元)存储在其中,而没有记录每个金额所属的货币。
如果要猜测,您需要知道可能使用的时区名称。
在Java中,仅使用内置于Java 8及更高版本的java.time类。旧的日期时间类非常混乱,现在已经过时,被JSR 310中定义的java.time所取代。
确定您可能的时区。
ZoneId zoneSaoPaulo = ZoneId.of( "America/Sao_Paulo" ) ;
ZoneId zoneLisbon = ZoneId.of( "Europe/Lisbon" ) ;
ZoneId zoneKolkata = ZoneId.of( "Asia/Kolkata" ) ;
提取日期时间值作为
LocalDateTime
,这是一个缺乏任何时区/偏移概念的Java类的日期时间值。使用JDBC 4.2及更高版本,您可以直接与数据库交换
java.time对象。
LocalDateTime ldt = myResultSet.getObject( … , LocalDateTime.class ) ;
也许枚举类型是表示您的配送中心的适当方式。这假设列表在运行时不需要更改。
public enum DistributionCenter {
SAOPAULO( ZoneId.of( "America/Sao_Paulo" ) ) ,
LISBON( ZoneId.of( "Europe/Lisbon" ) ) ,
KOLKATA( ZoneId.of( "Asia/Kolkata" ) )
final public ZoneId zoneId ;
}
应用时区,生成一个
ZonedDateTime
对象。现在我们有了一个实际的时刻,时间线上的一个特定点。
DistributionCenter dc = … ;
ZonedDateTime zdt = ldt.atZone( dc.zoneId ) ;
将该值调整为UTC值。同一时刻,时间轴上的同一点,不同的挂钟时间。在您清楚理解这个概念之前,请勿继续进行项目。
Instant
类表示时间轴上以UTC为基准的瞬间,分辨率为纳秒(最多九位小数)。
Instant instant = zdt.toInstant() ;
你应该能够将你的
ZonedDateTime
对象传递给JDBC驱动程序,以进行调整为UTC。我只是想强调一点,我们最终在Postgres存储中得到的是UTC值。此外,我自己也会转换为
Instant
以便于调试 - 记住:UTC是“唯一真正的时间”。现在我们已确定了一个实际时刻,我们可以将其存储在数据库中。
myPreparedStatement.setObject( … , instant ) ;
请看这里:
请注意,所有这些代码都不依赖于您的服务器主机操作系统、您的Postgres集群或您的JVM的当前默认时区。
我必须将我们数据库中的所有列类型从TIMESTAMP更改为TIMESTAMPTZ
是的。记录实际时刻、历史片段的数据不应该存储在TIMESTAMP WITHOUT TIME ZONE
中。一些天真的程序员/DBA希望使用这种数据类型可以免除处理时区问题的麻烦。但事实上,这是一种“现在付款,或以后付款”的情况。不幸的是,你被困在了他们糟糕选择的泥潭中。
你可能可以通过Postgres过程完成同样的工作。Postgres确实比大多数数据库有更好的日期时间处理支持。然而,在日期时间处理方面,没有什么能打败java.time类库。并且,个人而言,我宁愿在Java中调试和练习这个特定的任务。
配送中心地理位置发生变化了
这很令人困惑,也不明智。企业确实应该将新位置标识为一个新中心,而不是相同的中心。如果你无法说服管理层这样做,我会在你的数据库和后台应用程序中进行操作。
关于java.time
java.time框架是内置于Java 8及更高版本中的。这些类取代了老旧的legacy日期时间类,如java.util.Date
、Calendar
和SimpleDateFormat
。
要了解更多信息,请参见Oracle教程。并在Stack Overflow上搜索许多示例和解释。规范为JSR 310。
Joda-Time 项目现在处于 维护模式,建议迁移到 java.time 类。
您可以直接使用 java.time 对象与数据库交换。使用符合 JDBC 4.2 或更高版本的 JDBC 驱动程序。无需字符串,无需 java.sql.*
类。Hibernate 5 和 JPA 2.2 支持 java.time。
如何获取 java.time 类?
TimeZone
配置参数仅从服务器时区初始化,之后服务器时区不起任何作用。TimeZone
不是数据库时区,它是会话时区的默认值。PostgreSQL没有数据库时区的概念。 - Laurenz AlbeTIMESTAMP WITHOUT TIMEZONE
列中保存了一个时区的时间戳(幸好)。如果一个数据中心发生地理位置和时区变化,未来创建的事件应该考虑新的时区,对于已经存储的内容:没有任何更改。如果因为先前的数据而无法创建新的数据中心,则我无法创建一个新数据中心。监管人员必须能够查看迁移前创建的数据以及新数据(每个数据尊重其各自不同的时区,假设数据中心的更改也会更改其时区)。 - Luiz