Postgres UTC到Java ZonedDateTime [英] Postgres UTC to Java ZonedDateTime

问题描述

对我原来的问题的跟进问题。

Follow-up question to my original issue.

因为我知道所使用的任何日期时间都是针对已知的时区，而不是用户可能提交请求的地方，我会采用LocalDateTime，转换为UTC并保留。然后，当检索到约会时，我将保存的时间转换为会议位置时区（存储在db中）。但是，似乎我保存的值实际上是保存在我当地的时区。

Because I know that any date time used is against a known timezone, rather than where the user may be submitting their request from, I take a LocalDateTime, convert to UTC and persist. Then, when the appointment is retrieved I convert the saved time to the meeting location timezone (stored in db). However, it would seem that the values I save are actually being saved in my local timezone.

我在Rest Controller中收到日期时间值，例如：

I receive a date time value in the Rest Controller such as:

startLocalDateTime: 2016-04-11T10:00
endLocalDateTime: 2016-04-11T10:30

约会有两个ZoneDateTime字段：

Appointment has two ZoneDateTime fields:

@Column(name = "startDateTime", columnDefinition= "TIMESTAMP WITH TIME ZONE")
private ZonedDateTime startDateTime;
@Column(name = "endDateTime", columnDefinition= "TIMESTAMP WITH TIME ZONE")
private ZonedDateTime endDateTime;

然后我将值更改为UTC并存储在我的实体上以存储到Postgres：

Then I change the values to UTC and store on my entity to store to Postgres:

appointment.setStartDateTime(startLocalDateTime.atZone(ZoneId.of( "UTC" )))
appointment.setEndDateTime(endLocalDateTime.atZone(ZoneId.of( "UTC" )))

我将其存储在Postgres （columnDefinition =TIMESTAMP WITH TIME ZONE）当我查看pgadminIII中的记录时，我看到：

and I store that in Postgres (columnDefinition= "TIMESTAMP WITH TIME ZONE") When I look at the record in pgadminIII I see:

startDateTime "2016-04-11 04:00:00-06"
endDateTime  "2016-04-11 04:30:00-06"

所以这些似乎以UTC格式正确存储（如果到目前为止我做错了，请纠正我）。然后我从数据库中检索它们并将它们返回为：

So these appear to be stored properly in UTC format (please correct me if I am doing anything wrong so far). I then retrieve them from the database and they are returned as:

Appointment
startdatetime: 2016-04-11T04:00-06:00[America/Denver]
enddatetime: 2016-04-11T04:30-06:00[America/Denver]

这些值以JSON形式发回：

Those values are sent back as JSON:

{  
 "appointmentId":50,
 "startDateTime":"2016-04-11T04:00",
 "endDateTime":"2016-04-11T04:30"
}

所以，即使我将它们保存为UTC，当我检索它们时，它们仍处于MST（我的本地）时区，而不是UTC ，我无法将它们转换回实际时间。

So even though I am saving them as UTC, when I retrieve them they are in MST (my local) timezone, rather than UTC, and I am unable to convert them back to the actual time.

仍然在坚持不懈。我尝试在我的实体上使用java.sql.timestamp，java.sql.Date，java.util.Date和java.time.ZonedDateTime。我的Postgres仍然是带时区的时间戳。但是因为我使用Spring-Data-JPA并且需要使用相同的类型进行查询。如果我使用Date - 应该是sql.Date还是util.Date？

Still struggling with the persistence. I have tried using the java.sql.timestamp, java.sql.Date, java.util.Date, and java.time.ZonedDateTime on my entity. My Postgres is still a "timestamp with time zone". But because I am using Spring-Data-JPA and need to query with the same type. If I use Date - should that be sql.Date or util.Date?

推荐答案

jdbc驱动程序对你目前所处的时区有一些了解。一般来说，我在过去已经解决了这个问题。数据库为我做时区转换，一些衍生的没有时区AT TIME ZONE区域的时间戳或带时区的时间戳'UTC'。它是postgres jdbc驱动程序的核心，它正在计算JVM所处的时区并在保存中使用它。

The jdbc driver has some knowledge about the timezone you are currently in. Generally I have gotten around this in the past by having the database do the timezone conversion for me, some derivative of "timestamp without time zone AT TIME ZONE zone" or "timestamp with time zone at time zone 'UTC'". It is in the guts of the postgres jdbc driver that it is figuring out what timezone the JVM is at and is using it in the save.

这篇关于Postgres UTC到Java ZonedDateTime的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！