Storing appointments in a SQL database such as Postgres for use with java.time framework

Let's say that we have an appointment in Milan Italy happening on 01/23/2021 21:00 "Europe/Rome". This appointment is saved to the database in UTC in a column of a type akin to the SQL-standard type TIMESTAMP WITH TIME ZONE.

Now a user living in New York US needs to understand when this appointment will take place. We can show that user the date-time converted to "America/New_York" time zone, or instead, in "Europe/Rome" TZ. Once the user will fly from New York to Milan, he will find both info useful.

The point is to store everything converted into the same TZ reference (UTC), and the manipulate the date-time depending on the goal you have using the java.time framework bundled with modern Java.

Makes sense or there is something wrong/missing?

>Makes sense or there is something wrong/missing?

It depends on the kind of appointment.

There are two kinds of appointments:

• A moment, a specific point on the timeline, ignoring any changes to time zone rules.<br/>Example: Rocket launch.
• A date and time-of-day that should adjust for changes to time zone rules.<br/>Example: Medical/Dental visit.

Moment

If we are booking the launch of a rocket, for example, we do not care about the date and the time-of-day. We only care about the moment when (a) the heavens align, and (b) we expect favorable weather.

If in the intervening time the politicians change the rules of the time zone in use at our launch site or at our offices, that has no effect on our launch appointment. If politicians governing our launch site adopt Daylight Saving Time (DST), the moment of our launch remains the same. If the politicians governing our offices decide to change the clock a half-hour earlier because of diplomatic relations with a neighboring country, the moment of our launch remains the same.

For such an appointment, yes, your approach would be correct. You would record the appointment in UTC using a column of type TIMESTAMP WITH TIME ZONE. Upon retrieval, adjust into any time zone the user prefers.

Databases such as Postgres use any time zone info accompanying an input to adjust into UTC, and then disposes of that time zone info. When you retrieve the value from Postgres, it will always represent a date with time-of-day as seen in UTC. Beware, some tooling or middleware may have the anti-feature of applying some default time zone between retrieval from database and delivery to you the programmer. But be clear: Postgres always saves and retrieves values of type TIMESTAMP WITH TIME ZONE in UTC, always UTC, and offset-from-UTC of zero hours-minutes-seconds.

Here is some example Java code.

LocalDate launchDateAsSeenInRome = LocalDate.of( 2021 , 1 , 23 ) ;
LocalTime launchTimeAsSeenInRome = LocalTime.of( 21 , 0 ) ;
ZoneId zoneEuropeRome = ZoneId.of( "Europe/Rome" ) ;
// Assemble those three parts to determine a moment.
ZonedDateTime launchMomentAsSeenInRome = ZonedDateTime.of( launchDateAsSeenInRome , launchTimeAsSeenInRome , zoneEuropeRome ) ;

>launchMomentAsSeenInRome.toString(): 2021-01-23T21:00+01:00[Europe/Rome]

To see the same moment in UTC, convert to an Instant. An Instant object always represents a moment as seen in UTC.

Instant launchInstant = launchMomentAsSeenInRome.toInstant() ;  // Adjust from Rome time zone to UTC.

> launchInstant.toString(): 2021-01-23T20:00:00Z

The Z on the end of the above string example is standard notation for UTC, and is pronounced “Zulu”.

Unfortunately the JDBC 4.2 team neglected to require support for either Instant or ZonedDateTime types. So your JDBC driver may or may not be able to read/write such objects to your database. If not, simply convert to OffsetDateTime. All three types represent a moment, a specific point on the timeline. But OffsetDateTime has support required by JDBC 4.2 for reasons that escape me.

OffsetDateTime odtLaunchAsSeenInRome = launchMomentAsSeenInRome.toOffsetDateTime() ;

Writing to database.

myPreparedStatement.setObject( … , odtLaunchAsSeenInRome ) ;

Retrieval from database.

OffsetDateTime launchMoment = myResultSet.getObject( … , OffsetDateTime.class ) ;

Adjust to the New York time zone desired by your user.

ZoneId zoneAmericaNewYork = ZoneId.of( "America/New_York" ) ;
ZonedDateTime launchAsSeenInNewYork = launchMoment.atZoneSameInstant( zoneAmericaNewYork ) ;

> launchAsSeenInNewYork.toString(): 2021-01-23T15:00-05:00[America/New_York]

You can see all of the above code run live at IdeOne.com.

By the way, tracking past events are also treated as a moment. When did the patient actually arrive for appointment, when did the customer pay the invoice, when did a new hire sign their documents, when did the server crash… all these are tracked as a moment in UTC. As discussed above, usually that would be an Instant, though ZonedDateTime & OffsetDateTime also represent a moment. For the database use TIMESTAMP WITH TIME ZONE (not WITHOUT).

Time-of-day

I expect most business-oriented apps are focused on appointments of the other type, where we aim at a date with a time-of-day rather than a specific moment.

