Java 8 - SQL Timestamp to Instant with properly formatted time

user_3816534 0
Viewed 67

I've read through the available q and a on SO, but nothing I have found answers my question of how to format my time in 12hour format.

Following is my code that runs a query on a MySQL database and returns results, checking to see if an appointment is within 15 minutes of login so an alert can pop.

public void apptCheck(int userId) throws SQLException {
        
        // this method checks for an appointment occurring within 15 minutes of login
        
        Statement apptStatement = DBQuery.getStatement();
        String apptQuery = "Select apt.start, cs.customerName from DBName.appointment apt "
                + "JOIN DBName.customer cs ON cs.customerId = apt.customerId WHERE "
                + "userId = " + userId + " AND start >= NOW() AND start < NOW() + interval 16 minute";
        apptStatement.execute(apptQuery);
        ResultSet apptRs = apptStatement.getResultSet();
        while(apptRs.next()) {
            Timestamp apptTime = apptRs.getTimestamp("start");
            
            
            ResourceBundle languageRB = ResourceBundle.getBundle("wgucms/RB", Locale.getDefault());
            Alert apptCheck = new Alert(AlertType.INFORMATION);
            apptCheck.setHeaderText(null);
            apptCheck.setContentText(languageRB.getString("apptSoon") + " " + apptTime.toInstant().atZone(ZoneId.systemDefault()));
           
            apptCheck.showAndWait();
           
           
        }

My result is:

enter image description here

I want the time to display 3:00, not the 19:00 - 06:00. How can I make that happen?

java-8
2 Answers

I found the solution which will perform the UTC to local time conversion and then format the time so that the resulting alert is in 12 hour time format without the date or time zone info. Here is the full code:

while(apptRs.next()) {
        Timestamp apptTime = apptRs.getTimestamp("start");
        
        // perform time conversion from UTC to User Local Time
        ZoneId zidApptTime = ZoneId.systemDefault();
        ZonedDateTime newZDTApptTime = apptTime.toLocalDateTime().atZone(ZoneId.of("UTC"));
        ZonedDateTime convertedApptTime = newZDTApptTime.withZoneSameInstant(zidApptTime);
        
        ResourceBundle languageRB = ResourceBundle.getBundle("wgucms/RB", Locale.getDefault());
        Alert apptCheck = new Alert(AlertType.INFORMATION);
        apptCheck.setHeaderText(null);
        
        // set the Alert text and format in 12 hour format
        apptCheck.setContentText(languageRB.getString("apptSoon") + 
                convertedApptTime.toInstant().atZone(ZoneId.systemDefault())
                        .format(DateTimeFormatter.ofPattern("h:mm a")) + ".");
       
        apptCheck.showAndWait();
       
       
    }
user_38165340 
 ZonedDateTime zonedDateTime=ZonedDateTime.of(apptTime.toLocalDateTime(),ZoneId.systemDefault());

You can use ZonedDateTime and format the time as you want.

docs.oracle.com/javase/8/docs/api/java/time/ZonedDateTime.html ZonedDateTime has a lot of features you can see all here and you can get the hour, minute, day etc.

DateTimeFormatter formatter = DateTimeFormatter.ofPattern("hh:mm:ss"); 
String formattedString = zonedDateTime.format(formatter);

if you only want time in 12hour format you can use this

user_95739430
Related