[SOLVED] Parsing mysql date to ZonedDateTime

Parsing mysql date to ZonedDateTime

I'm trying to parse data coming from MySql in the following format:

DATE_FORMAT(datetime,'%m-%d-%Y %H:%i')

Java parser code looks like this:

ZonedDateTime datetime = ZonedDateTime
                    .parse(
                            row[1].toString(), 
                            DateTimeFormatter
                                    .ofPattern("MM-dd-YYYY hh:mm")
                                    .withZone(ZoneId.of("Etc/GMT"))
                    );

Exception in thread "AWT-EventQueue-0" java.time.format.DateTimeParseException: Text '06-08-2017 04:15' could not be parsed: Unable to obtain ZonedDateTime from TemporalAccessor: {MinuteOfHour=15, DayOfMonth=8, WeekBasedYear[WeekFields[SUNDAY,1]]=2017, MonthOfYear=6, HourOfAmPm=4},ISO,Etc/GMT of type java.time.format.Parsed

Is there a better/shorter way to get this done?

Solution

The local repair to your problem is:

    ZonedDateTime datetime = LocalDateTime.parse(row[1].toString(), 
                    DateTimeFormatter.ofPattern("MM-dd-uuuu HH:mm"))
            .atZone(ZoneOffset.UTC);

Please note that I have changed YYYY to uuuu and hh to uppercase HH in your format pattern. Y is used for week-based-year, and h is used for clock-hour-of-am-pm (1-12) which does not make sense without a (the symbol for am/pm marker). Learn more about these symbols from documentation.

Edit: I was taking my mouth too full here. Your own Java code works nicely with just these two changes to your format pattern. I will let my version of the code stand in case anyone can take inspiration from it.

That said, the best solution is to get for example an Instant object from your JDBC driver rather than a string that is formatted on the database side and parsed back on the Java side. A JDBC 4.2 compliant driver should be able to give you that.