If the user makes an appointment with their health care provider to review the results of a test, they do so for a particular time-of-day on that date. If in the meantime the politicians change the rules of their time zone, moving the clock ahead or behind an hour or half-hour or any other amount of time, the date and time-of-day of that medical appointment remain the same. In actuality, the point of the timeline of the original appointment will be changed after the politicians change the time zone, shifting to an earlier/later point on the timeline.

For such appointmentss, we do not store the date and time-of-day as seen in UTC. We do not use the database column type TIMESTAMP WITH TIME ZONE.

For such appointments, we store the date with time-of-day without any regard to time zone. We use a database column of type TIMESTAMP WITHOUT TIME ZONE (notice WITHOUT rather than WITH). The matching type in Java is LocalDateTime.

LocalDate medicalApptDate = LocalDate.of( 2021 , 1 , 23 ) ;
LocalTime medicalApptTime = LocalTime.of( 21 , 0 ) ;
LocalDateTime medicalApptDateTime = LocalDateTime.of( medicalApptDate , medicalApptTime ) ;

Write that to the database.

myPreparedStatement.setObject( … , medicalApptDateTime ) ;

Be clear on this: a LocalDateTime object does not represent a moment, is not a specific point on the timeline. A LocalDateTime object represents a range of possible moments along about 26-27 hours of the timeline (the range of time zones around the globe). To give real meaning to a LocalDateTime, we must associate an intended time zone.

For that intended time zone, use a second column to store the zone identifier. For example, the strings Europe/Rome or America/New_York. See list of zone names.

ZoneId zoneEuropeRome = ZoneId.of( "Europe/Rome" ) ;

Write that to the database as text.

myPreparedStatement.setString( … , zoneEuropeRome ) ;

Retrieval. Retrieve the zone name as text, and instantiate a ZoneId object.

LocalDateTime medicalApptDateTime = myResultSet.getObject( … , LocalDateTime.class ) ;
ZoneId medicalApptZone = ZoneId.of( myResultSet.getString( … ) ) ;

Put those two pieces together to determine a moment represented as a ZonedDateTime object. Do this dynamically when you need to schedule a calendar. But do not store the moment. If the politicians redefine the time zone(s) in the future, a different moment must be calculated then.

ZonedDateTime medicalApptAsSeenInCareProviderZone = ZonedDateTime.of( medicalApptDateTime , medicalApptZone ) ;

The user is traveling to New York US. They need to know when to call the health care provider in Milan Italy according to the clocks on the wall in their temporary location of New York. So adjust from one time zone to another. Same moment, different wall-clock time.

ZoneId zoneAmericaNewYork = ZoneId.of( "America/New_York" ) ;
ZonedDateTime medicalApptAsSeenInNewYork = medicalApptAsSeenInCareProviderZone.withZoneSameInstant( zoneAmericaNewYork ) ;

tzdata

Be aware that if the rules of your desired time zones may be changing, you must update the copy of time zone definitions on your computers.

Java contains its own copy of the tzdata, as does the Postgres database engine. And your host operating system as well. This particular code shown here requires only Java to be up-to-date. If you use Postgres to make time zone adjustments, its tzdata must also be up-to-date. And for logging and such, your host OS should be kept up-to-date. For proper clock-watching by the user, their client machine's OS must also be up-to-date.

Beware: Politicians around the world have shown a penchant for changing their time zones with surprising frequency, and often with little forewarning.

About java.time

The java.time framework is built into Java 8 and later. These classes supplant the troublesome old legacy date-time classes such as java.util.Date, Calendar, & SimpleDateFormat.

To learn more, see the Oracle Tutorial. And search Stack Overflow for many examples and explanations. Specification is JSR 310.

The Joda-Time project, now in maintenance mode, advises migration to the java.time classes.

You may exchange java.time objects directly with your database. Use a JDBC driver compliant with JDBC 4.2 or later. No need for strings, no need for java.sql.* classes. Hibernate 5 & JPA 2.2 support java.time.

Where to obtain the java.time classes?

• Java SE 8, Java SE 9, Java SE 10, Java SE 11, and later - Part of the standard Java API with a bundled implementation.
  • Java 9 brought some minor features and fixes.
• Java SE 6 and Java SE 7
  • Most of the java.time functionality is back-ported to Java 6 & 7 in ThreeTen-Backport.
• Android
  • Later versions of Android (26+) bundle implementations of the java.time classes.
  • For earlier Android (<26), a process known as API desugaring brings a subset of the java.time functionality not originally built into Android.
    • If the desugaring does not offer what you need, the ThreeTenABP project adapts ThreeTen-Backport (mentioned above) to Android. See How to use ThreeTenABP….

A very simple solution would be to leave the conversion to PostgreSQL. If you set the timezone parameter correctly for each session and use timestamp with time zone PostgreSQL will automatically show the timestamp to the New York user in New York time.