Looking for help creating 15 min appointments, Currently hard coded for 1 hour

Question

First time post - I'm newer to java/JavaFx

I'm trying to get my code to save in 15 min incrementalist. The project has an Observable List that houses selectable appointment times.

ApptAddController.java

private final ObservableList<String> times = FXCollections.observableArrayList("8:00 AM", "9:00 AM", "10:00 AM", "11:00 AM", "12:00 PM", "1:00 PM", "2:00 PM", "3:00 PM", "4:00 PM");

ApptDB.Java

The "times" selected goes to the "saveAppt" method

public static boolean saveAppt(int id, String type, String contact, String location, String date, String time) {
        //Time stamp for booking times
        String tsStart = createTimeStamp(date, time, location, true);
        String tsEnd = createTimeStamp(date, time, location, false);
        try {
            //get date for Appointment createDate
            DateTimeFormatter dt = DateTimeFormatter.ofPattern("YYYY-MM-dd HH:mm:ss");
            String nowString = LocalDateTime.now(Clock.systemUTC()).format(dt);
            Statement statement = DBConnection.getConnection().createStatement();
            //Query
            String queryInsertOne = "INSERT INTO appointment(customerId, type, contact, location, start, end,  createDate, lastUpdateBy, createdBy) values ('" + id + "', '" + type + "', '" + contact + "', '" + location + "','" + tsStart + "','" + tsEnd + "','" + nowString + "','" + UserDB.getCurrentUser() + "','" + UserDB.getCurrentUser() + "')";
            int updateOne = statement.executeUpdate(queryInsertOne);         
            return true;
       } catch (SQLException e) {
            System.out.println("SQLException: " + e.getMessage());
        }
        return false;
    }

The createTimeStamp method is hard coded in the following method with the "00":

public static String createTimeStamp(String date, String time, String location, boolean startMode) {
        String t = time.split(":")[0];
        int baseH = Integer.parseInt(t);
        if(baseH < 8) {
            baseH += 12;
        }
        if(!startMode) {
            baseH += 1;
        }
        String baseD = String.format("%s %02d:%s", date, baseH, "00");
        DateTimeFormatter dtf = DateTimeFormatter.ofPattern("yyyy-MM-dd kk:mm");
        LocalDateTime ldt = LocalDateTime.parse(baseD, dtf);
        ZoneId zid;
        zid = TimeZone.getDefault().toZoneId();
           ZonedDateTime zdt = ldt.atZone(zid);
        ZonedDateTime utcDate = zdt.withZoneSameInstant(ZoneId.of("UTC"));
        ldt = utcDate.toLocalDateTime();
        Timestamp ts = Timestamp.valueOf(ldt); 
        return ts.toString();
    }

Id like to have the appointments be 15 min incrementals. The user will select times at random (8:00, 8:15, 8:30, 8:45). How do I get this code to detect what the user selects and puts int in the database accordingly. If I change "00" to "15" it will hard code every appt for 15 after.

Thanks for your time.

Answer 1

I am not able to follow your code well. So here's some general advice. I should say up front, managing appointments and schedules is a surprisingly complicated problem domain.

You seem to be focused on strings rather than appropriate data types, a common issue with new programmers. Learn to use smart objects, not dumb strings. Notice that none of the code shown below involves strings, except for presentation to users. Values are exchanged with the database as objects, without the use of strings.

Java offers an industry-leading assortment of date-time classes, in the java.time packages. So use them. LocalTime represents a time-of-day. LocalDate represents a date-only, without time-of-day, and without time zone.

For data-entry on the time of appointment, you should be collecting an hour and a minute. Work internally with 24-hour clock LocalTime objects.

List< LocalTime > hourTimes = 
    List.of( 
        LocalTime.of( 8 , 0 ) ,
        LocalTime.of( 9 , 0 ) ,
        LocalTime.of( 10 , 0 ) ,
        LocalTime.of( 11 , 0 ) ,
        LocalTime.of( 12 , 0 ) ,
        LocalTime.of( 13 , 0 ) ,
        LocalTime.of( 14 , 0 ) ,
        LocalTime.of( 15 , 0 ) ,
        LocalTime.of( 16 , 0 ) 
    )
;

If your audience expects a 12-hour clock, present their display with a custom formatter. If your audience expects a 12-hour clock, present their display with a custom formatter.

