File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes Oracle/OAS and the fly likes Java new Date() EST and get back from Oracle DB in UTC Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Products » Oracle/OAS
Bookmark "Java new Date() EST and get back from Oracle DB in UTC" Watch "Java new Date() EST and get back from Oracle DB in UTC" New topic
Author

Java new Date() EST and get back from Oracle DB in UTC

Niall Loughnane
Ranch Hand

Joined: Dec 07, 2006
Posts: 208
Hi,

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,

Thanks,

Niall
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3610
    
  60

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).

Some of the methods I'm speaking about are:
java.sql.PreparedStatement#setDate(int, java.sql.Date, java.util.Calendar)
java.sql.ResultSet#getDate(int, java.util.Calendar)

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.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Java new Date() EST and get back from Oracle DB in UTC