1

I'm working on an appointment scheduler for a client and I'm getting very frustrated with an error I'm encountering regarding constantly having to deal with reformatting dates between utc, strings (containing start times, ex 8:00AM to a local time, then to localdatetime etc).

I've managed to isolate it to a single method call but I cannot find how in the world my sql update is being ruined.

My table entry before the call to 'update'

 appointmentId, customerId, title, description, location, contact, url, start, end, createDate, createdBy, lastUpdate, lastUpdateBy
'21', '3', 'test', 'Consultation', '1', '1', '1', '2018-11-30 18:00:00', '2018-11-30 19:00:00', '2018-11-30 16:17:39', 'test', '2018-11-30 16:17:39', 'test'

As you can see the above has two valid timestamps at 18:00:00 and at 19:00:00, so the appointment will be at those times in UTC

private boolean updateForm(){
    LocalDate localDate = datePicker.getValue();
    LocalTime startTime =  LocalTime.parse(startTimeField.getSelectionModel().getSelectedItem(), timeDTF);
    LocalTime endTime = LocalTime.parse(endTimeField.getSelectionModel().getSelectedItem(), timeDTF);

    LocalDateTime startDT = LocalDateTime.of(localDate, startTime);
    LocalDateTime endDT = LocalDateTime.of(localDate, endTime);

    ZonedDateTime startUTC = startDT.atZone(ZoneId.systemDefault()).withZoneSameInstant(ZoneId.of("UTC"));
    ZonedDateTime endUTC = endDT.atZone(ZoneId.systemDefault()).withZoneSameInstant(ZoneId.of("UTC"));            

Timestamp startsqlts = Timestamp.valueOf(startUTC.toLocalDateTime()); //this value can be inserted into database
    Timestamp endsqlts = Timestamp.valueOf(endUTC.toLocalDateTime()); //this value can be inserted into database        


    System.out.println("Timestamp value start: " + startsqlts.toString() + " end: " + endsqlts.toString());

//OUTPUT OF ABOVE
//"Timestamp value start: 2018-11-30 18:00:00.0 end: 2018-11-30 19:00:00.0"
    try{
            PreparedStatement st = sqlConnection.prepareStatement("UPDATE appointment SET customerId = ?, title = ?, description = ?, start = ?, end = ?, lastUpdate = CURRENT_TIMESTAMP, lastUpdateBy = ? WHERE appointmentId = ?");

            st.setString(1, Integer.toString(customerTable.getSelectionModel().getSelectedItem().getId().getValue().intValue()));
            st.setString(2, titleField.getText());
            st.setString(3, typeField.getValue());
            //Inserting timestamps directly
            st.setTimestamp(4, startsqlts);
            st.setTimestamp(5, endsqlts);
            st.setString(6, mainScheduler.currentUser);
            st.setString(7, Integer.toString(appointment.getId().getValue().intValue()));

            if(st.executeUpdate() > 0){
                System.out.println("Updated appointment");

                return true;
            }else{
                System.out.println("Failed to update appointment");
            }

    }catch(Exception e){
        e.printStackTrace();
    }
    return false;
}

As you can see above from the output I put below my print the timestamps are valid, yet after this call exits and I look at my table I have this: (Nothing else modifies my table in between this call and the time I check):

appointmentId, customerId, title, description, location, contact, url, start, end, createDate, createdBy, lastUpdate, lastUpdateBy
'21', '3', 'test', 'Consultation', '1', '1', '1', '2018-12-01 02:00:00', '2018-12-01 03:00:00', '2018-11-30 16:17:39', 'test', '2018-11-30 16:29:00', 'test'

Which shows that the upload is adding 8 hours to an already formatted timestamp.

How is this remedied? It's been the sole problem behind my application not working for almost a week now and I just isolated it so any help is greatly appreciated.

For a reference I am 8 hours behind UTC, so my local time + 8 hours = UTC.

EDIT: If it's not apparent I'm calling update with no changes so that it's easier to see the change reflected.

Joe
  • 269
  • 3
  • 13
  • What is the default timezone of your JVM? Your problem is likely one discussed in [Is java.sql.Timestamp timezone specific?](https://stackoverflow.com/questions/14070572/is-java-sql-timestamp-timezone-specific). Consider using `java.time.LocalDateTime` **without** using `java.sql.Timestamp`. That should be supported with `get/setObject` on JDBC 4.2 compliant drivers. – Mark Rotteveel Nov 30 '18 at 16:48
  • My default would be PST. Everyone always suggests me to use the opposite of what I'm using at the time and I really would rather not switch again. – Joe Nov 30 '18 at 16:54
  • I do appreciate the help though, I'm quite done with dealing with the whole thing so if worst comes to worst I'll be uploading it as a string instead of a timestamp object since that seems to work just fine. – Joe Nov 30 '18 at 16:59
  • Are the "start" and "end" columns of the type `TIMESTAMP` in your MySQL table? – Gord Thompson Dec 03 '18 at 15:42
  • 1
    @MarkRotteveel re: "Consider using java.time.LocalDateTime without using java.sql.Timestamp." - That was my thought as well. However, I found that [MySQL Connector/J still has problems dealing with LocalDateTime query parameters](https://bugs.mysql.com/bug.php?id=93444). The saga continues .... – Gord Thompson Dec 23 '18 at 17:01

0 Answers0