How to read time from MySQL database appointment as UTC, convert to local system time and display it in TableView column in JavaFX?

Question

I have 2 database columns in MySQL database showing start and end times of the appointments. it is stored as '2019-01-01 00:00:00'. How to I take that time (assuming it is in UTC) and convert it to local system time (for example PC set to EST) and then display it inside start and end columns of the TableView?

I setup tableview columns like this:

        appTableViewStartColumn.setCellValueFactory(new PropertyValueFactory<Appointment, Calendar>("start"));
        appTableViewEndColumn.setCellValueFactory(new PropertyValueFactory<Appointment, Calendar>("end"));

And this is my code for method called getAllAppointments:

    public static ObservableList<Appointment> getAllAppointments() throws SQLException, Exception {
        DatabaseConnection.makeConnection();
        String sqlStatement = "SELECT appointmentId, customerId, type, start, end, customerName FROM appointment LEFT JOIN customer " +
                "USING (customerId)";
        Query.makeQuery(sqlStatement);
        ResultSet result = Query.getResult();

        while (result.next()) {
            int appointmentId = result.getInt("appointmentId");
            int customerIdIn = result.getInt("customerId");
            String type = result.getString("type");
            String customerName = result.getString("customerName");

            // Following gets date as string, then converts it to Calendar
            String startString = result.getString("start");
            String endString = result.getString("end");
            Calendar start = stringToCalendar(startString);
            Calendar end = stringToCalendar(endString);

            Appointment appointmentResult = new Appointment (appointmentId, customerIdIn, type, start, end, customerName);
            allAppointments.add(appointmentResult);
        }

This is the method that I used to convert string to Calendar object:

    public static Calendar stringToCalendar (String stringDate) throws ParseException {

        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
        Date date = sdf.parse(stringDate);
        Calendar calendar = Calendar.getInstance();
        calendar.setTime(date);
        return calendar;
    }

Well, I did search here on StackOverflow extensively. There are related questions that can be very similar but they do not fit in my situation. — Aman Khan, Feb 07 '20 at 15:24
Note that using `hh` you only get values form 1-12. This is probably not what you want. Use `HH` instead. — fabian, Feb 07 '20 at 17:01
better not add offending remarks to a question, doing so might lead to an account suspension.. — kleopatra, Feb 08 '20 at 07:48
I apologize! Thank you to everybody for help! Really appreciate it! — Aman Khan, Feb 08 '20 at 19:28

fabian · Accepted Answer · 2020-02-08T22:40:59.923

I assume you use the DATETIME column type in the db.

I recommend doing yourself a favor and going with the java.time API instead of using Calendar.

You can retrieve the time directly via ResultSet and apply the appropriate conversions to either LocalDateTime or ZonedDateTime. The former has the benefit of having a StringConverter implementation in the JavaFX API, but for configuring the display, both work.

Data Retrieval

final ZoneId est = ZoneId.of("America/New_York"); // ZoneId.systemDefault();
while (result.next()){
    ...
    Timestamp time = result.getTimestamp("start"); // utc is offset 0; no offset required
    ZonedDateTime zTime = time.toInstant().atZone(est);
    LocalDateTime start = zTime.toLocalDateTime();
    ...
}

Column Setup

TableColumn<Appointment, LocalDateTime> appTableViewStartColumn = ...

...

final DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");
appTableViewStartColumn.setCellFactory(col -> new TableCell<Appointment, LocalDateTime>() {
    @Override
    protected void updateItem(LocalDateTime item, boolean empty) {
        super.updateItem(item, empty);
        setText(item == null ? "" : formatter.format(item));
    }
});

It worked perfectly! Thank you for your help! In order to reverse it to write into database, I just have to do it in reverse order? — Aman Khan, Feb 07 '20 at 18:38

Newbie · Answer 2 · 2020-02-07T16:06:29.940

0

First of all part of your code is missing from the question (stringToCalendar())

But assuming that you are attempting to parse a string date in UTC/GMT format and then exploit the Calendar functionality in another timezone, the following should work:

// Parse date - use your format
SimpleDateFormat sdf = new SimpleDateFormat("dd-MM-yyyy");
Date date = sdf.parse(strDate);
//instantiates a calendar using the current time in the specified timezone
Calendar cal= Calendar.getInstance(TimeZone.getTimeZone("GMT"));
cal.setTime(date);
//change the timezone
cal.setTimeZone(TimeZone.getDefault());
// or cal.setTimeZone(TimeZone.getTimeZone("Asia/Calcutta"));
//get the current hour of the day in the new timezone
cal.get(Calendar.HOUR_OF_DAY);
//...

Part of the code comes from: How to change TIMEZONE for a java.util.Calendar/Date

edited Feb 07 '20 at 16:06

answered Feb 07 '20 at 15:28

Newbie

4,462
11
23

But how to make it read system time on local machine to calculate Timezone shift based on that? – Aman Khan Feb 07 '20 at 15:50
Typically, you get a TimeZone using getDefault which creates a TimeZone based on the time zone where the program is running. From: https://docs.oracle.com/javase/7/docs/api/java/util/TimeZone.html – Newbie Feb 07 '20 at 16:05
Your code helped me in understanding the problem! Thank you! – Aman Khan Feb 08 '20 at 19:27
Can you provide any hint about how to improove the answer? – Newbie Feb 08 '20 at 21:35

How to read time from MySQL database appointment as UTC, convert to local system time and display it in TableView column in JavaFX?

2 Answers2