Java, Hibernate, MySQL - store UTC date-time

Question

I saw here on SO a few related questions (like this one and of course, this one)... Essentially, what I want is to store date-time as UTC, and let application user choose the time zone he wants to display date-time in.
Since it seems that date-time fields are affected by the underlying JDBC driver, I wonder if this is an acceptable way to go about storing UTC date-time:

Set both MySQL and Application server machine to UTC time zone (no need to separate)
Both MySQL and JVM should pick up underlying system time settings (if not instructed otherwise)
Use DATETIME table columns on MySQL side
Use java.util.Date as corresponding mapping on Hibernate side (I guess java.sql.Timestamp could be used too)
Let the application worry about interpreting date-time fields - i.e. let the user choose preferred time zone

Is this OK?

EDIT
To clarify - here I meant to refer to timestamps created strictly on the server (e.g.date-time of record creation). So the application server instantiates Date objects (new Date() equals current date-time on the server, and this is really time zone agnostic).
Now if a client user wants to supply some date for searching/filtering purposes, here is where the transformation from client-local time to UTC should take place, IMHO...

AFAIR we had to convert the local date to UTC before storing it on DB and then convert it to corresponding time zone on display. — Germann Arlington, Aug 08 '13 at 12:29
But what is considered here as "the local date" ? Were your servers set up to use local time? — Less, Aug 08 '13 at 12:32
In your case "client" (where the date object is created) is the DB server. — Germann Arlington, Aug 08 '13 at 12:43

score 3 · Accepted Answer · answered Aug 08 '13 at 12:43

I would suggest another simple approach which would independent of machine timezone settings.

Instead of setting the timezone of the server machine, set the timezone of JVM. This can be done via system properties. On Windows example would as follows

set JAVA_OPTS=%JAVA_OPTS% -Duser.timezone=GMT

For MySQL here is the reference for doing it.
Now this this make sure that all your date-time are in GMT
Keep the timezone as configurable property OR it can be user dependent as well. So you store timezone for each user if the users belong to different geographies.
Whenever, a date is needed, after you select it from the database, apply the timezone to get the correct time.
The advantage of this approach is that this will work for the all the timezone users. Meaning the user will see the correct time as per their timezone.

Thanks Santosh, that is exactly what I meant and already did on the application level (I allowed the user to select and save his own date format and time zone). The only real question here was how to initially store UTC/GMT date-time. I was aware of your suggested approach too (set DB server and JVM time zone settings separately). You think that this is a better solution? — Less, Aug 08 '13 at 12:54
Yes I feels so as it makes your application independent of machine timezone. So for deployment of you application the deployment engineer need not bother about the server machine timezone. — Santosh, Aug 08 '13 at 12:57

score 0 · Answer 2 · answered Aug 08 '13 at 12:38

0

Use locales to implement internationalization.

answered Aug 08 '13 at 12:38

user2550754

884
8
15

Java, Hibernate, MySQL - store UTC date-time

2 Answers2

Linked