I'm saving appointments to a database and have a method that checks if times are overlapping.
public static boolean checkForOverlappingAppointment(Timestamp start, Timestamp end, int
customerID) {
try {
String sql = "SELECT * FROM appointments WHERE Customer_ID = ?";
PreparedStatement ps = JDBC.getConnection().prepareStatement(sql);
ps.setString(1, String.valueOf(customerID));
ResultSet rs = ps.executeQuery();
while (rs.next()) {
Timestamp startOtherAppts = rs.getTimestamp("Start");
Timestamp endOtherAppts = rs.getTimestamp("End");
if ((start.before(startOtherAppts) && end.after(endOtherAppts)) ||
(start.before(startOtherAppts) && end.after(startOtherAppts) &&
end.before(endOtherAppts)) || (start.after(startOtherAppts) &&
start.before(endOtherAppts) && end.after(endOtherAppts)) ||
(start.after(startOtherAppts) && end.before(endOtherAppts)))
{
return true;
}
}
}
catch (SQLException e) {
e.printStackTrace(); }
return false;
}
I'm calling the method using
if (DBAppointments.checkForOverlappingAppointment(Timestamp.valueOf(startDateTimeZDT.toLocalDateTime()), Timestamp.valueOf(endDateTimeZDT.toLocalDateTime()), customerIDint)) {
Alert alert3 = new Alert(Alert.AlertType.ERROR);
alert3.setHeaderText("APPOINTMENT OVERLAP");
alert3.setContentText("Appointment overlaps with an existing appointment.");
alert3.showAndWait();}
I don't receive any errors when saving appointments although they can be for the same exact times.
I'm converting a LocalTime to a ZonedDateTime to save in the DB by using Timestamp.valueOf(startDateTimeZDT.toLocalDateTime() Timestamp.valueOf(endDateTimeZDT.toLocalDateTime()
I can't seem to find out where it is going wrong at. Any help would be great!
CodePudding user response：
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/Regionsuch asAmerica/New_YorkorAfrica/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 bePT1H30M.
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 ZONEvalue, apply the row's time zone, to get a moment, aTIMESTAMP WITH TIME ZONEvalue. - For the end of the appointment, we take that generated
TIMESTAMP WITH TIME ZONEvalue 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.
CodePudding user response：
Your database may do that work for you.
public static boolean checkForOverlappingAppointment(
LocalDateTime start, LocalDateTime end, int customerID) {
try {
String sql = "SELECT *"
" FROM appointments"
" WHERE Customer_ID = ? and Start < ? and end > ?";
PreparedStatement ps = JDBC.getConnection().prepareStatement(sql);
ps.setInt(1, customerID);
ps.setObject(2, end);
ps.setObject(3, start);
ResultSet rs = ps.executeQuery();
// If there is a result row, we know it’s an overlapping appointment
return rs.next();
}
catch (SQLException e) {
e.printStackTrace();
}
return false;
}
java.time: I recommend that you use java.time for your dates and times. Assuming that your start and end columns have type datetime or timestamp (without time zone), LocalDateTime is the correct corresponding type to use in Java. Since JDBC 4.2 you can directly pass LocalDateTime and other java.time classes to your prepared statement using its setObject method.
Checking for overlap: Your logic was overly complicated for this check. The argument may be a bit tricky, but as you can see, my code is simple. For each appointment already in the database there are three possibilities: either it’s earlier, it’s overlapping or it’s later. If its start is before the end of this appointment we know it’s not later. If its end is after the start of this appointment, it cannot be earlier. If both conditions are fulfilled, it must be overlapping.
Was there an error in your logic? First of all I found your if condition unreadable. My code formatter formatted it in this way, which helps quite a bit:
if ((start.before(startOtherAppts) && end.after(endOtherAppts))
|| (start.before(startOtherAppts)
&& end.after(startOtherAppts)
&& end.before(endOtherAppts))
|| (start.after(startOtherAppts)
&& start.before(endOtherAppts)
&& end.after(endOtherAppts))
|| (start.after(startOtherAppts) && end.before(endOtherAppts))) {
return true;
}
I think you are not taking the cases into account where either both appointments have the exact same start time or they both have the same end time. You are always only checking whether one time is strictly before or strictly after the other.
Error handling: I don’t think you will want just to return false in case of an SQL error.
Tutorial link: Oracle Tutorial: Date Time