How to insert date/time in the UTC zone into MySQL via EclipseLink using Joda-Time?

Question

I need to store date/time in UTC zone into MySQL database (of a DATETIME type column). When a user enters a date, it is first converted to org.joda.time.DateTime by a JSF converter.

Before inserting this date into MySQL database, it again needs to be converted to java.util.Date - thanks to EclipseLink.

The following is the converter that again converters org.joda.time.DateTime to java.util.Date though it is not really needed to see this converter.

package joda.converter;

import java.util.Date;
import org.eclipse.persistence.mappings.DatabaseMapping;
import org.eclipse.persistence.mappings.converters.Converter;
import org.eclipse.persistence.sessions.Session;
import org.joda.time.DateTime;
import org.joda.time.DateTimeZone;

public final class JodaDateTimeConverter implements Converter
{
    private static final long serialVersionUID = 1L;

    @Override
    public Object convertObjectValueToDataValue(Object objectValue, Session session)
    {
        return objectValue instanceof DateTime?((DateTime) objectValue).withZone(DateTimeZone.UTC).toDate():null;
    }

    @Override
    public Object convertDataValueToObjectValue(Object dataValue, Session session)
    {
        return dataValue instanceof Date?new DateTime((Date) dataValue):null;
    }

    @Override
    public boolean isMutable()
    {
        return true;
    }

    @Override
    public void initialize(DatabaseMapping databaseMapping, Session session)
    {
        databaseMapping.getField().setType(java.util.Date.class);
    }
}

In the convertObjectValueToDataValue() method (the first one), the value of the first parameter - objectValue received is the correct UTC date/time converted by Joda-Time in a JSF converter.

For example, if I entered a date - 02-Oct-2013 11:34:26 AM then, the value of objectValue would be - 2013-10-02T06:04:26.000Z. This date/time should be inserted into the database.

But when this value is converted by this expression - (DateTime) objectValue).withZone(DateTimeZone.UTC).toDate(), it is again evaluated to 2013-10-02 11:34:26.0 and this value is supplied to the database which is incorrect.

Anyway, how to set the UTC zone to (DateTime) objectValue).withZone(DateTimeZone.UTC).toDate()?

---

A property of type org.joda.time.DateTime is designated in a model class as follows.

@Column(name = "discount_start_date", columnDefinition = "DATETIME")
@Converter(name = "dateTimeConverter", converterClass = JodaDateTimeConverter.class)
@Convert("dateTimeConverter")
private DateTime discountStartDate;

---

EDIT: ^{(The following JSF converter works as expected along with the EclipseLink converter above which remains intact - from the only answer until now by BalusC)}

This is my JSF converter.

package converter;

import java.util.TimeZone;
import javax.faces.application.FacesMessage;
import javax.faces.bean.ManagedBean;
import javax.faces.bean.RequestScoped;
import javax.faces.component.UIComponent;
import javax.faces.context.FacesContext;
import javax.faces.convert.Converter;
import javax.faces.convert.ConverterException;
import org.joda.time.DateTime;
import org.joda.time.DateTimeZone;
import org.joda.time.format.DateTimeFormat;
import org.joda.time.format.DateTimeFormatter;
import util.Utility;

@ManagedBean
@RequestScoped
public final class DateTimeConverter implements Converter
{
    @Override
    public Object getAsObject(FacesContext context, UIComponent component, String value)
    {
        DateTime dateTime=null;

        try
        {
            dateTime = DateTimeFormat.forPattern("dd-MMM-yyyy hh:mm:ss aa").withZone(DateTimeZone.forTimeZone(TimeZone.getTimeZone("IST"))).parseDateTime(value);
        }
        catch (IllegalArgumentException e)
        {
            throw new ConverterException(new FacesMessage(FacesMessage.SEVERITY_ERROR, "", Utility.getMessage("datetime.converter.error", DateTimeFormat.forPattern("dd-MMM-yyyy hh:mm:ss aa").print(DateTime.now().withZone(DateTimeZone.forID("Asia/Kolkata"))))), e);
        }
        catch(UnsupportedOperationException e)
        {
            throw new ConverterException(new FacesMessage(FacesMessage.SEVERITY_ERROR, "", Utility.getMessage("datetime.converter.error", DateTimeFormat.forPattern("dd-MMM-yyyy hh:mm:ss aa").print(DateTime.now().withZone(DateTimeZone.forID("Asia/Kolkata"))))), e);
        }
        return dateTime;
    }

    @Override
    public String getAsString(FacesContext context, UIComponent component, Object value)
    {
        DateTimeFormatter dateTimeFormatter=DateTimeFormat.forPattern("dd-MMM-yyyy hh:mm:ss aa").withZone(DateTimeZone.forID("Asia/Kolkata")); //This zone will be tackled/handled later from the database to display.              
        return value instanceof DateTime?dateTimeFormatter.print((DateTime)value):null;
    }
}

Answer 1

Your concrete problem is caused because DateTime#toDate() doesn't use the time zone during the conversion to java.util.Date. It basically returns new Date(millis) wherein the millis is the internally stored epoch time of DateTime instance, exactly as described by DateTime javadoc and required by the java.util.Date constructor.

In other words, the withZone(DateTimeZone.UTC) part has totally no effect here. The code behaves exactly the same as if that part was absent. That explains why you end up with the originally entered time.

Technically, the problem is in your custom JSF converter which converts from String to DateTime. That converter is apparently not taking the time zone into account and assuming that the input is already in GMT timezone (as by default). The converter must be instructed that the input is in IST timezone. If you were using standard java.util.Date property with standard JSF <f:convertDateTime>, then you could have solved it by setting its timeZone attribute to IST.

<h:inputText value="#{bean.date}">
    <f:convertDateTime pattern="dd-MMM-yyyy hh:mm:ss a" locale="en" timeZone="IST" />
</h:inputText>

Your custom JSF converter should under the covers be doing exactly the same: tell the API that the supplied String is in IST timezone instead of letting it assume that it's already in GMT timezone. You didn't show the custom JSF converter anywhere, so it's hard to supply the exact answer, but it should boil down to the following kickoff example:

String inputDateString = "02-Oct-2013 11:34:26 AM";
String inputDatePattern = "dd-MMM-yyyy hh:mm:ss a";
TimeZone inputTimeZone = TimeZone.getTimeZone("IST");

DateTime dateTime = DateTimeFormat
    .forPattern(inputDatePattern)
    .withZone(DateTimeZone.forTimeZone(inputTimeZone))
    .parseDateTime(inputDateString);

The resulting DateTime instance will end up having the right internal epoch time in millis.