JDBC 4.2 and java.time
Arvind Kumar Avinash has already nicely explained how the offset works (what XMLGregorianCalendar misleadingly calls a time zone). He has also already recommended java.time, the modern Java date and time API. I should like to elaborate on this recommendation. Assuming that you thought you wanted a java.sql.Timestamp for your SQL database, you should not want that. Since JDBC 4.2 and Hibernate 5 we can seamlessly transfer the types of java.time to our SQL databases. Which I recommend that you do instead.
If using timestamp with time zone in SQL, use OffsetDateTime in UTC in Java
If the datatype in SQL is timestamp with time zone (recommended for timestamps), transfer an OffsetDateTime from Java to it. To most database engines timestamp with time zone really means timestamp in UTC, so for clarity I prefer to pass an OffsetDateTime that is in UTC too. There are a couple of ways to convert, each with their pros and cons.
1. Convert via GregorianCalendar.
// For demonstration build an XMLGregorianCalenadr equivalent to yours
XMLGregorianCalendar xmlGregorianCalendar = DatatypeFactory.newInstance()
.newXMLGregorianCalendar("2020-10-02T13:07:38-06:00");
// Convert to OffsetDateTime for your SQL database
OffsetDateTime dateTime = xmlGregorianCalendar.toGregorianCalendar()
.toZonedDateTime()
.toOffsetDateTime()
.withOffsetSameInstant(ZoneOffset.UTC);
// Show what we’ve got
System.out.println(dateTime);
Output is:
2020-10-02T19:07:38Z
Pro: it’s the official conversion.
Or 2. Convert via String.
OffsetDateTime dateTime = OffsetDateTime.parse(xmlGregorianCalendar.toString())
.withOffsetSameInstant(ZoneOffset.UTC);
Pro: it’s short, and I don’t think it gives any surprises. Con: To me it feels like a waste to format into a string and parse back into a date-time object again.
3. Convert via int. You may convert by taking the individual fields out of the XMLGregorianCalendar and building an OffsetDateTime from them. It’s getting long-winded, handling fraction of second is a bit complicated, and there’s a risk of accidentally swapping fields, so I don’t recommend it.
How to pass to SQL. Here’s an example of transferring the OffsetDateTime to SQL.
String sqlString = "insert into your_table(your_timestamp_column) values (?);";
PreparedStatement ps = yourDatabaseConnection.prepareStatement(sqlString);
ps.setObject(1, dateTime);
int rowsInserted = ps.executeUpdate();
If using timestamp without time zone in SQL, instead pass a LocalDateTime from Java
If the datatype in SQL is timestamp without time zone (not recommended for timestamps, but often seen), you need to pass a LocalDateTime from Java. Again there are a couple of ways to convert. I am showing just one:
LocalDateTime dateTime = xmlGregorianCalendar.toGregorianCalendar()
.toZonedDateTime()
.withZoneSameInstant(ZoneId.systemDefault())
.toLocalDateTime();
I am using the default time zone of the JVM in agreement with what the old-fashioned Timestamp class did, so the result is time zone dependent. In my time zone (Europe/Copenhagen) currently at UTC offset +02:00, the result is:
2020-10-02T21:07:38
What went wrong in your code?
The three-arg XMLGregorianCalendar.toGregorianCalendar(TimeZone, Locale, XMLGregorianCalendar) that you were using is designed to introduce bugs like the one you observed. It promises to construct a GregorianCalendar with the specified time zone (if one is given) and with the field values from the XMLGregorianCalendar (year, month, day, hour, minute, etc.). So the documentation says it quite clearly: if you specify a time zone that does not agree with the offset of the XMLGregorianCalendar, it gives you a different point in time than the one specified by the XMLGregorianCalendar. It might occasionally be useful if the XMLGregorianCalendar hadn’t got any offset and we knew which time zone was intended. But your XMLGregorianCalendar has got offset, so this method definitely is not what you want to use.
Links
