java - 使用时间戳更新表尝试再次转换为 UTC？

Question

我正在为客户制定约会计划程序，我对遇到的一个错误感到非常沮丧，因为我经常不得不处理重新格式化 UTC、字符串之间的日期（包含开始时间，例如上午 8:00 到当地时间，然后到 localdatetime 等）。

我已经设法将它隔离为一个方法调用，但我无法找到我的 sql 更新是如何被破坏的。

我在调用“更新”之前的表条目

 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'

如您所见，上面有两个有效的时间戳，分别为 18:00:00 和 19:00:00，因此约会将在 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;
}

正如您从上面我在打印下方的输出中看到的那样，时间戳是有效的，但是在此调用退出并查看我的表后，我有这个：（在此调用和我检查的时间之间没有其他任何东西修改我的表）：

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.

标签： javamysqljdbc