Postgres UTC for Java ZonedDateTime

Question

The next question is to my original problem .

Since I know that any used time is dated by a known time zone, and not where the user can send his request, I take LocalDateTime, convert to UTC and save. Then, when the appointment is received, I convert the saved time into the time zone of the meeting location (stored in db). However, it would seem that the values ​​I preserve are actually stored in my local time zone.

I get the date time value in the break controller, for example:

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

The destination 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 save on my entity to store Postgres:

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

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

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

That way they look correctly saved in UTC (please correct me if I don't do anything yet). Then I retrieve them from the database and they return as:

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

These values ​​are sent back as JSON:

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

So, although I save them as UTC, when I retrieve them, they are in the MST (my local) time zone, not in UTC, and I can’t get them back at the current time.

Still struggling with perseverance. I tried using java.sql.timestamp, java.sql.Date, java.util.Date and java.time.ZonedDateTime on my object. My Postgres is still a “time zone time stamp”. But because I use Spring -Data-JPA and must request the same type. If I use Date - should it be sql.Date or util.Date?

Answer 1

The jdbc driver has some knowledge of the time zone you are currently in. As a rule, I talked about this in the past, having in my database a temporary conversion, some derivative of the “timestamp without time zone of the AT TIME ZONE zone”, or the “time stamp with the time zone in the time zone of“ UTC ”. guts postgres jdbc driver finds out what time zone the JVM is in and whether it uses it in saving.