I have an application that is deployed on a JVM in New York,
The application does a new Date() and sets this field in the DB,
The new Date() creates a date object in EST and is stored in the database as EST,
The issue is that i need to display this time in Germany where the time zone is +0100 UTC,
Is there a way in Oracle that:
* the application creates a new Date() in EST
* the database is set to UTC
* when the application stores the new Date() (EST) into Oracle - Oracle converts the EST Date into the UTC time
* the application then reads the date from the database as UTC
* the application then converts this database UTC time into +0100 UTC,
Oracle's DATE does not have a time zone information, so you need to handle the time zones yourself. My advice would be to choose a time zone and store all DATE columns in the database in that time zone. GMT/UTC might be a good choice, but is it not necessary; you might choose a time zone closer to your local time zone, which might be more convenient to you. The time zone you choose should not have Daylight Saving Time, though, because in time zones that have DST it is not possible to represent all hours in the day when the DST ends. (GMT/UTC doesn't have Daylight Saving Time.)
The conversion can take place during storing/retrieving the dates from the DB. You'll use the setDate/getDate methods of PreparedStatement or Resultset which take an additional Calendar parameter. The date is stored to the database in the time zone of the supplied calendar; upon retrieval it is converted to the time zone of the supplied calendar. I'm not sure these operations are thread-safe and I'd suggest allocating a new calendar for each thread that uses these methods (I'm using ThreadLocal variables for this myself).
Alternatively you might use a TIMESTAMP WITH TIME ZONE, or TIMESTAMP WITH LOCAL TIME ZONE as the data type, as these types take time zones into account and the time zone information should be transferred correctly between application and the database when using these data types. I don't have much experience with these, though.