0

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:

  • 2018-02-26 10:50:00 the resulting Java date is correct

But for the arrival date:

  • 2018-02-26 12:20:00 the resulting Java date is set to 2018-02-26T00:20:00

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...

luso
  • 2,812
  • 6
  • 35
  • 50
  • 1
    I recommend you avoid the `SimpleDateFormat` class. It is not only long outdated, it is also notoriously troublesome. Today we have so much better in [`java.time`, the modern Java date and time API](https://docs.oracle.com/javase/tutorial/datetime/). – Ole V.V. Feb 14 '18 at 14:32
  • 3
    You're using `hh`, which is for 12-hour based clocks. Use `kk` instead for 24-hour clocks. But yes you should try to use `java.time` instead. :) – kevmo314 Feb 14 '18 at 14:33
  • In your workaround you are using `LocalDateTime` — from Joda-Time? That’s already better than the old Java date and time classes. – Ole V.V. Feb 14 '18 at 14:37
  • It's hard to understand why is behavior like that. I am curious also. What's coming to my mind is **Synchronization**, although there are no multiple threads in your code (DAO is to blame?). *Date formats are not synchronized. It is recommended to create separate format instances for each thread. If multiple threads access a format concurrently, it must be synchronized externally.* I really wish to know is this a case in your example. – zlakad Feb 14 '18 at 14:38
  • With Java 8 or later and a JDBC 4.2 compliant JDBC driver you should be able to do `LocalDateTime.of(rs.getObject(DEPART_DATE_VOL, LocalDate.class), rs.getObject(DEPART_HEURE_VOL, LocalTime.class))`. – Ole V.V. Feb 14 '18 at 14:40
  • And you are right in worrying about time zone… – Ole V.V. Feb 14 '18 at 14:49
  • This should be helpful: [Common mistakes in date/time formatting and parsing](https://codeblog.jonskeet.uk/2015/05/05/common-mistakes-in-datetime-formatting-and-parsing/). And this question: [Difference between hh:mm a and HH:mm a](https://stackoverflow.com/questions/34431260/difference-between-hhmm-a-and-hhmm-a/34431361). – Ole V.V. Feb 14 '18 at 14:53
  • Unfortunately I must stick to JDK6, thus the usage of (yes) joda.time – luso Feb 15 '18 at 08:51
  • If you’re happy with Joda-Time, using it is a good idea. It’s not my home field, but I believe it can parse your date string and time string from the database just as easily as `java.time` can? In case you do want to upgrade within Java 6, `java.time` has been backported to Java 6 and 7, so you can get [ThreeTen Backport](http://www.threeten.org/threetenbp/) and use `java.time`. – Ole V.V. Feb 15 '18 at 10:54

2 Answers2

2

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

lexicore
  • 42,748
  • 17
  • 132
  • 221
  • Of course! As usual, just skipping the most obvious :/ – luso Feb 14 '18 at 19:04
  • @luso Ultimately, you’d be better off using smart objects instead of dumb strings. See the [other Answer, by Ole V.V.](https://stackoverflow.com/a/48793182/642706) for the modern solution. – Basil Bourque Feb 14 '18 at 21:55
2
  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

Ole V.V.
  • 81,772
  • 15
  • 137
  • 161