Joda DateTime,, Formatting, and Mysql Timestamp

Question

After a week of going through so many examples, and moving from Java Date, to Calendar, to Joda. I have decided to seek help from other sources.

The problem:

Our table has two fields Date (Timestamp), and TZ (String). The idea is to store the user's UTC in timestamp, and timezone, well, you get the idea. So basically we think in UTC, and present the user with the time converted to their timezone on the front end (ie, using the value store in table.TZ)

Another requirement is to use the proper Object (Date, DateTime whatever). And not pass a String representation of the date around. The best would be a valid Long that will be correctly translated by MySQL, without having to use the FROM_UNIXTIME mysql function in our query.

Code we are using:

public DateTime convertTimezone(LocalDateTime date, DateTimeZone srcTZ, DateTimeZone dstTZ, Locale l) {
DateTimeFormatter formatter = DateTimeFormat.forPattern("yyyy-MM-dd HH:mm:ss").withLocale(l);

DateTime srcDateTime = date.toDateTime(srcTZ);
DateTime dstDateTime = srcDateTime.toDateTime(dstTZ);

System.out.println(formatter.print(dstDateTime));
System.out.println(formatter.parseDateTime(dstDateTime.toString()));


return formatter.parseDateTime(formatter.print(dstDateTime));
}

The String output is exactly what we need (ie UTC time, 2013-08-23 18:19:12), but the formatter.parseDateTime(dstDateTime.toString() is crashing with the following error. Probably because of the UTC timezone independent info, and milleseconds?:

Exception in thread "main" java.lang.IllegalArgumentException: Invalid format: "2013-08-    23T18:19:12.515Z" is malformed at "T18:19:12.515Z"
at org.joda.time.format.DateTimeFormatter.parseDateTime(DateTimeFormatter.java:873)
at com.example.business.rate.RateDeck.convertTimezone(RateDeck.java:75)
at com.example.business.rate.RateDeck.WriteData(RateDeck.java:143)
at com.example.business.rate.RateDeck.main(RateDeck.java:64)

Search engine enriched question:

How to format UTC for Joda DateTime.

PS My first SO post, and it feels nice? :)

Thanks in Advance,

The new fixed version:

public Timestamp convertTimezone(LocalDateTime date, DateTimeZone srcTZ, DateTimeZone dstTZ, Locale l) {
DateTime srcDateTime = date.toDateTime(srcTZ);
DateTime dstDateTime = srcDateTime.toDateTime(dstTZ);
return new Timestamp(dstDateTime.getMillis());
}

Nick.

JB Nizet JB Nizet · Accepted Answer · 2013-08-23T21:16:12

It's simply crashing because the format of the parsed string doesn't match with the format of the formatter.

The formatter parses using the format yyyy-MM-dd HH:mm:ss, and the toString() method of DateTime formats the date it using (as documented) the ISO8601 format (yyyy-MM-ddTHH:mm:ss.SSSZZ).

Joda DateTime,, Formatting, and Mysql Timestamp

1 Answers