Postgres UTC to Java ZonedDateTime

Question

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?

Answer 1

You said:

I take a LocalDateTime, convert to UTC and persist.

Nope. Stick with that LocalDateTime for booking appointments, no UTC involved for appointments.

When booking appointments that should appear as a certain time-of-day regardless of how politicians redefine the offset used within the time zone(s) under their jurisdiction, store a date with time-of-day but keep the time zone separate.

Politicians around the world have shown a strange penchant for frequently redefining the offset of their zones, adjusting to match or differ their neighbors, or to adopt or drop the foolishness known as Daylight Saving Time (DST). These changes are done with little forewarning, or even none at all. So what is right now looks like 2:30 PM on a future day could become 1:30 PM, 2:00 PM, 3:30 PM, or who knows what the politicians might come up with, if we stored as a moment (date, time-of-day, zone/offset, all together).

Java

To represent a date and time-of-day only, use LocalDateTime. This class purposely lacks any concept of time zone or offset-from-UTC. This lack means this class does not track moments, is not a point on the timeline. So we generally do not use this class in business apps. Booking future appointments is one of the few cases where we do want this class.

LocalDate ld = LocalDate.of( 2020 , Month.JANUARY , 23 ) ;    // 2020-01-23. 
LocalTime lt = LocalTime.of( 14 , 30 ) ;                      // 2:30 PM.
LocalDateTime ldt = LocalDateTime.of( ld , lt ) ;

See this code run live at IdeOne.com.

ldt.toString(): 2020-01-23T14:30

Store that in your database using JDBC 4.2 and later.

myPreparedStatement.setObject( … , ldt ) ;

Retrieve from database.

LocalDateTime ldt = myResultSet.getObject( … , LocalDateTime.class ); 

We need to track the intended time zone. Is this an appointment for a dentist office in Québec? Represent that fact as well. Keep in mind that a time zone is a history of past, present, and future (planned) changes to the offset-from-UTC used by the people of a particular region.

Use the ZoneId class to represent a time zone.

ZoneId z = ZoneId.of( "America/Montreal" ) ;

Save that zone name as text to your database. Get its identifying name by calling ZoneId::toString. Do not use ZoneId::getDisplayName as that is for generating localized text for display to a user, but is not a formal identifier for the zone.

myPreparedStatement.setString( … , z.toString() ) ;

When your retrieve from database, reconstitute the ZoneId.

String zoneIdString = myResultSet.getString( … ) ;
ZoneId z = ZoneId.of( zoneIdString ) ;

When you are generating a calendar for these appointments, if you need to determine (tentatively) points on the timeline, combine the date-with-time-of-day and the time zone together. Applying the ZoneId to the LocalDateTime produces a ZonedDateTime. This ZonedDateTime is a moment, is a point on the timeline, unlike LocalDateTime.

 ZonedDateTime zdt = ldt.atZone( z ) ;

See this code run live at IdeOne.com.

zdt.toString(): 2020-01-23T14:30-05:00[America/Montreal]

Of course, we do not store that ZonedDateTime. As discussed above this moment that right now appears to be 2:30 PM on the 23rd would turn into 1:30 PM or 3:30 PM if those busy politicians changed the time zone rules between now and then. We only use this ZonedDateTime tentatively, temporarily.

Database

We store a LocalDateTime only in a column of type TIMESTAMP WITHOUT TIME ZONE. You were incorrectly using WITH rather than WITHOUT, a major problem. For the WITHOUT type, Postgres stores the date and the time-of-day as given in the input. Even if a zone or offset were included with the input, Postgres would ignore the zone or offset.

Be aware that TIMESTAMP WITH TIME ZONE is just the opposite in Postgres. The time zone or offset info accompanying an input is used to adjust the date and time to UTC. That UTC value is then stored. So values in a column of this type are all in UTC. Unfortunately, many tools have the anti-feature of dynamically applying a default time zone to the retrieved value before passing on to you. This creates the false illusion of a time zone being stored when in fact the value is in UTC. Retrieving as a OffsetDateTime will always tell you the truth. I say OffsetDateTime because, oddly, the JDBC 4.2 spec requires support for that class but not for the more commonly used Instant & ZonedDateTime classes. Your JDBC driver may support the other two classes, optionally. But all of this paragraph is neither here nor there, as we are not using WITH columns for future appointments.