Locale locale = Locale.US ;
DateTimeFormatter formatterHourOfDay = DateTimeFormatter.ofPattern ( "h a" ).withLocale ( locale ) ;
String output = hourTimes.get( 7 ).format( formatterHourOfDay ) ;

See similar code run live at IdeOne.com.

3 PM

Note that java.time uses immutable objects. So you can freely use the LocalTime object directly from the master list with no need to copy, no need to worry about its values being changed out from under your feet. This also means you can use java.time objects across threads, as they are designed to be thread-safe.

Keep a list of the possible minutes.

List< Integer > minutes = List.of( 0 , 15 , 30 , 45 ) ;

In you user-interface, let the use pick one of those four values, to be mated with their choice from hourTimes above.

Put these values together for your time-of-day to make a new LocalTime.

LocalTime localTime = hourTimes.get( 7 ).plusMinutes( minutes.get( 2 ) ) ;  // Adding 0, 15, 30, or 45 minutes to the on-the-hour `LocalTime` object, resulting in another `LocalTime` object. 

Combine the time-of-day with your intended date to get a LocalDateTime object.

LocalDate localDate = LocalDate.of( 2020 , Month.JANUARY , 23 ) ;
LocalDateTime localDateTime = LocalDateTime.of( localDate , localTime ) ;

Store that in a database column of SQL-standard type TIMESTAMP WITHOUT TIME ZONE. (that was "WITHOUT", not "WITH")

Generally best to use prepared statements in SQL rather than string-combining.

myPreparedStatement.setObject( … , localDateTime ) ;

In addition, record the intended time zone for that appointment. There is a data type in Java for this, ZoneId. But not in SQL. So record the zone name as text in your database.

ZoneId z = ZoneId.of( "America/Montreal" ) ;
…
myPreparedStatement.setObject( … , z.toString() ) ;  // Record name of zone as text.

By the way, no need to pass when the record was created or last updated. You should be configuring your database to set those value automatically on the server-side.

Separate your business logic from your user-interface. Notice that none of my code above relates to JavaFX.

Define a class just for the appointment. This class should know only the needs of an appointment, the business rules for what defines a valid appointment. This class should know nothing about the database, nor the user-interface.

I do not understand what you are trying to do with time zones in your use of ZonedDateTime. It seems you are trying to form a java.sql.Timestamp object. That is the wrong way to go for two reasons. First, that class is one of the terrible legacy date-time classes supplanted by java.time; never use it. Second, that is the wrong way to book appointments. Future appointments should be recorded as two separate parts, (a) date-with-time-of-day and (b) time zone.

Politicians around the world have shown a penchant for frequently changing the offset of the time zones under their jurisdiction. They do so with little, or even no, forewarning. So if you booked 3:45 PM for next January as a moment, as a specific point on the timeline, the time-of-day for that zone's wall-clock time might be changed by then, with 3:45 PM becoming 3:15 PM or 4:45 PM. Imagine if your software booked an appointment for the specific moment that was 3:45 PM before the zone changed its offset-from-UTC. The customers keeping their appointments will be appearing at 3 PM on the current wall-clock time while your appointment book software will show them as early (or late). To avoid this problem, do not schedule appointments as a moment, keep the date-with-time-of-day (LocalDateTime) separated from the time zone (ZoneId).

Combine the two (date-with-time-of-day & time zone) only when building out a schedule where you need specific moments.

ZoneId z = ZoneId.of( zoneNameFromDatabase ) ;
ZonedDateTime zdt = localDateTimeFromDatabase.atZone( z ) ;

If you need to see that moment in UTC, adjust by extracting a Instant object.

Instant instant = zdt.toInstant() ;

But do not store either the ZonedDateTime or Instant for the future. Store them only after the fact, when recording history. And even then, you record OffsetDateTime, as oddly the JDBC spec does not require support for ZonedDateTime or Instant. You would store that OffsetDateTime object in a column of SQL-standard type TIMESTAMP WITH TIME ZONE ("WITH", not "WITHOUT" as seen above).

OffsetDateTime odt = zdt.toOffsetDateTime() ;
myPreparedStatement.setObject( … , odt ) ;  // Record history as a moment, a specific point on the timeline.