Postgres UTC to Java ZonedDateTime

问题:

Follow-up question to my original issue.

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.

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

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

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;

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" )))

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"

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]

Those values are sent back as JSON:

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

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.

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?

回答1:

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.