Re: [eclipselink-users] MySQL's datetime and time-zones?

[lwedmo <leo@xxxxxxxxxxx>]

Here's the work around I'm using.  Recognizing that java.util.Date,
java.sql.Date, java.sql.Timestamp are all essentially longs, and what I call
universal dates, and that Oracle and MySql DATE and TIMESTAMP types are
essentially tuples (y m d h m s etc. ) and are regional dates.  There is a
fundamental mismatch.  A region or zone's time rules are required to convert
back and forth between regional and universal.  So my solution is to create
use a different type, a regional time, and write a converter for it.  Since
EclipseLink does not provide support for a regional date time, we use a
regional object which Oracle will understand (oracle.sql.DATE), and "tunnel"
it all the way through the EclipseLink layer. In the code below, we are
converting from regional to regional date, so no zone information is needed. 
The complexity comes from coding and decoding to Oracles byte array format
described in
http://www.orindasoft.com/public/Oracle_JDBC_JavaDoc/javadoc815/oracle.sql.DATE.html. 
In this way, we have a what you see is what you get relationship with the
database without conversion surprises.

      Len

import java.sql.Timestamp;

import oracle.sql.DATE;

import org.eclipse.persistence.mappings.DatabaseMapping;
import org.eclipse.persistence.mappings.converters.Converter;
import org.eclipse.persistence.sessions.Session;

public class RegionalDateTimeConverter implements Converter {

	public Object convertDataValueToObjectValue(Object dataValue, Session
session) {
		if (dataValue == null){	
			return null;	
		}
		byte[] d = DATE.toBytes((Timestamp) dataValue);
		// y m d h m s
		return new RegionalDateTime (
				((d[0]&0xFF)-100)*100 + (d[1]&0xFF)-100,
				d[2],
				d[3],
				d[4]-1,
				d[5]-1,
				d[6]-1
		);
	}

	public Object convertObjectValueToDataValue(Object objectValue, Session
session) {
		if (objectValue == null){
			return null;
		}
		RegionalDateTime dateTime = (RegionalDateTime) objectValue;

		byte[] d = new byte[7];
		d[0] = (byte) (dateTime.getYear() / 100 + 100);
		d[1] = (byte) (dateTime.getYear() % 100 + 100);
		d[2] = (byte) dateTime.getMonth();
		d[3] = (byte) dateTime.getDate();
		d[4] = (byte) (dateTime.getHour() + 1);
		d[5] = (byte) (dateTime.getMinute() + 1);
		d[6] = (byte) (dateTime.getSecond() + 1);

		return new DATE(d);
	}

	public void initialize(DatabaseMapping mapping, Session session) {
	}

	public boolean isMutable() {
		return false;
	}
}



cowwoc wrote:
> 
> Hi,
> 
> What's the implication of using EclipseLink to store dates in MySQL
> "datetime" columns? By the looks of it EclipseLink stores the date using
> the current time-zone. Shouldn't EclipseLink converts dates to UTC before
> passing them on to MySQL? Otherwise, what will happen if the server
> time-zone changes?
> 
> Thank you,
> Gili
> 

-- 
View this message in context: http://www.nabble.com/MySQL%27s-datetime-and-time-zones--tp21006801p22017316.html
Sent from the EclipseLink - Users mailing list archive at Nabble.com.