I use Hibernate Entity Manager with JBoss 4.2.2 and Seam 2.0 and I have marked my java.util.Date mapped fields with @Temporal(TIMESTAMP) but Hibernate generates datetime' MySQL columns instead of 'timestamp'.

This is a big problem since this is going to be an international application and I want to store dates/times in db as number of millis since epoch (1970-01-01 00:00:00 UTC) and display them back to users in their own TimeZones rather than storing the year, month, day, hour, minute and seconds as a datetime.

For more info please refer to MySQL documentation.
http://dev.mysql.com/doc/refman/5.0/en/ ... ments.html

Hibernate version:
Hibernate 3.2 that comes with JBoss 4.2.2.GA

Mapping documents:
N/A since I used JPA/EJB3 annotations.
I use org.hibernate.dialect.MySQL5InnoDBDialect

Code between sessionFactory.openSession() and session.close():
N/A

Full stack trace of any exception that occurs:
N/A

Name and version of the database you are using:
MySQL 5.0.45

The generated SQL (show_sql=true):


Debug level Hibernate log excerpt:
N/A

Problems with Session and transaction handling?
N/A

Are you sure it is a problem?
timestamp and datetime are just two different ways to encode a Date, when you will load the saved entity it will have the same Date it had when you save it, nothing changed.
When you will show the Date object to the user you will have all options about format and timezones.

Anyway if you feel more comfortable with timestamps you could try forcing the type mapping:

should do the trick.

I would suggest keeping the datetime, as you can't persist all dates wih timestamp (before 1978 and after some future date), so you would actually loose information when forcing the mapping type to timestamp.

regards,
Sanne

The problem is this (or at least I think there exists a problem):

Topology:
webserver(s) in Europe and California connecting to a central database in California.

Problem:
Customer logs in from Paris to a server hosted in Europe and places an order at 1PM CET. The Admin in Los Angeles logs into a server hosted in California and goes to the process order screen. Now we have different scenarios:

1) if the date was stored in database as 1PM with TimeZone CET (a combination of a datetime column and a varchar column for TimeZone), then Admin can see that the order was placed at 1PM CET and the application can convert it to Los Angeles time too. But a simple query of 'find all orders placed today' would not be so simple anymore, since MySQL doesn't really support timezones directly.

2) store all dates in the database as datetime types and before saving any dates to the database convert them to GMT. This sounds possible, I just don't know how to do it in a global level. Maybe a hibernate filter?

3) store all the dates in database as timestamps which is basically number of milliseconds since epoch. Now that's not TimeZone dependant. Since the number of milliseconds since epoch is the same no matter what time zone you're in and that's actually how java stores dates in a java.util.Date object. But then there's the catch that you can't store dates before 1970 and after 2037 which i think should be OK for an ecommerce system.

This looks like a pretty universal problem but I was not able to find a solution for it using google. Or maybe I'm just over-complicating it.

Thanks again for the reply.

Hi,
I don't think you want this logic depend on where your server is, just where your client is.

Most people just keep all servers using GMT times, so you really have only to care at the presentation level. This is a known best practice, and that's why JSF specifications default the converters to use GTM/UTC

from http://wiki.apache.org/myfaces/FAQ :




That solves all presentations;
To resolve
you will have to write some trivial code, just to define what you mean with "today" using GMT.


maybe, but maybe you'll find exceptions (customer born date?)

You should have all business logic using GMT, otherwise you can't compare dates from different sources. So, yes it is a good idea, but you don't need to configure any filter: just use GTM dates everywhere.

regards,
Sanne

Sanne,

I agree that it is best to save date in the database in GMT I just don't know how to do it. See the problem is this, all that java.util.Date stores is the number of milliseconds since the epoch, which is not TimeZone dependant.

Try this:



The formatted times are different even though the millis are the same which makes sense. Now when I'm trying to save the field in the database, then I basically format it as a YYYYMMDDHHmmss and the formatting IS TimeZone dependent. So how does hibernate know how to format the date? How can I force Hibernate to always format in GMT? The first thing that comes to mind is to always set the TimeZone of the server to GMT, what do you think?

Thanks,
Drew

I would do that, all our servers are usually configured like that; Also all linux servers default to that configuration as it's the only one wich saves you from all this sort of trouble.

I also remember to have read somewere you can still use a different timezone on the server and configure the default one at JVM startup, unfortunatley I don't remember how to and if you get other types of problems in such a scenario;

There are some more tips at http://www.hibernate.org/100.html, still I would advise you to set the whole system as GMT, keeping it simple and flexible: it's a known good design practice.

regards,
Sanne

Thanks for the reply. I think that's what I'm going to do.

This is how you can set the default TimeZone of JVM from commnd line:

-Duser.timezone=GMT

I also found http://www.javaworld.com/javaworld/jw-10-2003/jw-1003-time.html very helpful.

cheers,
Drew

nice, I wish you luck.
could you please rate some of my posts?

regards,
Sanne