This week's giveaway is in the Android forum.
We're giving away four copies of Android Security Essentials Live Lessons and have Godfrey Nolan on-line!
See this thread for details.
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


Win a copy of Android Security Essentials Live Lessons this week in the Android forum!
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: 3606
    
  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.
 
Don't get me started about those stupid light bulbs.
 
subject: Java new Date() EST and get back from Oracle DB in UTC
 
Similar Threads
How to decide EST or EDT in java program
Java Date problem
Convert Local time to UTC and vice versa
Daylight Saving Time in Java5
Timezone getting Oracle date