As mentioned above, for future appointments should be stored in three (3) separate columns:

• TIMESTAMP WITHOUT TIME ZONE for the date and the time-of-day.
• TEXT (or similar type) for the identifying name of the intended time zone by which we want to view that appointment.
• TEXT (or similar) for the duration of the appointment, in standard ISO 8601 format. (see below)

Points in your Question

You said:

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

startLocalDateTime: 2016-04-11T10:00

So parse as a LocalDateTime. Such ISO 8601 compliant strings can be parsed directly by the java.time classes.

LocalDateTime ldt = LocalDateTime.parse( "2016-04-11T10:00" ) ;

You said:

endLocalDateTime: 2016-04-11T10:30

No. Do not store the ending time. The ending time you expect now could be different if time zone anomalies occur at that point. For example, what if your politicians adopt Daylight Saving Time (DST), then during the time of this appointment the "Spring ahead" 1-hour change of DST occurred? Then your half-hour meeting should run from 10:00 AM to 11:30 AM, while your recorded end-time would incorrectly read "10:30 AM" ending.

Generally the best way to handle appointments is to record the duration of the appointment, the span-of-time, rather than on the clock. As mentioned above, an appointment consists of three separate pieces of data: (1) starting date with time-of-day, (2) the intended time zone, and (3) duration of appointment. The ending should be dynamically calculated as needed, using fresh current time zone data, for temporary use.

The ISO 8601 standard includes a textual format for recording such durations: PnYnMnDTnHnMnS where P marks the beginning, and T separates any years-months-days from any hours-minutes-seconds. The java.time classes Period and Duration can parse such strings.

Duration d = Duration.ofHours( 1 ) ;
String output = d.toString() ;

See this code run live at IdeOne.com.

output: PT1H

You can dynamically apply this duration to the LocalDateTime.

LocalDateTime ending = ldt.plus( d ) ;

Ditto for ZonedDateTime.

ZonedDateTime ending = zdt.plus( d ) ;

You said:

Appointment has two ZoneDateTime fields:

Nope. Make that a LocalDateTime field, not ZonedDateTime.

You said:

@Column(name = "startDateTime", columnDefinition= "TIMESTAMP WITH TIME ZONE")

Nope. Make that a column of type "TIMESTAMP WITHOUT TIME ZONE" to track a date and time-of-day without the context of zone/offset.

You said:

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

Nope. Booking appointments is one of the few cases where we do not want UTC. When tracking a moment, we generally do want to use UTC. But future appointments are not moments. We do not know the moment until we apply a time zone to the date-with-time-of-day, and we cannot do that ahead of time because we cannot trust politicians.

You said:

Still struggling with the persistence.

Cannot help you there. I do not use either Spring or JPA, as they solve problems I do not have. I use straight JDBC.

You said:

So these appear to be stored properly in UTC format (please correct me if I am doing anything wrong so far).

Nope, do not use UTC, do not store in UTC, not for appointments.

Do use UTC for moments though. For example, your logs should all be reporting each moment of an incident in UTC. Programmers & sysadmins would do well to learn to think in UTC while on the job. Keep a second clock on your desk set to UTC.

You said:

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.

We are not using UTC, nor any other time zone or offset-from-UTC in our appointment booking. So your problem vaporizes.

Tip: As I said above, your tools are likely lying to you about time zones. Set the tool and your session to UTC, if need be, to defeat this anti-feature.

You said:

I have tried using the java.sql.timestamp, java.sql.Date, java.util.Date, and java.time.ZonedDateTime on my entity.

• Never use java.sql.Timestamp. Replaced by OffsetDateTime (and Instant).
• Never use java.sql.Date. Replaced by LocalDate.
• Never use java.util.Date. Replaced by Instant.

Use only the modern java.time classes that years ago supplanted those terrible date-time classes bundled with the earliest versions of Java. Those awful classes became legacy as of the adoption of JSR 310.

Answer 2

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.