Tricky business
Handling appointments is actually complicated business.
I will assume your appointments are like dentist or car mechanic where you intend to keep the time-of-day the same, adjusting the moment as needed to abide by politically-created anomalies such as Daylight Saving Time (DST). This would be in contrast to events that are driven by natural time, such as a rocket launch.
To store such appointments, you need three columns in your database:
- Intended time zone. If your database lacks a data type for this, use a textual column to store real time zone names in format of
Continent/Region such as America/New_York or Africa/Casablanca.
TIMESTAMP WITHOUT TIME ZONE, to store the date and time-of-day of the appointment but without the context of a time zone or offset-from-UTC.- A duration, for how long the appointment will last. If your database lacks a data type for this, store text in standard ISO 8601 durations format:
PnYnMnDTnHnMnS. For example, one and a half hours would be PT1H30M.
Time zones change
We cannot store a moment, a specific point on the timeline, for appointments because we never know when politicians will change the offset used for the time zone(s) in their jurisdiction. Politicians around the world have shown a predilection for changing their time zone rules. They do so surprisingly often, and with little or even no warning. So we programmers must always assume future changes to our time zone rules.
When determining a schedule, such as your desired query, we must dynamically determine a moment for each appointment. We must craft the query to convert each appointment row’s stored values into a a dynamically calculated moment.
- For the start of the appointment, we take the
TIMESTAMP WITHOUT TIME ZONE value, apply the row's time zone, to get a moment, a TIMESTAMP WITH TIME ZONE value.
- For the end of the appointment, we take that generated
TIMESTAMP WITH TIME ZONE value and add to it the row’s duration value.
SQL
You may be able to craft such a query if your database offers robust date-time handling facilities.
Java
If your database lacks robust date-time handling facilities, then bring the data into Java to be processed and filtered.
Define a class to represent each row. Here we use a record.
record Appointment ( UUID uuid , ZoneId zoneId , LocalDateTime start , Duration duration ) {}
Retrieve the data, and collect.
List< Appointment > appointments = new ArrayList<>() ;
…
UUID uuid = myResultSet.getObject( … , UUID.class ) ;
ZoneID zoneId = ZoneId.of( myResultSet.getString( … ) ) ;
LocalDateTime start = myResultSet.getObject( … , LocalDateTime.class ) ;
Duration duration = Duration.parse( myResultSet.getString( … ) ) ;
appointments.add( new Appointment( uuid , zoneId , start , duration ) ) ;
Define the target start and end of your search. Generally best to use Half-Open approach to defining a span-of-time. In Half-Open, the beginning is inclusive while the ending is exclusive. This provides for neatly abutting spans, and other benefits.
ZonedDateTime zdtStart = ZonedDateTime.of( 2022 , Month.JANUARY , 23 , 15 , 30 , 0 , 0 , ZoneId.of( "America/Los_Angeles" ) ) ;
ZonedDateTime zdtEnd = zdtStart.plusHours( 2 ) ;
Adjust those to UTC (an offset of zero hour-minutes-seconds) by extracting an Instant.
Instant targetStart = zdtStart.toInstant() ;
Instant targetEnd = zdtEnd.toInstant() ;
You could code the logic yourself for asking if an appointment overlaps the target span-of-time. But I find it easier to use the Interval class found in the ThreeTen-Extra library.
Interval target = Interval.of( targetStart , targetEnd ) ;
Now we can loop our list of Appointment objects, to compare each against our target.
List< Appointment > hits = new ArrayList<>() ;
for( Appointment appointment : appointments )
{
ZonedDateTime start = appointment.start().atZone( appointment.zoneId() ) ;
ZonedDateTime stop = start.plus( appointment.duration() ) ;
Interval appointmentInterval = Interval.of( start , stop ) ;
if( appointmentInterval.overlaps( target ) )
{
hits.add( appointment ) ;
}
}
return List.copyOf( hits ) ; // Return an unmodifiable list, as a generally good habit.
Reminder: Your host operating system, database engine, and Java implementation likely each have their own copy of the time zone rules (tzdata, usually). Be sure to update all of them if the rules for any time zones you care about are being changed.
Avoid legacy date time classes
Never use the terrible date-time classes such as Timestamp, Date, Calendar, and SimpleDateFormat. These are frightfully flawed, designed by people who did not understand the subtleties and complexities of date-time handling.
These legacy classes were years ago supplanted by the modern java.time classes defined in JSR 310. JDBC 4.2 and later requires JDBC drivers to support the java.time types.
