Parsing mysql date to ZonedDateTime

Question

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?

Read it as a `java.sql.Date`, and convert that to a `ZonedDateTime`. — Elliott Frisch, Jun 08 '17 at 15:19
Not everything you read from database must come as a string. Sql has native date-time types, and you should definitely use them. — M. Prokhorov, Jun 08 '17 at 15:20
Before you resort to outdated classes like `java.sql.Date` and friends, check if you have or can get a JDBC driver that can give you an instance of the modern classes like `ZonedDateTime` or `Instant` (requires JDBC 4.2 compliance, so I’ve read). — Ole V.V., Jun 08 '17 at 15:39

score 2 · Accepted Answer · edited Jan 01 '23 at 17:45

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.

This is what i ended up doing: ZonedDateTime dt = ZonedDateTime.ofInstant(rs.getTimestamp("datetime").toInstant(), UTCZONEID) — AnKing, Jun 08 '17 at 16:19
That looks like an improvement over the way you tried to make work before. Clearer and simpler. Another thing I like about it is that the `java.sql.Timestamp` you obtain, the first thing you do with it is convert it to `Instant` so to minimize your use of the old class. — Ole V.V., Jun 08 '17 at 16:36

Parsing mysql date to ZonedDateTime

1 Answers1