Mailing-List: contact users-help@openjpa.apache.org; run by ezmlm
Precedence: bulk
Reply-To: users@openjpa.apache.org
Received-SPF: pass (nike.apache.org: domain of diogocasado@gmail.com
 designates 209.85.223.180 as permitted sender)
MIME-Version: 1.0
Date: Tue, 3 Dec 2013 14:31:09 -0200
Message-ID: 
 <CAD0TfkFzUR+CaLbs+KJgQobmn2xo_w0HoZMC+CVYM+MwoimpZQ@mail.gmail.com>
Subject: yet another DATETIME timezone question related to openjpa+mysql
From: Diogo Casado <diogocasado@gmail.com>
To: users@openjpa.apache.org
Content-Type: text/plain; charset=ISO-8859-1

Hello guys,

I just finished migrating from Hibernate to OpenJPA since we will use
Tomee from now on.
I've been researching/testing this problem for quite some time now.
Could use some help..

I have a DATETIME field in a MySQL database that must be stored as utc
time. This is standard good practice I guess.
But the database server is not in utc time and cannot change server
timezone. I have to connect to several databases but they all have
different timezones.

I'm using the following annotation:

@Column
@Temporal(TemporalType.TIMESTAMP) //Does not affect result
private Date dateTimeUtc;

What happens is that when I fetch/update this field.. the Date object
millisecond value gets adjusted to database session timezone and an
invalid value is stored.
Just highlighting that DATETIME is not influenced by server timezone
conversion while TIMESTAMP is..

For example:
Let's say I need to store 2013-12-03 12:00:00 UTC.
I use JodaTime to convert my local time (adjustable depending on user
preferences, cannot change default instance timezone) to utc.
An example:
new DateTime().withZone(DateTimeZone.UTC).withDate(2013, 12,
3).withTime(12, 0, 0, 0).toDate(); -> this is set in the above field.

My date object is correct, nice milliseconds since epoch..
(1386072000000 checked with debug).
The database server is running on GMT-2 (in this case both jvm and
database are on the same timezone).
The value stored is 2013-12-03 10:00:00 instead of 2013-12-03 12:00:00.

The opposite:
I go there and update manually the field to 2013-12-03 12:00:00.

I fetch the entity the Date object has +2hs (millis 1386079200000).

I can do: new DateTime(dateTimeUtc, DateTimeZone.UTC) or just look at
Date's local representation and it shows 2013-12-03-12:00:00 which
means Date+2h(session timezone)-2h(jvm timezone) (confusing I know..
but it's behaving exactly this way).

How can I avoid this behavior? I previously worked with plain Date
object with hibernate and it was ok.

>From other posts.. everyone come up with: change your server timezone
to utc and that in my case is not an option.
So.. help?

Thanks!

- Diogo