CODEWITHSUNDEEP
javadatesimpledateformat
luso
luso

Reputation: 3000

Incorrect date parsing with SimpleDateFormat

I've got a MySQL table with a date() and time() columns like 

CREATE TABLE `vol` (
  `ID` bigint(20) unsigned NOT NULL,
  `DEPART_DATE_VOL` date NOT NULL,
  `DEPART_HEURE_VOL` time NOT NULL,
  `ARRIVEE_DATE_VOL` date NOT NULL,
  `ARRIVEE_HEURE_VOL` time NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

I need to query rows using the date() in the WHERE clause so I won't have a datetime() or timestamp (also, I can't modify the DB)

When getting the info in my DAO, I concat the date and time columns to parse a Java Date like:

SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
addon.setDateDepart(sdf.parse(rs.getString(DEPART_DATE_VOL) + " " + rs.getString(DEPART_HEURE_VOL)));
addon.setDateArrivee(sdf.parse(rs.getString(ARRIVEE_DATE_VOL) + " " + rs.getString(ARRIVEE_HEURE_VOL)));

For a row with departure date:

But for the arrival date:

TMP Solution

For now, I'll stick to

addon.setDateDepart(new LocalDateTime(rs.getString(DEPART_DATE_VOL) + "T" + rs.getString(DEPART_HEURE_VOL)).toDate());

addon.setDateArrivee(new LocalDateTime(rs.getString(ARRIVEE_DATE_VOL) + "T" + rs.getString(ARRIVEE_HEURE_VOL)).toDate());

which seems to work, altough I'm not sure if I will run into timezone problems...

Upvotes: 0

Views: 83

Answers (2)

Anonymous
Anonymous

Reputation: 86173

  1. Use timestamp datatype in your database instead of separate columns for date and for time. A MySQL timestamp is in UTC, so should avoid any time zone issues on the database side.
  2. Use java.time, the modern Java date and time API. The old date and time classes like Date are poorly designed, and SimpleDateFormat in particular is notoriously troublesome, so avoid it.
  3. Transfer date-time objects between your Java program and your database, not strings.

Read more in this question: Java Best Practice for Date Manipulation/Storage for Geographically Diverse Users. If you cannot do all of this, do as much as you can.

Details

If you can store timestamps in your database, you can retrieve them as java.time.Instant:

Instant departVol = rs.getObject(DEPART_INSTANT_VOL, Instant.class);

If you cannot change the type that addon.setDateDepart() accepts, convert to a Date like this:

addon.setDateDepart(Date.from(departVol));

Or in the version using the ThreeTen Backport, the backport of java.time to Java 6 and 7:

addon.setDateDepart(DateTimeUtils.toDate(departVol));

If you cannot change the database design, you can still retrieve java.time objects from it:

LocalDate departDateVol = rs.getObject(DEPART_DATE_VOL, LocalDate.class);
LocalTime departHeureVol = rs.getObject(DEPART_HEURE_VOL, LocalTime.class);
LocalDateTime departDateHeureVol = LocalDateTime.of(departDateVol, departHeureVol);

The conversion to an old-fashioned Date is shaky, because as you say, there might be a time zone issue. Try:

addon.setDateDepart(
        Date.from(departDateHeureVol.atZone(ZoneId.systemDefault()).toInstant()));

If there seems to be a time zone issue here, you need to specify the correct time zone instead of ZoneId.systemDefault().

If your Java version or JDBC driver doesn’t support getting the right date-time objects from your result set, parsing the strings is straightforward with the modern classes:

LocalDate departDateVol = LocalDate.parse(rs.getString(DEPART_DATE_VOL);
LocalTime departHeureVol = LocalTime.parse(rs.getString(DEPART_HEURE_VOL);

With these objects continue as above.

PS I haven’t tested my code snippets. If there’s a typo and you cannot fix it yourself, please revert.

What went wrong in your code?

kevmo314 is correct that lowercase hh in your format pattern string is for hour with AM or PM, from 1 through 12. So 12:20:00 does mean 00:20:00. To interpret 12 on a 24 hour clock, use uppercase HH for hour of day from 0 through 23.

Link: ThreeTen backport Home

Upvotes: 2

lexicore
lexicore

Reputation: 43651

Try HH:mm:ss for the time part. hh is used for "Hour in am/pm (1-12)", see the javadoc.

Upvotes: 2

Related Questions